How do you output MySQL query results in csv format (to the screen, not to a file)?

Question

I'm trying to output query results in comma delimited format using the mysql command line tool. My mysql user does not have access to use the "INTO OUTFILE" option referenced in this question:

http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format

I'm also aware of the -H and -X options to format output in html and xml respectively, but is there no way to output csv format directly to the screen?

I found this method using sed - http://tlug.dnho.net/?q=node/209 . But, I'm curious to find a straight mysql solution.

Any ideas?

Answer 1

I've been using MySQL for years, and I don't know of any way to use the mysql command-line client alone to produce CSV output, except for the solutions you've already found.

There are a couple of feature requests on file for CSV output support:

• http://bugs.mysql.com/bug.php?id=5707
• http://bugs.mysql.com/bug.php?id=25243

The only other solution I would suggest is to write a script in Perl or PHP, to fetch data from MySQL and output it in CSV format or any other format you want. This is not a difficult script to write.

Answer 2

If you have access to mysqldump you can use something like this

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=\; --fields-terminated-by=,

Answer 3

I ended up just taking the tab delimited output and copy pasting it to a spreadsheet and then exporting that to csv. Also realized that I could have used the concat or concat_ws function to do the job and will do that next time.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

SELECT CONCAT_WS(',', field1, field2, field3) FROM table;

Answer 4

The CLI can output in tab-delimited format, which is nearly good enough (I speak from experience). Use the -B option. The biggest problem with tab-delimited is that I couldn't make Excel import that format. However, OpenOffice does.