4

I have a CSV file that with quoted string that contains comma. When I open it in excel it still uses the comma inside the strings and parse it in different columns. Can someone help?

T7, 7, "331-8463 C - Ind. HT collar, laser HT insert, crown TR", 85, 0.37, N, 0.00, N
T7, 8, "331-8463 C - Ind. HT collar, laser HT insert, crown TR", 90, 0.33, N, 0.00, N

Simply create a CSV file with the above two lines and open it in Excel File.

Here is the result I get.

CSV Comma Parsing Issue

  • Set "text qualifier" to " to let Excel know not to look inside it. – Máté Juhász Jan 12 '17 at 15:32
  • Additionally and just as FYI at least, the example you provided opens in Excel in the expected format if you set the delimiter to space only and leaving the default double quote text qualifier... Just FYI.... – Pimp Juice IT Jan 12 '17 at 16:15
2

Remove the extraneous spaces and it'll work correctly:

T7,7,"331-8463 C - Ind. HT collar, laser HT insert, crown TR",85,0.37,N,0.00,N
T7,8,"331-8463 C - Ind. HT collar, laser HT insert, crown TR",90,0.33,N,0.00,N
  • Oh Wow...Who knew? MS Special! Thank you so much. It works when I removed all extra spaces (not the one inside the quote). Really appreciate it. – Cricrazy Jan 12 '17 at 15:57
  • Who knew? Not I, but I had this hunch and it paid off. ;-) We both learned something useful. – Steve Rindsberg Jan 14 '17 at 18:45
2

Sadly, Steve Rindsberg is correct. Excel will accept double-quote encapsulation only if the field separator is:

,

rather than:

,space

We can't always control the format of our import files.

If you can't change the .csv file format, then use the Import Wizard to put all the data into a single column and use Text To Columns to parse the data yourself.

  • Thanks. I guess, you always learn new things...(or quirks) every day. I am creating a CSV file programmatically, so I can control the spaces. – Cricrazy Jan 12 '17 at 16:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.