导出 MySQL 中的表，其中的列具有换行符

发布于 2024-10-09 05:50:27 字数 389 浏览 9 评论 0原文

我对 SQL 非常缺乏经验，所以应该有一个简单的解决方案来解决我的问题：我正在将一个表选择到逗号分隔的文件中，并且 TEXT 类型的列具有换行符，因此当我尝试将 csv 导入 Excel 时，它会在换行符后面为每段文本创建单独的行。

这是我的查询：

SELECT * FROM `db`.`table` INTO OUTFILE 'c:\\result.txt' FIELDS TERMINATED BY ','
ESCAPED BY '\\' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\r\n' ;

然后在 Excel 中我导入为逗号分隔的文件，这会导致文本包含换行符的列出现问题。

任何帮助表示赞赏！

原文

I am pretty inexperienced in SQL, so there should be a simple solution to my problem:
I am selecting a table into a comma-separated file, and the column of type TEXT has newline characters, so when I try to import my csv into Excel, it creates separate rows each piece of text following a newline character.

Here is my query:

SELECT * FROM `db`.`table` INTO OUTFILE 'c:\\result.txt' FIELDS TERMINATED BY ','
ESCAPED BY '\\' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\r\n' ;

and then in Excel I import as a comma separated file which causes issues for column that has text with newline characters.

any help is appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾城泪 2024-10-16 05:50:27

也许只需将所有内容用双引号括起来即可。

SELECT * FROM db.table INTO OUTFILE 'c:/result.txt'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Just enclose everything in double quotes perhaps.

SELECT * FROM db.table INTO OUTFILE 'c:/result.txt'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

回复收藏 0 原文

挽心 2024-10-16 05:50:27

诺维科夫是正确的，但您也可以在导出时转义换行符。

SELECT REPLACE(`fieldname1`,'\n','\\n'),`fieldname2` FROM db.table INTO OUTFILE 'c:/result.txt'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

然后，这将用文本字符串 '\n' 替换所有新行字符，但这可能不是您想要的输出。

直流

Novikov is correct but you could also escape the new line characters while exporting.

SELECT REPLACE(`fieldname1`,'\n','\\n'),`fieldname2` FROM db.table INTO OUTFILE 'c:/result.txt'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

This will then replace all the new line characters with the text string '\n' This may not be what you want in the output though.

回复收藏 0 原文

~没有更多了~