Export table to MySQL with columns having newline characters

Question

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

Here is my request:

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-delimited file, which causes problems for a column with text with newline characters.

any help is appreciated!

Answer 1

Just include everything in double quotes.

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

Answer 2

Novikov is correct, but when exporting, you can also avoid the appearance of new characters.

 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 replace all newline characters with the text string '\ n'. Perhaps this is not what you want in the output.

DC