exporting a table in MySQL with columns that have newline characters

Question

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!

Ring

Answer 1

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';

Palmetto

in this case if I have a text field that has a value "abc, bcd" this text field will be parsed into two text fields abc and bcd since there is a comma inside. – Ring

commas inside a quote enclosed field should be ignored by excel. When importing into excel make sure that you have text qualifier set to " – Peroxide

Answer 2

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.

DC

Peroxide