Export result of query from mysql console to CSV file
Sure all GUI DB tools can do export of result of query into CSV file. But what if you have to do this from command line? There is a simple way perform that task. Here is an example:
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
When you run that query all records from the table products will be dumped into file /tmp/products.csv. All fields of that dump will be delimited by comma, enclosed by double quotes and escaped by two slashes. Each row will be ended by end of the line character.
Sure you can change delimiter, enclose, escape symbols as well as add any others SQL statements such JOIN, WHERE, LIMIT ORDER etc. Enjoy!
UPDATED: Don’t forget to drop the export file before re-export data. Otherwise you’ll get the error:
ERROR 1086 (HY000): File '/tmp/products.csv' already exists
[via Electrictoolbox]
Thanks for bringing back some very old memories!
I have used this many years ago, in combination with the related LOAD DATA INFILE or LOAD DATA LOCAL INFLINE SQL statements, to do the reverse, which may look a little something like this:
LOAD DATA LOCAL INFILE ‘/tmp/products.csv’
INTO TABLE products
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
ESCAPED BY ‘\’
LINES TERMINATED BY ‘\n’
This one troubled me for a while, but your solution worked like a charm. Thanks for post.