How to export MySQL to Excel (XLS) or CSV using PHP

The below code will export every column name and value from your database into an excel document (.xls).

<?php
/***** EDIT BELOW LINES *****/
$DB_Server = "localhost"; // MySQL Server$DB_Username = "username"; // MySQL Username
$DB_Password = "password"; // MySQL Password$DB_DBName = "databasename"; // MySQL Database Name
$DB_TBLName = "tablename"; // MySQL Table Name$xls_filename = 'export_'.date('Y-m-d').'.xls'; // Define Excel (.xls) file name

/***** DO NOT EDIT BELOW LINES *****/
// Create MySQL connection
$sql = "Select * from$DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username,$DB_Password) or die("Failed to connect to MySQL:<br />" . mysql_error() . "<br />" . mysql_errno());
// Select database
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Failed to select database:<br />" . mysql_error(). "<br />" . mysql_errno()); // Execute query$result = @mysql_query($sql,$Connect) or die("Failed to execute query:<br />" . mysql_error(). "<br />" . mysql_errno());

// Header info settings
header("Content-Disposition: attachment; filename=$xls_filename"); header("Pragma: no-cache"); header("Expires: 0"); /***** Start of Formatting for Excel *****/ // Define separator (defines columns in excel & tabs in word)$sep = "\t"; // tabbed character

// Start of printing column names as names of MySQL fields
for ($i = 0;$i<mysql_num_fields($result);$i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
// End of printing column names

// Start while loop to get data
while($row = mysql_fetch_row($result))
{
$schema_insert = ""; for($j=0; $j<mysql_num_fields($result); $j++) { if(!isset($row[$j])) {$schema_insert .= "NULL".$sep; } elseif ($row[$j] != "") {$schema_insert .= "$row[$j]".$sep; } else {$schema_insert .= "".$sep; } }$schema_insert = str_replace($sep."$", "", $schema_insert);$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);$schema_insert .= "\t";
print(trim($schema_insert)); print "\n"; } ?> • View above code on Github Gist → Note: Delete the line spaces if you receive a parse error. 85 thoughts on “How to export MySQL to Excel (XLS) or CSV using PHP” 1. Hi, I have a error: Parse error: syntax error, unexpected ‘;’, expecting ‘)’ which piece of code should I change? • Didn’t you get line number with the error? I suggest you make sure you’ve copied the above code properly. I just checked and can’t replicate the error you’ve mentioned. ~ Dipak G. • I am using the code everything works good but when i change the query to complex one creating joins on multiple tables in the same database then it doesnot works. I will be really thankful if you suggest me solution to that. 2. Yes, it works. By the way, how to select only several column and rename the column header • To select only specific columns, you have to change the query at line 12 in the above code. Let’s say you want to display only 2 columns first_name and last_name by renaming it’s title ‘First Name’ and ‘Last Name’ in the results, you can use something like…$sql = “Select first_name as ‘First Name’, last_name as ‘Last Name’ from $DB_TBLName”; I hope this helps. ~ Dipak G. 3. Hi Dipak, These codes are perfectly worked.by the way exported xls file dont show D-M-Y. how can i do that?? Thank you. 4. Thank you for the codes sir!!! 🙂 It helped a lot! 5. Hi. I used this code and it works. But when i open file it says- ” The file you are trying to open is in different format than specified by the file extension.” Second – Date and time shows like this – ####. But when I make the column broad. It appear perfectly. Third when I see its print preview it doesn’t show the borders.While if we create a excel file from scratch it shows border in print preview. That mean if client want to print. He can’t get the border which is important for the column distinguish. Any help be appreciated. Thanks • Hi Jimmy, The best place to get answers of your questions is Google. Thanks, ~ Dipak G. • hey did u get ur ans??i had same problem please tell me • You have to put the borders manually in excel because even the mysql table had no borders during the creation of the table. 6. Thank you !! 7. Hello, thanks for the post it is indeed very helpful. My main problem is that the produced .xls file instead of showing the contents of the selected table it only shows the contents of my main page (text, metadata etc). I’m using this in my wordpress site. Do you have any idea if i have to make any changes? Thank you for your kind help. • Can you post a query-statement of line 12 ? • this is my query$result = $wpdb->get_results(“SELECT * FROM jma_users”); if i just echo the$result array i get perfectly correct answers. It’s just that the produced .xls file containts my First Page and not those results!! any ideas? Thank you in advance.

