PHP Exporting UTF-8 Data into Excel from Mysql

Question

I am using core php code to export data to excel. The following is my code:

header('Content-Type: text/html; charset=utf-8'); mysql_connect("localhost","root",""); mysql_select_db("test"); //---------------------------------------- mysql_query("SET NAMES 'utf8'"); mysql_query("SET CHARACTER SET utf8"); mysql_query("SET COLLATION_CONNECTION = 'utf8_unicode_ci'"); //---------------------------------------- $filename = "excelwork.xls"; $exists = file_exists('excelwork.xls'); if($exists) { unlink($filename); } $filename = "excelwork.xls"; $fp = fopen($filename, "wb"); $sql = "select * from test"; $result = mysql_query($sql); $$insert = ""; $insert_rows = ""; for ($i = 1; $i < mysql_num_fields($result); $i++) { $insert_rows .= mysql_field_name($result,$i) . "\t"; } $insert_rows.="\n"; fwrite($fp, $insert_rows); while($row = mysql_fetch_row($result)) { $insert = $row[1]. "\t" .$row[2]. "\t".$row[3]. "\t".$row[4]. "\t".$row[5]; $insert .= "\n"; // serialize($assoc) fwrite($fp, $insert); } if (!is_resource($fp)) { echo "cannot open excel file"; } echo "success full export"; fclose($fp); 

============ This is my Mysql Table ================

CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `status` int(11) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `test` -- INSERT INTO `test` (`id`, `name`, `status`) VALUES (1, 'सजल', 1), (2, 'सूर्या', 1), (3, 'धनश्री', 1), (4, 'मनीष', 1), (5, 'राहुल', 1); 

The Output excel file gives this output :

सजल सूरà¥à¤¯à¤¾ धनशà¥à¤°à¥€ मनीष राहà¥à¤² 

But when i Export using Phpmyadmin into xls the output is correct:

सजल सूर्या धनश्री मनीष राहुल 

Please tell me what is wrong on my code. I have tried many things, searched many posts.
Thanks

Answer 1

This question seems to be asked several times a day at the moment

1. Use PHP's built-in fputcsv() function
2. Write a UTF-8 BOM header to the csv file before you write any other data

3. Consider using MySQL's own export

   SELECT *
   INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test;

EDIT

$fh = fopen('test2.csv', 'w+'); fwrite($fh, "\xEF\xBB\xBF"); // Write UTF-8 BOM fwrite($fh, "sep=\t" . PHP_EOL); // Hint for MS Excel while($row = mysql_fetch_row($result)) { fputcsv($fh, $row, "\t"); } fclose($fh);