how to export database in any excel spreedsheet or alternative

Question

i want to export data from mysql table to excel or any such kind of alternative using php

does any one have any having any code or suggestion

Answer 1

You can get MySQL to export to a CSV file which any version of Excel will open.

SELECT *
INTO OUTFILE '/path/to/folder/result.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM example_table

You can still easily customize it but selecting columns etc

http://dev.mysql.com/doc/refman/5.1/en/select-into.html

Of course if you have phpMyAdmin on your server, there is an export option will will do this for you

Answer 2

I usually write to a file using something like fputcsv which excel will then open easily. @jleagle 's method also is a good one when you want a straight dump of the table.

If I'm just wanting a manual export I also use a program called Navicat which I use for managing databases when not working with the command line.

Answer 3

If you're looking to do it yourself every now and then you could just use PHP myadmin and use it's export functions rather than coding your own.

Answer 4

Transforming your table to CSV file is probably your safest bet as Excel can natively handle CSV as it were an Excel file. You can always do a "Save As" once in excel and save it to an .xlsx file. Here is a similar posting that has PHP snippets on transforming a table to CSV.