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.

Add new comment

The content of this field is kept private and will not be shown publicly.