The quickest route from database results to CSV
5

The quickest route from database results to CSV

I’m wondering what the quickest route is from the results of a SELECT statement in the MySQL command line client to a CSV file that can easily be imported into Excel. Using INTO OUTFILE is an option but only if you have the proper privileges and have access to the filesystem on the server. It won’t select into a file on the system where the client is running. (This seems like a huge oversight to me.)

There are also GUIs (like phpMyAdmin) that provide this functionality, but lots of systems administrators avoid it because of security holes.

Usually, I just whip up a Perl script to query the database and dump the results to CSV, but I’m wondering if there isn’t an easier way.

By the way, in researching this blog post, I noticed that it’s really easy to set your MySQL prompt to something much more useful than the default. For more, see the mysql client documentation. For now I’m going with (\u@\h) [\d]>.

Update: mysql -H -e "select * from whatever" > outfile.html dumps the results of a query in HTML format, which can easily be loaded into Excel. (Thanks, Erik.)

5 thoughts on “The quickest route from database results to CSV

  1. Well, I just do it on the command line like this:

    echo "SELECT * FROM users" | mysql  db_development > myUsers.csv
    

    usually I develop the query on terminal session and then use a command to dump the final query data to a file.

  2. mysqldump is cool for dumping the full contents of tables, but I find myself running complex SELECT statements that use GROUP BY and needing those results in Excel so that they can be further analyzed.

    I didn’t know about the -T flag for it, though. That’s useful.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>