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