Everytime I need to do this I go into at least five minutes of Googling for a decent solution. The problem is that a lot of people don’t have access to the commandline, so the solutions all reference PHPMyAdmin which is, you know, OK, but I don’t use it or want to install it just to export some quick reports. Other solutions are similarly indirect.
Anyway, for my own benefit, here’s the solution I found yesterday:
SELECT
some, columns, from, my, table
FROM
a_table
WHERE
whatever = 'something'
INTO OUTFILE '/tmp/outfile.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
This seemed to do the job, although I did have a mild concern that it might not be escaping “stuff” properly. We shall see.
I just spent a few hours figuring this one out and there doesn’t seem to be much info online about it so I thought I’d share.
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 ‘rn’;
However fields with carriage returns may break the CSV as MySQL will automatically close a field when the rn line break is found. To work around this, replace all rn 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, ‘rn’, ‘n’) FROM table INTO OUTFILE ‘/temp.csv’ FIELDS ESCAPED BY ‘””‘ TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘rn’;
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 ‘rn’;
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.
Very handy Sam, thanks for sharing.