MySQL: carriage-return in query
carriage-return, mysql
Solution
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.
Problem
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 :)