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.
Use an advanced filter to filter and paste the results as values into a new worksheet in one go which at least saves a step and is probably more reproducable in fewer clicks every time after the first. You can also select only the columns you want and in any order.
Debra's site is probably the best resource for examples on this:
[www.contextures.com][1]
[1]: http:// www.contextures.com
Best Answer
This page also has the solution which comes straight from the horse's mouth:
http://support.microsoft.com/kb/291296/en-us
If the link degrades, the topic to search for is:
"Procedure to export a text file with both comma and quote delimiters in Excel" and/or "Q291296"
tl;dr: use their macro