How do I get Excel to import my CSV file?
File
I have a file claiming to be CSV. It contains 10 fields, all of which are surrounded by double quotes (yes, even the dates and numbers.) 2 or 3 of these fields contain commas. But data in these fields is also surrounded by quotes.
Problem
When I try to open this file, Excel completely ignores the quotes and assumes that they are part of the data.
It therefore splits the fields containing commas into multiple columns.
This is bad, at least for me.
Example data
"20051", "", "2009 Sep 30 02:53:23", "SOMETEXT", "", "5000", "2000000", "2008 - 99999 - -99999, 2008 - unk - unk", "191 - SOMETEXT - SOMETEXT, 192 - SOMETEXT - SOMETEXT, 193 - SOMETEXT - SOMETEXT, 194 - SOMETEXT - SOMETEXT, 195 - SOMETEXT - SOMETEXT, 196 - SOMETEXT - SOMETEXT", "45 - SOMETEXT - SOMETEXT, 1162 - SOMETEXT - SOMETEXT, 140 - SOMETEXT - SOMETEXT, 141 - SOMETEXT - SOMETEXT" "20052", "SOMETEXT", "2009 Sep 08 07:56:50", "SOMETEXT", "", "50000", "5000000", "2007 - 99999 - 99999", "146 - SOMETEXT - SOMETEXT, 145 - SOMETEXT - SOMETEXT, 147 - SOMETEXT - SOMETEXT, 148 - SOMETEXT - SOMETEXT", "103 - SOMETEXT - SOMETEXT, 1131 - SOMETEXT - SOMETEXT"
What have I tried?
I have used the import facility to specify delimiters and such, but this does not seem to help.
I have tried switching "Text Delimiter" from a double quote to "{none}" and back again. This appears to only affect the first column. This would be resasonable if it were possible to click the other columns and apply this setting to each. But this is not the case, and it seems this is intended to work across columns.
What else can I try?