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.)
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.htmldumps the results of a query in HTML format, which can easily be loaded into Excel. (Thanks, Erik.)