14

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?

  • 2
    Please show us the problematic fields, else the answers will be random stabs in the dark. – Jean-François Corbett Jul 11 '11 at 11:52
16

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"

  • 3
    This 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. – dthrasher Nov 7 '12 at 3:43
  • 4
    what 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. – anon58192932 Apr 8 '15 at 16:49
  • 2
    @anon58192932 ever figure this out? I'm seeing the exact same issue. – Michael Aug 5 '16 at 21:37
  • @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. – anon58192932 Aug 29 '16 at 15:07
  • 2
    Removing the spaces was the key. – Abram Dec 8 '16 at 20:05
1

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...

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy