Download data as csv using PHP

July 1, 2007 at 4:05 am · Filed under MySQL, PHP

This is a very simple example to download data in csv format using PHP and MySQL. I have used a table with two columns i.e. id and name.

The main point to download file is a proper use of header(). It has said many times in many places but I repeat here, "Make sure there must not be any sort of output (including whitespaces) before header()". We can also set a default file name for the file.

Here is the PHP code

<?php
// Connect to database
mysql_connect("localhost", "root", "root");
mysql_select_db("test");
 
// mention the file name to display in download prompt
$file_name = "download.csv";
 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
 
// Output header
echo "Id,Name"."\n";
 
$result = mysql_query("SELECT * FROM test");
while($rs = mysql_fetch_array($result))
{
	echo $rs["id"].",";
	echo $rs["name"]."\n";
}
?>

Consider the followings when downloading a large csv file

  1. PHP script may exceed maximum execution time. To prevent this, use set_time_limit() at top of the script.
  2. Sometimes it is beneficial to use mysql_unbuffered_query() instead of mysql_query() for huge data.

If you find any problem with this code, please inform.

Note: Do not use "ID" in the start of CSV file. Doing so will result in an error "SYLK: File format is not valid" when opening this file in Microsoft Excel.

Bookmark and Share

Related Posts

13 Comments »

  1. faruk chowdhury said,

    December 7, 2007 @ 2:20 am

    Dear Sir,
    i modified your code according to my requirement but i am facing a problem with characters set i.e there are some data at database like as plate_no : 1 2 3 ص ص ص but when i exported it in csv format i see it as 1 2 2 3 س س س. so can you tell me where is my problem or how can i solve it.
    [php code]
    < ?php
    header('Content-Type: text/html; charset=utf-8');
    // Connect to database
    mysql_connect("localhost", "root", "root123");
    mysql_select_db("medgulf");

    // mention the file name to display in download prompt
    $file_name = "download.csv";

    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$file_name\"");
    header("Cache-Control: cache, must-revalidate");
    header("Pragma: public");

    // Output header
    //echo "Name,Code"."\n";

    $result = mysql_query("SELECT * FROM vehicle_info");
    while($rs = mysql_fetch_array($result))
    {
    echo $rs["user_name"].",";
    echo $rs["entry_date"].",";
    echo $rs["id_no"].",";
    echo $rs["license_type"].",";
    echo $rs["vehicle_type"].",";
    echo $rs["plate_no"].",";
    echo $rs["chassis_no"].",";
    echo $rs["body_type"].",";
    echo $rs["model_year"].",";
    echo $rs["status"]."\n";
    }
    ?>

  2. Wasif said,

    December 11, 2007 @ 11:53 pm

    In notepad you can view utf8 data properly, but this is Excel which causes the problem. If you must need to view data in Excel, use an editor like SciTE or Ultraedit to save the file in unicode format and then open it in Excel.

  3. Ahmed Hosny said,

    December 14, 2007 @ 7:22 pm

    Dear ,

    try to put the output into var $utf8_str, and then

    use this code

    it will work
    Best regards
    Ahmed

  4. Ahmed Hosny said,

    December 14, 2007 @ 7:23 pm

    sorry this one

    $unicode_str_for_Excel = chr(255).chr(254).mb_convert_encoding( $utf8_str, ‘UTF-16LE’, ‘UTF-8′);

  5. Deepak Raikwar said,

    June 12, 2008 @ 5:45 pm

    Hi Everyone,
    Thanks coz i got ur code bye …………

  6. Klemen said,

    September 4, 2008 @ 7:06 pm

    I have the same problem. Excel doesn’t read UTF-8 files. Files must be in ANSI format to work correctly.

    I don’t know which target encoding should I use to output data?

    mb_convert_encoding( $utf8_str, ??????, ‘UTF-8′);

  7. Klemen said,

    September 4, 2008 @ 8:13 pm

    If I use:
    $unicode_str_for_Excel = chr(255).chr(254).mb_convert_encoding( $utf8_str, ‘UTF-16LE’, ‘UTF-8′);

    I get UCS-2 little endian encoding of a file, not ANSI. Result is better as I see special characters correctly, but now excel doesn’t put values in rows and columns – everything is in one cell.

  8. Klemen said,

    September 5, 2008 @ 2:45 pm

    I have found solution.

    Charset must be set to windows-1250 & mysql: set names cp1250

  9. Ramón said,

    November 3, 2008 @ 10:07 pm

    I have the same exact problem (as far as i can see): my application uses a file download wrapper wich uses the readfile() function to output a file.. it outputs it as an UTF8 file.. and i need them to be saved as ANSI.. perhaps you could share your solution more deeply explained.. thanx in advance

  10. Santhosh said,

    July 26, 2009 @ 12:42 am

    Yes, the same thing i am trying it out. But itis not working. Characters are unreadable.

    It would be great if some body paste the code related to the utf conversion.

  11. Danish Zahur said,

    October 7, 2009 @ 7:23 pm

    If your contents are in UTF-8 format, then no need to convert encoding. Just start your file/output stream with UTF-8 BOM after headers.
    echo pack(“CCC”,0xef,0xbb,0xbf);

    And header should contain encoding UTF-8
    header( “Content-type: application/vnd.ms-excel; charset=UTF-8″ );

    It will work like charm because Excel will recognize file charset with BOM bytes.

  12. Mind said,

    January 19, 2010 @ 3:32 pm

    I solved with this: hope will help some1.

    mb_convert_encoding( $s, ‘ISO-8859-1′, ‘UTF-8′);

  13. Mwasif said,

    July 17, 2010 @ 12:43 am

    Mwasif…

    [...] all about mwasif [...]…

RSS feed for comments on this post · TrackBack URI

Leave a Comment