I often work in command line mysql. A common need is to take a query's results and import them into a Numbers document (similar to an Excel document).
What is the fastest method for doing this?
You can select into an outfile directly from MySQL, but this takes several steps.
FIELDS OPTIONALY ENCLOSED BY
and DELIMITED BY
. I tend to do this method. For me it seems a little faster but that's mostly because I don't remember how to construct the SELECT INTO OUTFILE
query from above and have to look it up.
Select the table of the database that you want to export and click on the Export tab from the right side. Select the CSV format from the Format drop-down list and click on the Go button. Select the Save File option and press the OK button. The file will be downloaded in the Downloads folder.
How about this?:
mysql -B -e "$MY_QUERY" > my_data.csv
The output format is actually tab-separated rather than comma-separated but at least Excel and OpenOffice Calc automatically adapt to this.
BTW, for convenience and to enable non-interactive execution of
mysql commands, I strongly recommend setting up a secure ~/.my.cnf file
(readable only by you) with entries like this:
[client]
user=YOUR_MYSQL_USER_NAME
password=YOUR_MYSQL_PASSWORD
host=YOUR_MYSQL_SERVER
port=YOUR_MYSQL_SERVER_PORT
WHATEVER_OTHER_OPTIONS_YOU_LIKE
References:
http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html
--batch, -B
Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.
Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With