CODEWITHSUNDEEP
mysqlcsv
user121196
user121196

Reputation: 31020

export mysql table into csv where data contains newline within column

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

Upvotes: 3

Views: 1571

Answers (1)

DaveM
DaveM

Reputation: 734

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.

Upvotes: 0

Related Questions