CODEWITHSUNDEEP
mysqlsqlinto-outfile
Blaine Lafreniere
Blaine Lafreniere

Reputation: 3610

MySQL query to print output as CSV to standard output

I want to do the following mysql -uuser -ppass -h remote.host.tld database < script.sql

where script.sql contains the following

SELECT *
FROM webrecord_wr25mfz_20101011_175524
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'

I want CSV output directed to standard out. The reason is because running this query with an INTO OUTFILE 'blah.csv' will save the file on the remote host. I want the file saved on the local host.

If I could just redirect the standard output to a file, that would be dandy.

Upvotes: 21

Views: 13819

Answers (3)

Eugene
Eugene

Reputation: 1639

The answers above don't seem to fully answer the original question, and I'm not sure if this does either, but hopefully this might help someone:

See How to output MySQL query results in CSV format? for a lot of comments regarding how to sed. For example, based on the original parameters, the following might be sufficient:

mysql --batch -u user -h remote.host.tld database --port 3306 -ppass -e "SELECT * FROM webrecord_wr25mfz_20101011_175524;" | sed 's/\t/,/g' 2>&1

This is similar to the answer above, but redirecting to stdout instead of blah.csv.

However, (although not sure if this will work if you need to preserve tabs, there are many ways to address this though), I've used https://stackoverflow.com/a/2543226/2178980 to correctly escape double quotations and convert to comma-separated:

mysql --batch -u user -h remote.host.tld database --port 3306 -ppass -e "SELECT * FROM webrecord_wr25mfz_20101011_175524;" | perl -lpe 's/"/\\"/g; s/^|$/"/g; s/\t/","/g' 2>&1
  • Execute the sql "SELECT * FROM webrecord_wr25mfz_20101011_175524;" via mysql (this output will be tab-separated)
  • Convert to comma-separated by piping to perl -lpe 's/"/\\"/g; s/^|$/"/g; s/\t/","/g'
  • Have the output go to stdout by appending 2>&1

Upvotes: 5

instaflow
instaflow

Reputation: 48

In part a duplicate question to MySQL SELECT INTO OUTFILE to a different server. FIELDS TERMINATED BY can't be used without into outfile

A (not so elegant) alternative is using the --batch option to produce tab separated output and sedding the stdout. Something like this:

mysql --batch -uuser -ppass -h remote.host.tld database < stack.sql | sed 's/\t/,/g' > blah.csv

Be aware that --batch escapes special characters so depending on the data you have and its predictability, you might need to change the sed

Upvotes: 2

700 Software
700 Software

Reputation: 87913

Try this: mysql -uuser -ppass -h remote.host.tld database < script.sql 2> blah.csv

This will redirect the stderr

Upvotes: 0

Related Questions