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 …………
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′);
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.
Klemen said,
September 5, 2008 @ 2:45 pm
I have found solution.
Charset must be set to windows-1250 & mysql: set names cp1250
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
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.
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.
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′);