exporting a table in MySQL with columns that have newline characters

mysql sql into-outfile
Lenik picture Lenik · Dec 28, 2010 · Viewed 14.6k times · Source

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!

Answer

Novikov picture Novikov · Dec 28, 2010

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

Related questions

MySQL export into outfile : CSV escaping chars

I've a database table of timesheets with some common feilds. id, client_id, project_id, task_id, description, time, date There are more but thats the gist of it. I have an export running on that table to a CSV …

Read More
How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do: SELECT column1, column2 INTO OUTFILE 'outfile.csv' FIELDS TERMINATED BY ',' FROM table_name; outfile.…

Read More
MySQL - SELECT * INTO OUTFILE LOCAL ?

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client. So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE .... …

Read More