How can I output MySQL query results in CSV format?

Asked 2008-12-10T15:59:51.733

1461

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.

mysql csv quotes

Edit Report

2 Answers

Use:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

answered 2011-11-10T18:41:05.193

Here's what I do:

echo $QUERY | \
  mysql -B  $MYSQL_OPTS | \
  perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
  mail -s 'report' person@address

The Perl script (snipped from elsewhere) does a nice job of converting the tab spaced fields to CSV.

answered 2011-07-07T17:34:02.467

How can I output MySQL query results in CSV format?

2 Answers

Your Answer