Export CSV directly from MySQL
How ofter were you asked by a client for a CSV (or excel) file with data from their app? I get asked that question quite often, so I wanted make the process as easy as possible. And guess what? You can create CSV files directly from MySQL with just one query!
Let’s say you want to export the id, name and email fields from your users table to a CSV file. Here is your code:
SELECT id, name, email INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ‘\\’ LINES TERMINATED BY '\n' FROM users WHERE 1
Well, if you know MySQL, you’ll know how to customize this query to spit out the the right data. Your csv file can be found in /tmp/result.csv
Make sure your MySQL server has write permissions to the location where you want to store your results file.
it sounds easy …
Thank You! Post in my blog in Russian translation.
[...] Оригинал: Export CSV directly from MySQL [...]
Probably want to allow for special characters, too:
SELECT id, name, email INTO OUTFILE ‘/tmp/result.csv’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
FIELDS ESCAPED BY ‘\\’
LINES TERMINATED BY ‘\n’
FROM users WHERE 1
@Gary: Great tip! Thanks!
Hi! I believe there is a syntax error in your code. The second occurrence of the FIELDS keyword is not valid. Rather, the correct syntax is:
FIELDS …multiple clauses follow…
with multiple clauses being: TERMINATED BY, OPTIONALLY ENCLOSED BY and ESCAPED BY
BTW – The reverse – load CSV directly into a table – is possible too, with LOAD DATA INFILE:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
This uses similar clauses to specify the layout of the CSV file:
LOAD DATA INFILE ‘/tmp/result.csv’
INTO my_table
FIELDS
TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ESCAPED BY ‘\\’
LINES
TERMINATED BY ‘\n’
(You can even do some preprocessing and apply calculations and such on the csv values before dumping them to the table.
kind regards,
Roland Bouman
You can use the CSV engine too :
create table report engine=CSV select * from your_table;
You just have to send the report.CSV file to your customer
@Cyril: I’m not sure that works all the time. When I run that query it just generates a new table ‘report’ with (in your example) the same data and structure as your_table.
I don’t think you want to create a new table just to create CSV, if that’s what’s actually happening.
Also, it’s unclear to me where the CSV file is you should send to the customer.
Like Cyril mentioned, there is an actual CSV engine. It doesn’t work for Windows prior to MySQL 5.1x, but it’s in 5.x for all other platforms.
Just do a CREATE TABLE blah ENGINE=CSV and select into it. You can even select from the CSV table and insert more rows and delete rows from it as a normal table (no indexing is supported).
In each of these cases, you would have to take an additional step of grabbing the file from the file system. Another option is that you could generate the CSV directly. To do this you would need to clump all of the rows together into one column, which requires group_concat. Group concat has a limit of 500 characters so, right before your select you want to change the group_concat_max_len size. I change it to some large number. The following would best be in a stored procedure:
SET GROUP_CONCAT_MAX_LEN = 99999999999;
SELECT
CONCAT_WS(”,’”id”,”name”,”email”‘,
GROUP_CONCAT(
CONCAT_WS(”,’\n”‘,`id`,’”,”‘,`name`,’”,”‘,`email`,’”‘)
SEPARATED BY ”
)
)
AS csv_output
FROM `users`;
Then your app can simply grab the var csv_output, create the file itself, store it somewhere, basically anything you want. Of course you could also add an INTO DUMPFILE statement.
I find it truely amazing how many solutions there are to this one problem ;-)