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.
Those files are distributed? downloaded? Locally generated?
I think the unique way to solve your issue without access to the user's PC and without him to have to do something different of "double-click" on the file is to convert the files to .xls before distibuting or placing to download or to generate a .xls (or another format that works) instead of a .csv file. You can do that with a script or manually with a working Excel, depending on the number of files and the way they are generated.
Best Answer
If the CSV file uses tab characters, or some other delimiter between fields other than a comma, this is easy:
Download the free, open source GNUWin version of sed and put it in C:\Windows\SYSTEM32
Them enter this command
If your CSV uses commas to separate the fields, well, use Excel to output a new version which uses tabs instead of commas to separate the fields before regenerating a new version of OLDFILE.CSV.