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?
5 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
-
4This did the trick for me. I had spaces after the delimiting commas and before the next quoted value. After I removed the spaces, Excel parse my CSV file correctly. Nov 7, 2012 at 3:43
-
4what about when there are quotes AND commas as part of the values between separating characters? I'm having tons of issues and there's no spaces after the separating characters and quotes inside a value are escaped with a second quote. Apr 8, 2015 at 16:49
-
2
-
@Michael I didn't. I imagine that a significantly more complex algorithm than string.split(",") is required. you would have to manually check if you're inside quotes when you encounter a comma and check on every quote if there is a second matching quote to go with it. Aug 29, 2016 at 15:07
-
3
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.
When your quote contains a double quote, put TWO double quotes together
If two entries of your original raw text data looks like
She said, "Hello!"
He replied, "Nice to see you!"
and you want those to be on the same row, as two columns, do the following in your .csv file (note these entries also have commas AND double quotes!)
"She said, ""Hello!""","He replied, ""Nice to see you!"""
Here's the result of opening the .csv file with the text from above:
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.
