CODEWITHSUNDEEP
mysqlcsv
RyanW
RyanW

Reputation: 5418

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

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:

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?

Upvotes: 28

Views: 53931

Answers (9)

Dom Storey
Dom Storey

Reputation: 101

Pipe the answer to tr, like this: 

mysql <blah blah> -B | tr '\t' ','

Upvotes: 9

Artur Siara
Artur Siara

Reputation: 166

following the Change output format for MySQL command line results to CSV

mysql --skip-column-names --batch -e 'select * from dump3' t | awk -F'\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\\t/,"\t",$i); gsub(/\\n/,"\n",$i); gsub(/\\\\/,"\\",$i); gsub(/"/,"\"\"",$i); printf sep"\""$i"\""; sep=","; if(i==NF){printf"\n"}}}'

Upvotes: 2

Nevaar
Nevaar

Reputation: 41

mysql --raw --skip-column-names -u someuser -psomepass -h somehost -b somedatabase -e "select * from somedatabase.sometable;"| awk -F\\t '{print $1","$2","$3}'

-F\\t tells awk to use a tab as the delimeter on its input, and we print out the desired columns with commas as delimeters using the "," bit.  Replace "," with ":" or "|" or whatever to suit your needs.

If you need to quote an ouput field, lets say $1, your awk print would look like this:

awk -F\\t '{print "\""$1"\","$2","$3}'

Upvotes: 4

Paul Ericson
Paul Ericson

Reputation: 917

If you are using mysql interactively, you can set your pager to use sed like this:

$ mysql -u <user> p<passwpd>
mysql> pager sed 's/,/\n/g'
PAGER set to 'sed 's/,/\n/g''
mysql> SELECT blah FROM blah WHERE blah = blah

.
.
.
"blah":"blah"
"blah":"blah"
"blah":"blah"

If you don't use see as the pager the output is like this:

"blah":"blah","blah":"blah","blah":"blah"

Upvotes: 1

John
John

Reputation: 21

If you have MySQL Workbench installed, you can run a query then click "export" above the results. It will give you the option of saving as .CSV.

Upvotes: 2

staticsan
staticsan

Reputation: 30575

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.

Upvotes: 1

RyanW
RyanW

Reputation: 5418

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;

Upvotes: 21

Alekc
Alekc

Reputation: 4770

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=,

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 563021

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:

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.

Upvotes: 4

Related Questions