Download data as csv using 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
- PHP script may exceed maximum execution time. To prevent this, use set_time_limit() at top of the script.
- 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.
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";
}
?>
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.
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
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′);
Deepak Raikwar said,
June 12, 2008 @ 5:45 pm
Hi Everyone,
Thanks coz i got ur code bye …………