MySQL: carriage-return in query

Question

I have a query that exports data from two columns of each row into a file. In the file data from each column should be separated by carriage-return, something like this:

row1column1
row1column2
row2column1
row2column2
row3column1
row3column2

I tried using char(13):

SELECT CONCAT(column1, char(13), column2) FROM my_table INTO outfile 'my_file'

and the output file seemed perfectly fine (each column data was in another line), but when I used it as input to a program that should accept described format, it didn't recognize it. However, when I manually erased all carriage-returns in the file and added them again by pressing "enter" key, my program recognized the file without a problem. When I tried with char(13), char(10), my output file looked like this:

row1column1
\
row1column2
row2column1
\
row2column1

I'm sure I'm missing something obvious here :)

Answer 1

You probably have problems with differences in interpretation of newline between operating systems.

If your MySQL database is on Unix/Linux, and a file will be read on Unix or database is on Windows and will be read on Windows then try this:

select * into outfile 'my_file'
fields terminated by '\n' lines terminated by '\n'
from my_table;

If your MySQL database is on Unix/Linux, and a file will be read on Windows then try this:

select * into outfile 'my_file'
fields terminated by '\r\n' lines terminated by '\r\n'
from my_table;

You can also convert a file between "CRLF" (Windows) and "LF" (Unix) newlines using a small, command line utility called dos2unix and unix2dos, which is included in most Linux distributions.

Answer 2

i see it in mysql site, hope its help u.

You should use the following syntax to create a CSV file in the format expected by Microsoft Excel:

... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

However fields with carriage returns may break the CSV as MySQL will automatically close a field when the \r\n line break is found. To work around this, replace all \r\n breaks with \n. The field does not close on \n breaks and it will be read into a single cell in Excel. You can do this in the same SQL statement, for example:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I also found that null values could break the CSV. These can be handled in a similar way:

SELECT IFNULL(possible_null_field, "") FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Note: this replaces NULL values with an empty string which is technically not the same thing but it will give you an empty cell in Excel instead of breaking the CSV structure and shifting the following cells to the left.

Answer 3

Haim Evgi's answer works, but:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I had to change to:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '"' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

There was a double quote in the FIELDS ESCAPED BY, which gave this error in mysql: "Error 1083: Field separator argument is not what is expected; check the manual".

Changing it to a single quote stopped the error and exported a file. I was able to then import it into excel successfully with the newlines formatted correctly.

Answer 4

Try just char(10) - that's "\n" - the UNIX way.
Just char(13) "\r" is the (old) mac way and "\r\n" is the windows way, but I suspect MySQL just uses \n for each row, so you'll need to match that.