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 ;-)
Hi,
Here is a trigger which helps you to generate cvs on insert over a tabel
CREATE TRIGGER ins_trig3 After INSERT ON RECEIPTS
FOR EACH ROW
BEGIN
SELECT NEW.id,NEW.MONEY,NEW.DATENEW INTO OUTFILE ‘C:/test.csv”‘ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ FROM RECEIPTS WHERE RECEIPTS.ID=NEW.id;
END;
Can anyone tell me is it possible to give Headings for the column, right now it is just passing data ?
Help me.
Scenerio: User creates contact informaion through my application and export to .csv format. which he imports to his mail address (lotus notes). Lotus notes requires column heading to recognize the data.
[...] A partir de MySQL 5.1 el motor de almacenamiento en CSV (Valores separados por comas) viene instalado por defecto en el servidor MySQL, lo que nos facilita un método para exportar datos de tablas en este formato, utilizándolo de forma nativa. También es posible exportar datos en este formato directamente a través de una sola consulta, podéis verlo aquí. [...]
I am trying this and cant figure it out….I am using the MySQL Query Browser and type this in and get an error that “No Database Selected”
I am very new to this so sorry for the easy question
case_num is a field
indicent_case is the table
SELECT case_num INTO OUTFILE ‘c:/Temp/result.csv’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
FROM incident_case WHERE 1;
Hi,
I am trying to do as you say but I am getting the following error:
mysql> SELECT * INTO OUTFILE ‘/opt/oracle/export_histo_puestos.csv’
-> FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\n’
-> FROM histo_puestos;
ERROR 1045 (28000): Access denied for user ‘dwhouse’@'%’ (using password: YES)
I am connecting with a client to a remote database.
Even I tried excecuting:
mysqldump -u dwhouse -p -t -T/opt/oracle/ –fields-terminated-by=’,’ –host=172.16.0.12 mybase table_i_want
and got the same error
I would appreciate If anyone can help me.
Thanks!