rc3.org

Strong opinions, weakly held

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 Comments

  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. I like http://www.php.net/fputcsv because it means you don’t need to worry about escaping characters.

  3. Since it sounds like you have shell access to the box, try the mysqldump tool:

    http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

    It’s nice. You want the -T option.

  4. hey Jake-The mysqldump tool works beautilfully!Thx for the link;)

  5. 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.

*

© 2016 rc3.org

Theme by Anders NorenUp ↑