export mysql table into csv where data contains newline within column

Question

I tried to export a mysql table into csv file, the table contains wild character such as newline in several columns. The following csv file shows column improperly separated when opening in excel/csved, what's wrong?

SELECT * INTO OUTFILE 'table.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' escaped by '\\' LINES TERMINATED BY '\n'  FROM mytable limit 10

Can you connect to your db from Excell with ODBC? This should be much easier to get right than CSV. — Andrew
@Andrew: the remote db is too large(TBs) to be directly exported from excel. and the process needs to be scripted in linux shellscript — user121196

DaveM DaveM · Accepted Answer · 2015-05-07T19:45:14

During the export process of mysql you can suggest an alternate field separator, rather than a comma. Just select a character(s) that is unlikely to appear in any of your output fields ~ personally iI have done this and selected a couple of characters together, such as '£€£'.

Then. When you open the file in xl you can tell xl what the field and line separators are.

The info for the export syntax can be found with the load data on file documentation.

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

David.

export mysql table into csv where data contains newline within column

1 Answers