I have a CSV file that contains a text field that can have commas in it. When importing it into Excel, it's picking up these commas as field separators. I have the field in quotes, and I've tried double quotes as well. Is there any way I can import this as is or should I convert it to another delimiter?
14 Answers
Make sure there a no spaces between your separated values and wrap each field in quotes:
"1, 2","3, 4"Will import into 2 cells - the first having 1, 2 and the second having 3, 4
How are you importing it? Are you using the data import function on the ribbon or is this via VBA? Because when I import using the ribbon function I can specify the text qualifier as a " and it would import "abc,123","abc,123","abc,123" as abc,123<next cell>abc,123<next cell>abc,123.
If that isn't working though and you don't have to do this for dozens of files you could do a find-and-replace of "," (including quotes) and replace with "^t"(including quotes) (^t being the MS word version of tab if you do the find and replace in word). Then you can have a tab delimited file...
This isn't an ideal solution, but I find the csv importer in Google Sheets works better than the one in Excel. For one thing, it understands that commas inside quotes are not column delimiters. You can import to Google Sheets, then copy / paste into Excel.
Based on Brad's answer above, if you are importing a massive file (millions of rows), then Word will run out of space long before Notepad . In Notepad you would use Brad's principle but replace only with "^". This works when importing into Excel since you can only specify a single Character as the field delimited.
