提问者:小点点

csv文件中的逗号被识别为分隔符,即使它位于双引号内


我挣扎了2天……有人知道为什么会这样吗?

我所做的:

我已经将一些数据导出为csv文件(mysql)。 用逗号分隔,用双引号括起来。

查询看起来像。。

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

下面是sample.csv的样子:

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

问题:

当我在Mac Numbers(以及其他csv查看器)上打开这个文件时,“aa”后面的逗号似乎被识别为分隔符,因此“bb”被放在表中的下一个单元格中,这是一个意想不到的结果,因为我认为双引号内的逗号不会被视为分隔符。

如有任何建议和帮助,我们将不胜感激!

编辑:

编辑时我不小心删除了双引号。 所以“post_content实际上是”post_content

谢谢你的评论!! 我回家后会通读一遍的!


共3个答案

匿名用户

首先,标题中缺少一个双引号:

,"post_content,

其次,解析器似乎没有看到\作为嵌入双引号的转义符。

使用是”Linux样式“,其中”“是”Windows样式“。您的数据还使用\r\n,这是Windows样式--所以可能解析器混淆了?

无论如何,您应该告诉解析器使用\作为转义字符,或者在导出时仅仅使用作为转义字符。

匿名用户

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

我认为在“帖子内容”之后出现了一个错误的双引号。

希望这能帮上忙。

匿名用户

您希望将其导出到Excel可读的。csv

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