Dump MySQL table into CSV file
I needed to quickly dump bunch of MySQL tables into Excel so that our directors can play around with the data. It does make sense, believe me. It would take me at least a day or two to implement, test, and integrate the statistical analysis they want to do on the data into our web application. Meanwhile they can perform the same calculations on an excel sheet in the next 15 minutes. So I get to add bunch of items to my todo list, and they get bunch of data to play around with. Everyone wins 
And yes, they specifically asked for Excel format, and the future reports must have “export to Excel” feature.
Anyways, here is why I love MySQL:
SELECT * INTO OUTFILE 'result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM my_table;
You get a CSV file formated nicely for Excel. Now you can just open it, and save it as xls file if that is what you need.
If your table is to big for Excel you can of course use a WHY clause to cut down the number of results.
Related Posts:
October 2nd, 2007 at 11:42 am (6382) [Quote]
Hi! I had the same problem and I developed 2 PHP classes to solve the problem.
One is IAM_XLS, which exports data in Excel’s BIFF format. The class supports only text, integer and float value types but it’s kind of handy when all you need to do is dump a query result. It also has a “dump” function that does just that for you.
The other, named IAM_CSVDump, does the same as your SQL statement but adds a couple of features that make downloading the file a nicer experience.
Kind regards,
Posted usingIván
October 2nd, 2007 at 12:05 pm (6383) [Quote]
Nice! Thanks. I actually wrote a short PHP script that does the CSV export here.
I’ll definitely take a look on the BIFF format one though. Could be useful for the future.
Posted usingApril 28th, 2008 at 6:16 pm (8907) [Quote]
awesome! thanks for the tip
Posted usingJune 11th, 2008 at 11:08 am (9315) [Quote]
Nice one! Exactly what I needed for some development I’m doing.
I actually don’t care about the CSV bit, but was looking for a quick way to dump a single record from a table to file.
This is a hell of a lot more efficient than some other methods!
Posted usingSeptember 10th, 2008 at 11:52 am (10119) [Quote]
Perfect. Note the default location the file is saved in windows is, eg:
Posted usingC:\Program Files\MySQL\MySQL Server 5.0\data\myDatabaseName
September 25th, 2008 at 3:15 pm (10259) [Quote]
I can’t seem to get Ivan’s IAM_XLS to work. When I modify the example.php file and access it via my website I get the following:
Fatal error: Call to undefined method IAM_XLS::dump() in /export/home3/www/mentor/admin/example.php on line 8
Yes I do have the iam_xls.php file in the same directory and have modified it to use my db settings, userid, password etc…
Here is what my example.php file looks like:
dump($query, ‘ceementor’, ‘mentoruser’, ‘********’, ‘mydbs1.ce.gatech.edu’);
?>
If anyone could help it would be greatly appreciated!
Thanks,
Iván Melgrati said:
Posted usingSeptember 27th, 2008 at 11:36 am (10267) [Quote]
Hi! Sean asked this by email as well and the problem is that the correct “dump” method name is
$mid_excel->WriteSQLDump($query, ‘ceementor’, ‘mentoruser’, ‘*******’, ‘mydbs1.ce.gatech.edu’);
That will do the trick. Sorry about the confusion.
Regards,
Posted usingIván