Import comma separated flat file

[osoful]

I have a comma separated data file that I'd like to import into Excel. Unfortunately the fourth column in my text file also contains commas so it creates incorrect columns when I imprt it into Excel. Is there a way to prevent that from happening?

text file looks like this:
A00060,DD CDLE,20091124,1230277,FOOTWEAR,DRIFT,17.60,1,17.600

FOOTWEAR,DRIFT is one column but has comma in the value...

[Richard Buttrey]

Hi,

Presumably that was a typo and you meant the fifth column.

If so is there always and without exception one comma separating words like FootWear and Drift? If so just open the file parsing it in the usual way with commas and then use a helper column to concatenate the 5th and 6th columns before copying the helper column and and pasting it as values in column 5, the finally delete column 6.

Regards

[osoful]

correct - it's the fifth column. It doesn't contain comms in every row, only some so I can't split and concatenate. Are there any other options?

[Doglips]

If you put double quotes around the fields containing commas, and use the Text Import Wizard with delimited by commas, then the field imports as you would like it to.

Example:
A00060,DD CDLE,20091124,1230277,"FOOTWEAR,DRIFT",17.60,1,17.600 inputs as eight fields, not nine as you are experiencing

You may be able to insert the double quotes by putting one after the fourth comma and one before the next comma which has a number after it.
Alternatively, if your .csv-creating application can put double quotes around all fields, then this will work exactly as above.

Example:
"A00060","DD CDLE","20091124","1230277","FOOTWEAR,DRIFT","17.60","1","17.600" also inputs as eight fields, and correctly determines text vs numeric fields

[osoful]

Unfortunately all I have is the text file and it has 40000 rows. Going through it one by one to insert quotes will take forever and there is no application to recreate that file. Are there any other options?

[martindwilson]

assuming only 1 extra coma then import into a single column say col a of excel
then in col b put
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))=8,SUBSTITUTE(A1,",","-",5),A1)
drag down once done copy col b paste back over itself as special values to remove formulas then split with text to columns