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

How to export MySQL to Excel (.xls) or CSV using PHP

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-Type: application/xls");
  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.

    • 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.

  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

  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.

      • 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.

  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?:(

  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 ?

  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.

  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)

  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?

  22. it is working properly but i want to export selected records into excel. how it’s possible?

  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 ?

  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.

    • 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.

  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.

  34. I have to join two tables together, how can i view the details of the two tables together in excel, do reply quick

  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("Pragma: no-cache");
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?

  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.

  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)?

      • 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?

Comments are closed.

Let's discuss your project

Get in touch Schedule a call

Popular Services & Plugins

WordPress Speed Optimization Service
WordPress Support and Maintenance

WP Performance Score Booster
WP Super Minify

Partners

SiteGround
Cloudways
ShortPixel
WP Rocket
Perfmatters

© 2024 Dipak C. Gajjar ⚡

Privacy Policy Terms of Service