• So you mean you have updated line 12 in the above script to something like…

$sql = “SELECT * FROM jma_users”; • Exactly! 8. If i got your question right, my query results are correct. if i just echo them i get correct results 9. Thanks, help me a lot! 10. hi! your code is very helpful 😀 i have a problem 🙁 i have a lot of table to export in excel 🙁 i dunno the code to add to read the other table 🙁 can you help me? • To do any MySQL related modifications, you have to update a query at line 12 in the above code. P.S: These tutorials are meant to be helpful, but please note that I can not and will not be able to help with any implementations or modifications in FREE. You can hire me to do it for you. • i have to connect 9 php to one php file for exporting to excel but it only read one php file in exporting 🙁 so you can’t help me?:( 11. thanks anyway 😀 12. its doesn’t create a download link but displays everything in the browser… what changes i have to make. • Try to check in different browsers. 13. thank you it works great .. but i have an error with encoding the Arabic results ! the xls file replaces the Arabic letters with ‘????’ marks .. can you help me with this ? 14. hello dipak , i want to export data from webpage to excel pls help me 15. i want to select all fields but with custom fields name. i have all fields name in array, how i can print them to excel file. 16. thank you, it works.. 🙂 17. It looks like your code is working…. but where i could find the xls file and how i could insert it in a html button download? 18. Thank you it saved a lot of my work going back and forth to php my admin to accomplish certain tasks 19. Possible to capitalize all table cells before output? Willing to pay for assistance. (For example: First, Last, Location becomes FIRST, LAST, LOCATION) • Nevermind, strtoupper worked perfectly for my needs. • Great to hear that you’ve fixed it already! 20. Hello, I adapted your code to mysqli But I can’t find a solution, all my data appears together in line. For example; I exported some data and it appears like this: (field) id,name,email,city (field2) 12,john,mail@ex,california but no columns… is there a way to put all data in each cell? • I fixed it! If you’re interested I can share you my mysqli adapt • could you please share your code, i had the same problem with the rows! • hi, could you please share your code, i have the same problem with the rows in excel! 21. Can I use your code for my convenience???, I need something like your code. Thanks. • Yes, you’re free to use this code as per your needs. 22. it is working properly but i want to export selected records into excel. how it’s possible? • me read ur reply that me ask u on April 6, 2015 but my requirement is like that not a specific Coloumn i want to export specific records with this example it’s export every records of that table but i want to export selected recoerd.. 23. i want to export selected data from that table how it’s possible and also i want to give own fields name in excel…. 24. Hi, it works perfect, such a good job ! I was wondering if i can add a border on excel using this code ? 25. thanks u =D 26. Hey, it works almost perfect. The problem is that data exported to excel is missing the leading zero. So instead of number 012345 i get 12345. 27. Hi,How can I give color to each row in the excel through this code.? 28. this is not properly working when me export into excel it’s give me all fields in one column • You’re the only person who having issues with the above code all the times. If you do not understand it, hire me to customise it for you. 29. just perfect mate, well done 30. thanks, how to add report name on excel heet 31. thanks, how to make that read Russian letters? 32. HI Thanks for the code it’s really nice,Can you please advice how to mark cell at color like if export data value less then 2 then it will mark red if data>2 it will mark green if data==2 then there are no color code. Please advice thanks. 33. Hello, How i can export to XLSX format? 34. I have to join two tables together, how can i view the details of the two tables together in excel, do reply quick • Change MySQL query at line 12 accordingly. 35. Hey, this is great! I was wondering if you can add data to separate sheets this way? 36. it works well but i use multiple Mobile numbers in a cell it separates by comma 98,416,379,529,801,000,000 37. How can I download the created file directly from the browser? 38. Changing different browsers doesn’t help me as mentioned in one of the comments above. Please suggest some way to implement the download. 39. Same issue as dndautoes. All the data appear in the browser, but no download. I tried several browsers. I didn’t change anything in the code, except the necessary inlog en query data. • Could you try to replace headers? Replace this (lines 20-23)… header("Content-Type: application/xls"); header("Content-Disposition: attachment; filename=$xls_filename");
header("Expires: 0");

with this…

header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=$xls_filename"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); Let me know how this goes? 40. great tutorial thanx 41. thank you so much 42. hi Dipak i have problem Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 159 Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 160 Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 161 Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 162 Warning: Cannot modify header information – headers already sent by (output started at R:\test2go\htdocs\text.php:55) in R:\test2go\htdocs\text.php on line 163 id pname pdisc delar dp mrp pamt pqty alert scode date mfdate epdate item remark 29 test1 reguler xyz 9 11 10 70 30 2015-10-28 00:10:20 1970-01-01 1970-01-01 0 26 test2 reguler xyz 10 12 11 100 10 2015-10-27 21:15:10 1970-01-01 1970-01-01 0 23 test8 reguler xyz 10 12 11 50 20 2015-10-27 21:15:17 1970-01-01 1970-01-01 0 can you help me thanx rajkumar 43. hi guys, it worked in browser but not in server. What should I do If I want to save it in server automatically? 44. woww!! woww!! you are great man. saved me a lot of time. Not even Stackoverflow could do this. It worked without any disturbance. actually, since i had many tables in the database, i made a variable for table name and got it using$_Get from the multiselect on the previous page.

45. Hey can u tell me how to export complete data from particular database using php.

46. Wow.This code works amazingly.Thank you so much.

47. Sir,
how can i export data to excel from two tables

48. Hai,

when i use this code all the values are printing on web page.

How to export those values to excel. i am a beginner to php can u please explain me how to export those values into excel.

49. Cool , thanks bro.. i need this , save my time for my new project, but i have some problem with number, my data fields is 00021100332 , result in export excel is 21100332 .. please help whit this

50. Hello.
I have a problem … i use this script, but he risult is displaying in TXT in the browser window …. not in a xls filename. Have you a solution ? I have PHP 5.5.
Thank you.

51. Hi buddy, I have just found your post (almost 2 years after you created it!) It’s a very nice piece of code and it works for me… but when openning the exported file, Excel shows this error: “The file you are trying to open, ‘name.ext’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”. If clicking ‘Yes’, the import text wizard is ‘triggered’ and after the import process, the exported data table is shown.

Is this the intended behaviour when using your code? Or it should be loaded as an Excel file directly (not as a imported text file)?

• That warning is just a security feature of Excel. (you can ignore it.)

In such questions, Google is your best friend.

• Yeah buddy, I know that it’s an Excel warning and I already googled it before asking here. The point is that your code generates a csv file, not a proper Excel file (and that’s why Excel triggers the warning), so I wonder if this is the code’s intended behaviour, or it should be generated as a proper Excel file without warnings

52. Hi Dipak,
your php script is exactly what i need. So many thanks for that.
It works like a charme, but when it comes to a column with multiple values looking like that [“8″,”9”]
it gives me [“,8” in the right column and everything what comes after in the next. So not only “9”] but every value that follows without creating the new colums. Is it a problem with seperator?

• Sorry, just needed to add [ ] as seperator.
Again so many thanks
Jens

53. Jens, I am glad that it worked for you.

54. thanks for the info , it really helped me.
* link removed *