SQL to CSV; simple question?

donnied · 06-29-2009, 08:01 PM

I have an SQL database running on a remote host (I don't know how I would access files saved to that machine).

How do I save (or get) the query output to a CSV file?

propofol · 06-29-2009, 10:01 PM

If the remote machine allows client connections you could login with (at least with mysql):

Code:

mysql --host=xxx.xxx.xxx.xxx --user=dbaselogin --password=mypassword

If you can't there is always the option of scp, ftp or http to transfer the resulting text file.

Try:

Code:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

on the client computer.

Copied from http://www.tech-recipes.com/rx/1475/...t-or-csv-file/

Regards,
Stefan

donnied · 07-01-2009, 03:45 PM

I couldn't write using the output command. If I had I don't know how I would have gotten the file.

Instead I just used redirects:

Code:

mysql --host=xxx.xxx.xxx.xxx --user=dbaselogin --password=mypassword < mysqlscript.sql > myoutputfile.csv

Thank you for your tips and suggestions.