Export Results From MySQL Query To CSV

To export the results of a MySQL query to a CSV file use the following command.

mysql --user=username --password -h localhost database -B < query.sql | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > query_export.csv

This assumes you have created a SQL query in a file called "query.sql".

See this link to explainshell.com output for more information about the different parts of this command.

You can also use an inline query with the -e flag.

mysql --user=username --password -h localhost database -B -e "SELECT * FROM users;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > query_export.csv

The regular expression in use here works in the following way.

s/'/\'/          Replace ' with \'
s/\t/\",\"/g     Replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          At the end of the line, place a "
s/\n//g          Replace all \n (newline) with nothing

Initial code taken from https://stackoverflow.com/a/5395421.

This can also be done using awk.

mysql --user=username --password -h localhost database -B -e "SELECT * FROM users;" | awk 'BEGIN { FS = "\t"} ; {print "\""$1"\",\""$2"\",\""$3"\",\""$4"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$9"\",\""$10"\",\""$11"\",\""$12"\",\""$13"\",\""$14"\""}' > /tmp/somefile.csv

This will work as long as we have less than 14 fields in our export.