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

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.