How to make Excel strip ALL quotes from CSV text fields
When importing a CSV file into Excel, it only strips the double-quotes from the FIRST field on the line, but leaves them on all other fields. How can I force Excel to strip the quotes from ALL strings?
For instance, I have a CSV file:
"text1", "text2", "numeric1", "numeric 2"
"abc", "def", 123, 456
"abc", "def", 123, 456
"abc", "def", 123, 456
"abc", "def", 123, 456
I import it into Excel using Data > Import External Data > Import Data. I specify that the fields are delimited by commas, and that the text delimiter is the double-quote character.
Both the data preview and the actual Excel spreadsheet columns only strip the double-quotes from the first text field. All other text fields still have quotes around them. What's really strange is that Access is able to import this data correctly (i.e. strips quotes from every text field.
Note that this is NOT a matter of internal commas or quotes or escape characters.
This happens in Excel 2003 and Excel 2007.
Answer
Remove all spaces before/after commas from the document to be imported.