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?

Improve this question

9 Answers 9

Reset to default
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;

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You may want to use '--silent' option to remove tabular output. See dev.mysql.com/doc/refman/5.5/en/…
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.
9

Pipe the answer to tr, like this: 

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

1 Comment

ah it missed stuff in angle brackets... i mean mysql {blah blah} -B ...
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.

Improve this answer

1 Comment

Good to know I'm not missing something obvious. Thanks!
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}'
Improve this answer

Comments

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=,
Improve this answer

4 Comments

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.
Recent versions of mysqldump take a -w parameter for providing a WHERE clause to the dumping table.
Thanks, then that would be a great alternative.
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.
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.

Improve this answer

Comments

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"}}}'
Improve this answer

8 Comments

That sure is a lot more complicated than the accepted answer. Why is this better?
hi, because all of these solutions propagate the same mistake. Don't fulfill basic rules of CSV format: 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.
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 |
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 "
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.
|
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.

Improve this answer

2 Comments

Exactly, I used the -B option and took the output and pasted it into Numbers, which seem to accept it fine.
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/<Ctrl-V><tab>/,/g' > foo.csv
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"
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.