Retuning a MySQL Query in CSV

Retuning a MySQL Query in CSV

The following is an example of how to run a query on the command line to output the result of a MySQL query to a CSV file.

Create a text file with the your query, query.sql:

SELECT * FROM hosts;

The run the following command:

mysql --skip-column-names -uuser -ppassword database < query.sql | sed 's/\t/","/g;s/^/"/;s/$/"/;' > filename.csv

This will run the MySQL query and output the results to a text file in .csv format.

The sed commands do the following:

Replace all 'tabs' with ","

s/\t/","/g;

Print a " at the beginning of the line

s/^/"/; 

Print a " at the end of the line

s/$/"/;

If you have a single column of data that is returned and want that in CSV, run an additional sed command on the output:

sed ':a;N;$!ba;s/\n/,/g' > filename.csv