BASH MySQL Query to Comma Separated File

Question

I have the following bash script:

#!/bin/sh

MYSQLHOST="mysql.remote-host.com"
MYSQLDB="mysqldb"
MYSQLTABLE="table"
MYSQLUSER="user"
MYSQLPASS="pass"
MYSQLDUMP="Report/report.csv"
LOG="Report/report.log"

MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}"


echo "Report Begin: $(date)"
 echo "MySQL Dump Begin: $(date)" >> ${LOG}
  echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}
 echo "MySQL Dump End: $(date)" >> ${LOG}
echo "Report Successful: $(date)"

This ouputs my MySQL Query into a "TAB" separated file report.csv. However, I need to have it output to a "COMMA" separated file. I realize I could create another script to convert this file from TAB to COMMA separated, however, I'd rather save the step if I can. So how can I have MySQL dump the file in comma separated format?

EDIT: I did find this solution: How do you output MySQL query results in csv format (to the screen, not to a file)?

However I can't seem to get it to work:

echo "SELECT CONCAT_WS(',', ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options) FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}

Doesn't work :(

Solution 1

Found a simple perl 1-liner that seems to do the trick:

perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F '

-Perl Script

So the line in my script looks like this:

        echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} | perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' > ${MYSQLDUMP}

Thanks for all the help, Especially from Bill Karwin, I wanted to add a VoteUp to your answer but I don't have enough reputation :( Once I get more rep, I'll circle back and vote your answer up. Thanks again.

Solution 2

Run the following:

echo "SELECT CONCAT_WS(',', ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options) FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | \
mysql ${MYSQLOPTS}  | tr '\t' ',' > ${MYSQLDUMP}

The tr command replaces one char with another.