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.
[tags]mysql, excel, mysql export, sql, databases[/tags]
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,
Iván
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. :)
awesome! thanks for the tip
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!
Perfect. Note the default location the file is saved in windows is, eg:
C:\Program Files\MySQL\MySQL Server 5.0\data\myDatabaseName
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,
[quote comment=”6382″]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,
Iván[/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,
Iván
In linux the place where this file is /var/lib/mysql/database_name/file.csv
Hi all, my script is running, thanks for the ideas. But I need to rewrite the file with the same name each time. How can I do that? Thanks!
@ Max:
Hello,
I would like to know how to change the location (path) where the file is saved. Is this possible? If so what do i need to do.
many thanks in advance
Hilton
@ Hilton Becker:
write the full path in the file field
hi, i would like to know how to include the column names into the csv file
thanks guys,…
this is working but I also want the coloum header with the data can any one tell me the code that how can i export data with coloum name from mysql to .csv format