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?