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, Commented Jan 14, 2012 at 2:01
@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
– user121196, Commented Jan 14, 2012 at 2:11

DaveM · Accepted Answer · 2015-05-07 19:45:14Z

0

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.

answered May 7, 2015 at 19:45

DaveM

7345 gold badges13 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

export mysql table into csv where data contains newline within column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related