Questions

I'm looking to find a solution to this problem. please can someone help?

+
0 Votes
Locked

I'm looking to find a solution to this problem. please can someone help?

MiLOS Designs
Below is an example of a CSV output I receive from a website where I sell my products:

2010-05-28 12:26:53,36714734,"","",0,0,0,"MiLOS DBO Sneakers - White - Female","Graceleigh Broderie","Graceleigh Broderie",199,10

I am interested in a formula that can extract "MiLOS DBO Sneakers - White - Female" from the above string. In addition, if it possible to also extract the "199" and "10" in separate columns.

I would be very appreciative if someone could help me.

Apologies, I'm importing the data into Microsoft Excel.

Kind Regards

Jonathan Heavens
  • +
    0 Votes
    Darryl~ Moderator

    Are you importing it into Access or another database?
    Are you opening it in a spreadsheet?
    Are you just opening it as a text file in Notepad or something?

    We need a little more info on what you're trying to do.

    +
    0 Votes
    MiLOS Designs

    Apologies Darryl, my ignorance thought that this site is primarily to do with Microsoft Excel.

    I am importing the data into Excel. The formulas I am attempting to use are FIND, MID, LEN etc but I am having no luck.

    Kind Regards

    Jonathan Heavens

    +
    0 Votes
    NexS

    Do you want the data to just be put into columns?
    Can you give a little bit more info?

    +
    0 Votes
    Darryl~ Moderator

    trying to accomplish....

    I'd import the csv into a database, whether it be Access or OpenOffice's Base...everthing would automatically be sorted into the columns you specified & you could run a couple simple queries to extract the info you want from what looks like the "description" or "product" field.

    +
    0 Votes
    PurpleSkys Moderator

    just cause you're not behind me to "smack" me....check your spelling on "weather", should be "whether"

    +
    0 Votes
    Darryl~ Moderator

    besides....I could have been talking about "cloud" computing.....you just never know with me. :)

    +
    0 Votes
    PurpleSkys Moderator

    pa-lease...whatever do you take me for? (be careful how you answer that )

    +
    0 Votes
    Darryl~ Moderator

    inport into Excel fine & in the separate columns you desire....the " around certian fields just designate them as text...if you're getting them in the cells once imported into Excel, you would have to do a "find/replace" to remove them....quite a hassle....thus the reason I said a database would be nicer to work with.
    After you have them in the Excel sheet, all the "product descriptions" should be in the same column.....then you should be able to just do a "a - Z" sort & they would all show up together for you to copy & past or whatever it is you want to do....make sure you extend the sort to include all columns.

    I still say a database would be easier....it kind of looks like you're trying to use Excel as a database...that's a common mistake many people try to do.

    +
    0 Votes
    MiLOS Designs

    First of I would like to say a big thank you to everyone showing interest in my query. Basically, the background is that I run a business selling virtual products in the virtual world of Second Life. My products are listed on a website and sales are recorded. I can have a look at all previous sales but I can only copy and paste the CSV data from the webpage; hence why I am getting all the data into one cell. I don't have ACCESS, and I am simply interested in seeing if there is a way to extract the strings of text from each line, separating the information into separate columns. This is all in order to do some product analysis on sales.

    +
    0 Votes
    Darryl~ Moderator

    a copy and paste into a Notepad file & save it as filename.csv? (or just rename it from a .txt to a .csv extention).

    I've done that before in situations like you're attempting when I can't copy into a spreadsheet properly....then you should be able to just open it in Excel & it will already be formatted for the cells....works pretty slick :)

    +
    0 Votes
    MiLOS Designs

    Darryl, well done! Perfect, its all split into columns. Works perfect, I can now integrate the data into other spreadsheets and do product analysis. I'll be coming to you again if I have another query.

    Kindest Regards

    Jonathan

    +
    0 Votes
    Darryl~ Moderator

    Once I realized what your problem was I remembered experiencing that a number of times myself....I think it was more a matter of me not asking the "proper" questions initally so I knew what the problem was.

    Stick around TechRepublic a bit if you have the time....there's much to learn here from some very smart people.

    +
    0 Votes
    jdclyde

    either should get you what you need.

    Do you just need the field, or only the field with that data in it?

  • +
    0 Votes
    Darryl~ Moderator

    Are you importing it into Access or another database?
    Are you opening it in a spreadsheet?
    Are you just opening it as a text file in Notepad or something?

    We need a little more info on what you're trying to do.

    +
    0 Votes
    MiLOS Designs

    Apologies Darryl, my ignorance thought that this site is primarily to do with Microsoft Excel.

    I am importing the data into Excel. The formulas I am attempting to use are FIND, MID, LEN etc but I am having no luck.

    Kind Regards

    Jonathan Heavens

    +
    0 Votes
    NexS

    Do you want the data to just be put into columns?
    Can you give a little bit more info?

    +
    0 Votes
    Darryl~ Moderator

    trying to accomplish....

    I'd import the csv into a database, whether it be Access or OpenOffice's Base...everthing would automatically be sorted into the columns you specified & you could run a couple simple queries to extract the info you want from what looks like the "description" or "product" field.

    +
    0 Votes
    PurpleSkys Moderator

    just cause you're not behind me to "smack" me....check your spelling on "weather", should be "whether"

    +
    0 Votes
    Darryl~ Moderator

    besides....I could have been talking about "cloud" computing.....you just never know with me. :)

    +
    0 Votes
    PurpleSkys Moderator

    pa-lease...whatever do you take me for? (be careful how you answer that )

    +
    0 Votes
    Darryl~ Moderator

    inport into Excel fine & in the separate columns you desire....the " around certian fields just designate them as text...if you're getting them in the cells once imported into Excel, you would have to do a "find/replace" to remove them....quite a hassle....thus the reason I said a database would be nicer to work with.
    After you have them in the Excel sheet, all the "product descriptions" should be in the same column.....then you should be able to just do a "a - Z" sort & they would all show up together for you to copy & past or whatever it is you want to do....make sure you extend the sort to include all columns.

    I still say a database would be easier....it kind of looks like you're trying to use Excel as a database...that's a common mistake many people try to do.

    +
    0 Votes
    MiLOS Designs

    First of I would like to say a big thank you to everyone showing interest in my query. Basically, the background is that I run a business selling virtual products in the virtual world of Second Life. My products are listed on a website and sales are recorded. I can have a look at all previous sales but I can only copy and paste the CSV data from the webpage; hence why I am getting all the data into one cell. I don't have ACCESS, and I am simply interested in seeing if there is a way to extract the strings of text from each line, separating the information into separate columns. This is all in order to do some product analysis on sales.

    +
    0 Votes
    Darryl~ Moderator

    a copy and paste into a Notepad file & save it as filename.csv? (or just rename it from a .txt to a .csv extention).

    I've done that before in situations like you're attempting when I can't copy into a spreadsheet properly....then you should be able to just open it in Excel & it will already be formatted for the cells....works pretty slick :)

    +
    0 Votes
    MiLOS Designs

    Darryl, well done! Perfect, its all split into columns. Works perfect, I can now integrate the data into other spreadsheets and do product analysis. I'll be coming to you again if I have another query.

    Kindest Regards

    Jonathan

    +
    0 Votes
    Darryl~ Moderator

    Once I realized what your problem was I remembered experiencing that a number of times myself....I think it was more a matter of me not asking the "proper" questions initally so I knew what the problem was.

    Stick around TechRepublic a bit if you have the time....there's much to learn here from some very smart people.

    +
    0 Votes
    jdclyde

    either should get you what you need.

    Do you just need the field, or only the field with that data in it?