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.)
November 6, 2008 at 6:58 pm
Well, I just do it on the command line like this:
usually I develop the query on terminal session and then use a command to dump the final query data to a file.
November 7, 2008 at 12:41 am
I like http://www.php.net/fputcsv because it means you don’t need to worry about escaping characters.
November 7, 2008 at 1:18 am
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.
November 7, 2008 at 5:57 am
hey Jake-The mysqldump tool works beautilfully!Thx for the link;)
November 9, 2008 at 2:37 pm
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.