MySQL Query to CSV

Question

Is there an easy way to run a MySQL query from the linux command line and output the results in csv format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee
list.csv select id, concat("\"",name,"\"") as name from students EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Take a look at `SELECT * from MyTable into OUTFILE`. MySQL reference is on [this page](http://dev.mysql.com/doc/refman/5.6/en/select.html) — , Jan 14 '14 at 06:31
mysqldump with --tab should be the easy way here with no additional scripting requirements. I wrote [3 ways to convert MySQL to CSV](http://kedar.nitty-witty.com/blog/export-mysql-database-table-to-csv-delimited-excel-file) in detail. — mysql_user, Jan 31 '18 at 05:12

score 12 · Answer 1 · edited Jan 14 '14 at 06:39

You can use "INTO OUTFILE"

I.e.

SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

This will create a file "ca.csv" with a comma between each line. There are a few other options like escaping field values, but that should be enough to get you started.

The whole thing:

mysql -u uid -ppwd -D dbname -e "SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

EDIT :- A link for you to look at: SELECT INTO

To escape the fields, you would need to add FIELDS ESCAPED BY '"' - This would add double quotes

MySQL Query to CSV

1 Answers1