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

Question

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.

Answer 1

You can also use the mysql command line utility with the -e option and pipe the output to a file. This will output data in tab-delimited format.

Answer 2

Your option 1 is the best I think - do it once and print the correct options or even better, create a small script that will accept a query and automatically add your favorite enclosing & delimiter options.

You can also skip steps 2 and 3 if you install yourself the mysql client utility for your client platform and connect directly to the mysql server from your workstation if possible for you.

Answer 3

You can execute a SELECT ... INTO OUTFILE query on your local computer, and have it save the file locally. If you've got the mysql* command-line utility installed locally, simply add the -h <ip-address> flag to connect to a specific host. mysql can also read from standard input, so you can set up a .sql file that you'll use to import.

SQL file (outfile.sql):

SELECT * FROM myTable WHERE date>CURDATE()
INTO OUTFILE '/home/me/outfile.csv' -- Specify output file (if Windows, make sure
                                    -- to use backslashes and escape them 
                                    -- (C:\\Users\\<user>\\...)
FIELDS
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '"' -- Results in double quotation marks, which I have found Excel
                   -- requires (instead of using a backslash to escape)
    TERMINATED BY '\\n' -- or your line ending of choice
                        -- (i believe escaping is required)

Then you would issue the following command:

mysql -u <user> -p -h <server_ip_address> <outfile.sql

Note that it might be a good idea to create a new user that can connect from your remote IP address, with FILE permissions and only SELECT access to the tables you need to query.

It's a little bit of setup initially, but in the end, you can just stick the mysql command in a batch file and make it a two-click process.

*I believe you have to download the full MySQL system, which comes with the mysql CLI.

Answer 4

Excel has a Text to Columns feature under the Data menu which allows you to specify a custom delimeter. I am guessing Numbers would have similar functionality. If it does, then the solution is to copy and paste directly into your Numbers document and then convert those text cells to columns specifying | as the delimiter.

Answer 5

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.

Answer 6

Use your first method with the following modification:

Instead of running the command on the server you can run it on your local machine and save the file directly, without the need to sftp and delete it. For that you have to ssh forward the mysql port to a free port on your local machine.

ssh -L 3306:localhost:3306 user@remoteserver

The first "3306" is the port on the local machine. "localhost" refers to the remoteservers hostname. The second "3306" is the port on the remote machine, which shall be forwarded. If the MySQL Server runs on a different port you have to use that port instead. After logging in, you keep the ssh session open as long as you want to work.

In a new terminal window you can then start your mysql session.

mysql -hlocalhost -uUser -p --port=3306

This method has the advantage, that you don't have to expose your MySQL Server to the Internet and you are transfering all Data through an encrypted tunnel.