CODEWITHSUNDEEP
mysqlcsv
amu03
amu03

Reputation: 361

comma in csv file is recognized as separator even though it's inside double quote

I am struggling for 2 days...Does anyone have any idea why this happens?

What I did:

I've exported some data as csv file (mysql). It's separated by comma, enclosed by double quotes.

the query looks like..

select * from table1 
INTO OUTFILE 'sample.csv'
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

and this is what sample.csv looks like:

"post_name","post_title","post_date","post_thumbnail","post_mainimage","post_content,"meta_description","text2","editor_id"
    "post1","title!｜this is title","1451905200","123.jpg","123.jpg","<p>this is description</p> <h2> this is description <br>\"this is description \"</h2><p><span style=\"line-height: 1.8;\">「this is description」</span></p><p><img alt=\"aa,bb,cc \" class=\"fr-fin fr-dib\" src=\"/files/123.jpg\" title=\"aa,bb,cc \" width=\"300\"></p><p>this is description</p><p><strong>this is description</strong></p><p><span style=\"font-size: 13px;\">this is description<br>this is description</span><br></p>","this is meta_description","this is text2","12"

Problem:

when I open this file on Mac Numbers (and other csv viewers as well), it seems like comma after 'aa' is recognized as separator, and therefore 'bb' is placed in the next cell in the table, which is unexpected result because I thought that comma inside double quote wouldn't be treated as separator.

Any advice and help will be greatly appreciated!

edit:

I accidentally deleted double quote while I editing. so "post_content was actually "post_content"

thanks for the comments!! I will read through when I get home!

Upvotes: 1

Views: 1626

Answers (3)

O. Jones
O. Jones

Reputation: 108641

You want this to export to an Excel-readable .csv

select * from table1 
  INTO OUTFILE 'sample.csv'
  CHARACTER SET utf8
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '"' LINES TERMINATED BY '\r\n';

Upvotes: 0

Danny_ds
Danny_ds

Reputation: 11406

First, there is a double quote missing in the header:

,"post_content,

Second, it looks like the parser doesn't see the \ as an escape for the embedded double quotes.

Using \" is 'Linux style', where "" is 'Windows style'. Your data also uses \r\n which is Windows style - so maybe the parser is confused?

Anyway, you should tell the parser to use \ as escape character, or just use " as the escape character when exporting.

Upvotes: 3

thunderspark
thunderspark

Reputation: 68

"post_name","post_title","post_date","post_thumbnail","post_mainimage","post_content","meta_description","text2","editor_id"
"post1","title!｜this is title","1451905200","123.jpg","123.jpg","<p>this is description</p> <h2> this is description <br>\"this is description \"</h2><p><span style=\"line-height: 1.8;\">「this is description」</span></p><p><img alt=\"aa,bb,cc \" class=\"fr-fin fr-dib\" src=\"/files/123.jpg\" title=\"aa,bb,cc \" width=\"300\"></p><p>this is description</p><p><strong>this is description</strong></p><p><span style=\"font-size: 13px;\">this is description<br>this is description</span><br></p>","this is meta_description","this is text2","12"

I think there was a misplaced double quote after "post content".

Hope this helped.

Upvotes: 0

Related Questions