我挣扎了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
谢谢你的评论!! 我回家后会通读一遍的!
首先,标题中缺少一个双引号:
,"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';