Take the 2-minute tour ×

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.
share|improve this question
What do you mean by "ssh into the server and grab the file"? Or did you mean sftp? –  Gladwin B Feb 25 '10 at 3:40
I will correct the original post. Thank you. –  Gattster Feb 25 '10 at 8:28
add comment

6 Answers

up vote 7 down vote accepted

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:




--batch, -B



share|improve this answer
add comment

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.

share|improve this answer
Should this produce CSV? mysql -e "show databases;". It appears to be normal mysql output with |'s instead of commas. –  Gattster Jan 14 '10 at 20:33
@Lucas Oman: false. mysql -e will give TAB separated output, if mysql is piped to something else or if used in conjunction with the -B (batch) option. –  ggiroux Feb 26 '10 at 3:01
add comment

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):

INTO OUTFILE '/home/me/outfile.csv' -- Specify output file (if Windows, make sure
                                    -- to use backslashes and escape them 
                                    -- (C:\\Users\\<user>\\...)
    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.

share|improve this answer
add comment

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.

share|improve this answer
Great answer. This is exactly what I was looking for. I hit "accept answer" on the wrong answer though, so sorry you didn't get the bounty points. I owe you a beer. –  Gattster Mar 2 '10 at 19:03
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


