28

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?

Community
  • 1
  • 1
RyanW
  • 5,338
  • 4
  • 46
  • 58

9 Answers9

21

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;

RyanW
  • 5,338
  • 4
  • 46
  • 58
  • 3
    You may want to use '--silent' option to remove tabular output. See http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_silent – Nobu Jul 10 '11 at 22:17
  • Careful with `CONCAT_WS()`, as it will skip `NULL` values (resulting in an uneven column count). You may want to use something like `IFNULL(column, '')` to avoid this issue. – Marco Roy Aug 21 '19 at 18:13
9

Pipe the answer to tr, like this: 

mysql <blah blah> -B | tr '\t' ','
Toby Speight
  • 27,591
  • 48
  • 66
  • 103
Dom Storey
  • 101
  • 1
  • 1
4
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}'
Edd
  • 3,724
  • 3
  • 26
  • 33
Nevaar
  • 41
  • 1
4

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
3

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=,
Alekc
  • 4,682
  • 6
  • 32
  • 35
  • Thanks for the idea. I think that would've worked around my permissions issue. But I'd have to further filter records and fields because it would dump an entire table and I was just trying to get a small subset of the table. – RyanW Apr 01 '09 at 22:59
  • 1
    Recent versions of mysqldump take a -w parameter for providing a WHERE clause to the dumping table. – staticsan Apr 01 '09 at 23:07
  • Thanks, then that would be a great alternative. – RyanW Apr 02 '09 at 22:56
  • This only works if you have access to the database server, as `-T/path/to/directory` refers to a directory on the MySQL server, not on your local machine. So this can't be used for most DBs in the cloud. – Marco Roy Aug 21 '19 at 18:21
2

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.

John
  • 21
  • 1
2

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"}}}'
Community
  • 1
  • 1
Artur Siara
  • 166
  • 1
  • 5
  • That sure is a lot more complicated than the accepted answer. Why is this better? – Mike Oct 13 '16 at 15:01
  • 1
    hi, because all of these solutions propagate the same mistake. Don't fulfill basic rules of CSV format: https://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules * Fields with embedded commas or double-quote characters must be quoted. * Each of the embedded double-quote characters must be represented by a pair of double-quote characters. * Fields with embedded line breaks must be quoted The result will be proper one only then, when there are no comma nor " nor new-line characters in the value. – Artur Siara Oct 17 '16 at 14:13
  • 1
    For example, the result for such two columns couldn't be readable properly: mysql> select * from csvdata; | column1 | column2 | +--------------------------+----------------------------------+ | Super, "luxurious" truck | Second column, and another comma | Answer with SELECT CONCAT_WS `mysql> SELECT CONCAT_WS(',',column1, column2) from csvdata; +-----------------------------------------------------------+ | Super, "luxurious" truck,Second column, and another comma |` – Artur Siara Oct 17 '16 at 14:13
  • Answer with awk (the same is with the sed example): `mysql --raw --skip-column-names -uroot -p t -e 'select * from csvdata' | awk -F\\t '{print "\""$1"\","$2}' "Super, "luxurious" truck",Second column, and another comma` As you see there is no posibility to distinct the right fields because of confusion with , and " – Artur Siara Oct 17 '16 at 14:13
  • in my solution result would be: `"Super, ""luxurious"" truck","Second column, and another comma"` – Artur Siara Oct 17 '16 at 14:15
  • 1
    OK, that makes a lot of sense. You should have added that to the answer though, especially since it is now over 7 years old and contains an accepted solution. – Mike Oct 17 '16 at 15:46
  • thanks for your attention. How to achieve that besides of making comment to the current one? and I have not enough reputation to event make a comment. – Artur Siara Oct 17 '16 at 19:47
  • Underneath your answer there is an "Edit" link. Click on that and you can edit your own answer. You can always edit your own questions and answers, even if you don't have any reputation points. If you edit other people's questions and answers, however, it needs to be approved by 5 other people before it is active. – Mike Oct 18 '16 at 14:34
1

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"
Ankit Sharma
  • 3,923
  • 2
  • 29
  • 49
Paul Ericson
  • 777
  • 2
  • 7
  • 15
1

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.

staticsan
  • 29,935
  • 4
  • 60
  • 73
  • Exactly, I used the -B option and took the output and pasted it into Numbers, which seem to accept it fine. – RyanW Apr 01 '09 at 23:21
  • 1
    Thanks, this worked great. Then you can use sed to make it a CSV. Make sure to type Ctrl-V before the tab in the Sed statement. mysql -B -uroot foo -e 'SELECT email,country FROM email_signups' | sed -e 's//,/g' > foo.csv – Andrew Hopper Aug 24 '10 at 20:57