Excel Does Not Parse Quoted String containing comma in CSV File Correctly

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.

Cricrazy

Posted 2017-01-12T15:21:11.630

Reputation: 826

Set "text qualifier" to " to let Excel know not to look inside it. – Máté Juhász – 2017-01-12T15:32:48.683

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 – 2017-01-12T16:15:15.747

Answers

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

Steve Rindsberg

Posted 2017-01-12T15:21:11.630

Reputation: 4 139

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 – 2017-01-12T15:57:29.070

Who knew? Not I, but I had this hunch and it paid off. ;-) We both learned something useful. – Steve Rindsberg – 2017-01-14T18:45:22.230

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.

Gary's Student

Posted 2017-01-12T15:21:11.630

Reputation: 15 540

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 – 2017-01-12T16:05:23.797