Can there be a good way to operate a MySQL query in the linux command line and output the outcomes in csv format?

Some tips about what I am doing now:

mysql -u uid -ppwd -D dbname << EOQ  sed -e 's/        /,/g'  tee list.csv

choose id, concat(""",title,""") as title

from students


It will get untidy when you will find lots of posts that should be encircled by quotes, or maybe you will find quotes within the results that should be steered clear of.

From http://world wide

Choose order_id,product_title,qty

FROM orders

INTO OUTFILE '/tmp/orders.csv'

FIELDS Ended BY ','


LINES Ended BY 'n'

$ mysql your_database --password=foo < my_demands.sql > out.csv

The OUTFILE solution distributed by Paul Tomblin leads to a file to become written around the MySQL server itself, so this works only when you've FILE access, in addition to login access or any other method for locating the file from that box.

Without having such access, and tab-delimited output is really a reasonable replacement for CSV (e.g., in case your finish goal would be to import to Stand out), then Serbaut's solution (using mysql --batch and optionally --raw) is what you want.

mysql --batch, -B

Print results using tab because the column separator, with each row on the new line. With this particular option, mysql doesn't make use of the history file. Batch mode leads to non-tabular output format and getting away of special figures. Getting away might be disabled by utilizing raw mode see the description for that --raw option.

This provides you with a tab separated file. Since commas (or strings that contains comma) aren't steered clear of it's not straightforward to alter the delimiter to comma.

Alternatively towards the answer above, you'll have a MySQL table that utilizes the CSV engine.

Then you'll have a file in your hard disk drive that will be inside a CSV format for you to just copy without processing it.

Some tips about what I actually do:

echo $QUERY mysql -B $MYSQL_Decides perl -F"t" -lane 'print join ",", map @F ' mail -s 'report' person@address

The perl script (sniped from elsewhere) does a pleasant job of transforming the tab spread fields to CSV.

Hope that can help someone!

What about this:

mysql your_database -p < my_demands.sql  awk ' > out.csv

The stan Alternative wordks pretty much, just make certain to not put begin and finish quotes in your query.

Here is a fairly gnarly method of doing the work. Thought it was somewhere, can't take any credit

mysql --user=wibble --password wobble -B -e "choose * from vehicle_groups" sed "s/'/'/s/t/","/gs/^/"/s/$/"/s/n//g" > vehicle_groups.csv

Works pretty much. Once more though a regex proves write only.

You should use REPLACE() inside your query to achieve the quotes steered clear of.