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 EOQ
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.
Choose order_id,product_title,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS Ended BY ',' ENCLOSED 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.