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
- Follow via:
- RSS
- Email Alert
Question
0
Votes
I'm looking to find a solution to this problem. please can someone help?
Updated - 31st May 2010
Answers (3)
0
Votes
What program are you using to manipulate the data?
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.
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.
31st May 2010
Replies
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
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
MiLOS Designs
31st May 2010
Do you want the data to just be put into columns?
Can you give a little bit more info?
Can you give a little bit more info?
NexS
31st May 2010
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.
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.
Darryl~
1st Jun 2010
just cause you're not behind me to "smack" me....check your spelling on "weather", should be "whether" 
PurpleSkys
1st Jun 2010
besides....I could have been talking about "cloud" computing.....you just never know with me. 
Darryl~
1st Jun 2010
pa-lease...whatever do you take me for? (be careful how you answer that )
)
PurpleSkys
1st Jun 2010
0
Votes
The way that is formatted (the csv file), it should
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.
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.
1st Jun 2010
Replies
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.
MiLOS Designs
1st Jun 2010
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
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
Darryl~
1st Jun 2010
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
Kindest Regards
Jonathan
MiLOS Designs
1st Jun 2010
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.
Stick around TechRepublic a bit if you have the time....there's much to learn here from some very smart people.
Darryl~
1st Jun 2010
0
Votes
using Vlookup or conditional formulas
either should get you what you need.
Do you just need the field, or only the field with that data in it?
Do you just need the field, or only the field with that data in it?
1st Jun 2010
