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

yes its set to utf8 --- name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, — Sajal
– Sajal, Commented Jul 13, 2013 at 9:26
What do you see when you open your csv in a text editor? Compare that to the csv that is exported by phpmyadmin. — Marcin Krawiec
– Marcin Krawiec, Commented Jul 13, 2013 at 9:38
@ Marcin Krawiec- both files are xls not csv. when i open both files in wordpad, i see à¤¸à¥‚à¤°à¥à¤¯à¤¾ kind of characters. — Sajal
– Sajal, Commented Jul 13, 2013 at 10:11
Nope, both are text files but with .xls extension. You can select encoding when you open a file in wordpad - I don't remember exactly how but I'm sure that wordpad allows to force UTF-8 encoding at opening. — Marcin Krawiec
– Marcin Krawiec, Commented Jul 13, 2013 at 10:24
You're not writing an Excel BIFF file, even if your file extension is .xls; you're writing a csv file with a TAB separator (note that some versions of Excel will display a warning message when opening this file) - If you want to write a real Excel BIFF (.xls) or OfficeOpenXML (.xlsx) file, you'll need to use a library like PHPExcel — Mark Baker
– Mark Baker, Commented Jul 13, 2013 at 10:37

Mark Baker · Accepted Answer · 2013-07-13 10:39:53Z

2

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

Use PHP's built-in fputcsv() function
Write a UTF-8 BOM header to the csv file before you write any other data
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);

edited Jul 13, 2013 at 10:39

answered Jul 13, 2013 at 9:58

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sajal Over a year ago

@ Mark - The mysql outfile din't worked. It shows à¤§à¤¨à¤¶à¥à¤°à¥€ and not UTF-8 characters. I am trying fputcsv(). Can you please help with UTF-8 BOM header. I searched it earlier also but din't got any clue how to use it. Thanks.

Collectives™ on Stack Overflow

PHP Exporting UTF-8 Data into Excel from Mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related