How do I export a table that contains Hindi text into Excel using php?

Question

I am trying to export Hindi data into excel using php. After having exported the Hindi data, it is shown in different format "????????? ????". My previous code is

<?php
include 'config.php';
setlocale(LC_ALL, "en_US.UTF-8");
$DB_TBLName = "feedback"; //MySQL Table Name   
$filename = "feedback";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 

//execute query 

$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 
$file_ending = "xls";

//setlocale(LC_ALL, "en_US.UTF-8");

//$result=mb_convert_encoding($result, 'ISO-8859-13','UTF-8');

/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";

}

print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
                if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    } 


    //header info for browser

    header("Content-Type: application/vnd-ms-excel");
    header("Content-Disposition: attachment; filename=$filename.xls");  
    header("Pragma: no-cache"); 
    header("Expires: 0");

?>

After doing some research somebody suggested to add a charset before fetching data from table. And I added the line mysql_set_charser('utf-8'). Now it is exporting in different format à¤£à¤¾à¤šà¤¬à¥‚à¤›à¤ à¤›à¤¤à¤®, but I am not getting it in Hindi.

Thanks.

http://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically — anonymous, May 15 '15 at 14:41
Thank you Jurgis Gregov for giving the reference( http://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically). — Mohan Vijay, May 26 '15 at 09:51
You are *not* producing a `application/vnd-ms-excel` file here, just a CSV/TSV. Excel notoriously sucks with those. The best bet is to produce an actual XLS file with https://phpexcel.codeplex.com. — deceze, May 26 '15 at 09:54

Mohan Vijay · Answer 1 · 2015-05-26T09:49:08.083

Exporting UTF-8 encoded data from MySQL to Excel using Php
This Manual demonstrates how to export UTF-8 encoded data from MySQL to Excel. In my case I was tried to export Hindi data into excel it works fine. We can export in two ways
1) Exporting Manually and
2) Exporting programmatically using php.

1) Exporting Manually:
Following steps are exporting manually from Database.
1) Save the exported file as a csv or xls while exporting from database
2) Open Excel
3) Import the data using Data
-->Import External Data
--> Import Data
4) Select the file type of "csv" or "xls" and browse to your file
5) In the import wizard change the File_Origin to "65001 UTF" (or choose correct language character identifier)
6) Change the Delimiter to comma
7) Select where to import to and Finish
This way the special characters should show correctly.

2) Exporting programmatically using php:
In php we need to follow two steps.
Step 1: Add mysql_set_charset(“utf8”) when before fetching the data from DB.
Ex:

mysql_set_charset("utf8");
$result = @mysql_query("Select * from $DB_TBLName",$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

Step 2: And now Add to BOM(Byte Order Mark) code to headers.
Ex:

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
echo "\xEF\xBB\xBF";

Final Code in php:

<?php
include 'config.php';
$filename = "student_details";         //File Name
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
mysql_set_charset("utf8");
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
echo "\xEF\xBB\xBF";
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . ",";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".",";
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".",";
            else
                $schema_insert .= "".",";
        }
        $schema_insert = str_replace(","."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    } 
?>

How do I export a table that contains Hindi text into Excel using php?

1 Answers1