Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert mysql query results to CSV (with copy/paste)

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?

Method 1: Select into outfile

You can select into an outfile directly from MySQL, but this takes several steps.

  1. export your query with all the necessary arguments to make it a CSV format, like FIELDS OPTIONALY ENCLOSED BY and DELIMITED BY.
  2. sftp into the server and grab the file
  3. delete the file from the server

Method 2: Copy/paste

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.

  1. Copy/paste to a local text file
  2. Open in a text editor and replace | with ,
  3. Save as a CSV and open in Numbers.
like image 760
Gattster Avatar asked Jan 14 '10 19:01

Gattster


People also ask

How do I export a MySQL database to a CSV file?

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.


1 Answers

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.

like image 135
Steve Madere Avatar answered Sep 21 '22 12:09

Steve Madere