Excel 2007 save import steps on csv file

I just tried a quick test that replicated your problem.

I created a 1 line CSV in Word (which uses smart quotes) as test.csv “123”,“4,5,6” and it opened in Excel as you described.

Try replacing “ and ” with "

Having played with your sample I notice that Excel does not like the spaces between fields

e.g. instead of

"20051", "", "2009 Sep 30 02:53:23", ...

you want

"20051","","2009 Sep 30 02:53:23",...

a decent Regular Expression replacement should be able to handle it with

Find:    |("[^"]*",) |
Replace: |\1|

(pipe characters for visual cues only)

Or simply modify the .Net code if you have access to it ;-)

Also, as Arjan pointed out, you may also need to convert the file from UTF-8 to ANSI to prevent cell A1 from containing the BOM and its surrounding qoutes.

I have come across the Catch 22 of ANSI encoded CSV not handling international characters and UTF encoded CSV not being propery handled by Excel; and not found a solution while mainting the CSV. If international character support is required, the XML (or native XLS) formats seem the only way to go—at the cost of simplicity.

Open the file again. Then, according to the help:

1. Choose File > Save As.
2. In the File name box, type a name for the file.
3. In the File type list, select Text CSV and click Save.
   You may see a message box "This document may contain formatting or content that cannot be saved in the Text CSV file format. Do you want to save the document in this format anyway?" Click "Keep Current Format".
4. In the Export of text files dialog, select the options you want and then click OK.

If indeed you saw the warning in Step 3 (like because the file was using formulas now, which will be lost when saving as CSV), and you then selected "Save in ODF Format", then you would simply not have saved as CSV at all.

And as for:

CSV is supposed to be one of the simplest data formats

If it makes you feel any better: for a start, CSV needs to handle commas too. To do that, often text is quoted if it holds a comma. But then what if the text holds quotes too? And worse: in Excel on Windows even importing from CSV depends on your Regional Settings, like if to use commas or semi-colons or some other separator.