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