views:

1398

answers:

5

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: http://stackoverflow.com/questions/707473/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 :(

A: 

Here's an example SQL query that outputs to a comma-separated 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 INTO OUTFILE syntax writes to a file on the database server host, not the host where the shell script and the mysql client is running.

The mysql client doesn't have a csv output mode, it only has the bars-and-dashes tabular format (with the --table or -t flags), tab-separated format (with the --batch or -B flags), HTML format (--html or -H) and XML (--xml or -X).

If you have content in your database that contains tabs, then either convert tabs to something else using the REPLACE() SQL function, or else output in one of the other formats and try to convert that to csv:

mysql ... --table --skip-column-names |
sed -e 's/  *|  */,/g' \
    -e 's/^|  *//' \
    -e 's/  *|$//' \
    -e '/^+--/d'
Bill Karwin
That writes to a file on the server, not to standard out.
staticsan
@staticsan: Yes you're right. But notice "`MYSQLHOST=localhost`" in the OP's question.
Bill Karwin
I can't get it to work: echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options INTO OUTFILE 'Report/tmp2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP} I get:./report.sh: line 28: unexpected EOF while looking for matching `''./report.sh: line 33: syntax error: unexpected end of file
BassKozz
Remove the `OPTIONALLY ENCLOSED BY '"'` clause. You don't need fields enclosed by double-quotes, and that's probably what's causing the unbalanced quotes in your shell script.
Bill Karwin
staticsan has a point... I did put "localhost" in there, but that's not quite the truth, the MYSQL DB is stored on a REMOTE server, so this won't work :( I get the following when I tryed it after removing OPTIONALLY ENCLOSED BY: ERROR 1045 (28000) at line 1: Access denied for user 'user'@'123.456.789.0/255.255.254.0' (using password: YES)
BassKozz
Sorry I wasn't clear about that to begin with, I didn't think it was going to be a problem.
BassKozz
I Edited my initial question to reflect "Remote Host"
BassKozz
A: 

You don't need another script to do that. Just change:

> ${MYSQLDUMP}

at the end of your line to read:

| sed 's/\x09/,/g' > ${MYSQLDUMP}

But you need to be careful that your data doesn't have commas in it or they'll be mis-recognized by whatever uses the CSV file (but you may have that problem with tabs already).

paxdiablo
Unfortunately that doesn't work :(The "product_options" field contains items that get pickedup by the sed command and breaks them into new columns :(
BassKozz
A: 

Why not use

SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options INTO outfile '${MYSQLDUMP}' FROM ${MYSQLTABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at
Peter
Look at the comments left in Bill Karwin's Answer.I can't because it's a remote server that hosts the mysql db, and I don't have Read/Write privileges.
BassKozz
A: 

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.

BassKozz
A: 

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.

David Rabinowitz