BASH MySQL Query to Comma Separated File
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 outputs my MySQL query to a TAB shared file . report.csv. However, I need it to output to the " COMMA " section . I realize I can create another script to convert this file from TAB to shared COMMA, however I would rather keep this step if I could. So how can I get MySQL to dump the file in comma separated format?
EDIT: I found this solution: How do you output the results of MySQL queries in csv format (to the screen, not to a file)?
However, I cannot 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}
Does not work: (
source to share
Found a simple perl 1-liner that seems to do the trick:
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F '
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 VoteUp to your answer, but I don't have enough reputation :( Once I get more reputation, I'll come back and vote for your answer. Thanks again.
source to share
Do 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 command tr
replaces one char with another.
source to share
Here's an example SQL query that outputs to a comma delimited file:
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
Re comments: Fair enough, the syntax is INTO OUTFILE
written to a file on the host of the database server, not on the host where the shell script and the mysql client are running.
The mysql client does not have a csv output mode, it only has a tabular format of bars and hyphen (with flags --table
or -t
), tab-delimited format (with --batch
or -B
), HTML ( --html
or -H
) and XML ( --xml
or -X
) format .
If you have content in your database that contains tabs, then either convert the tabs to something else using the REPLACE()
SQL function , or output them to one of the other formats and try converting them to csv:
mysql ... --table --skip-column-names |
sed -e 's/ *| */,/g' \
-e 's/^| *//' \
-e 's/ *|$//' \
-e '/^+--/d'
source to share
You don't need another script for this. Just change:
> ${MYSQLDUMP}
at the end of the line to read:
| sed 's/\x09/,/g' > ${MYSQLDUMP}
But you need to be careful that your data does not contain commas or it is misrecognized as using the CSV file (but you may have this tabbed issue already).
source to share