MySQL query to print output as CSV to standard output

Question

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.

Gilbertina

Possible duplicate of Change output format for MySQL command line results to CSV – Winson

not a dupe, this is a question about routing to stdout. – Unintentional

Answer 1

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://mcmap.net/q/334611/-fastest-way-convert-tab-delimited-file-to-csv-in-linux 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

Fugger

Answer 2

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

Diatropism

Answer 3

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

This will redirect the stderr

Bagnio

This doesn't seem to solve the problem, which is if you don't specify an OUTFILE you can't use the subsequent "FIELDS TERMINATED BY" and such so you actually get CSV output! – Iterative