Convert MySQL table (utf8 data) to Excel file

Question

I'm trying to export an MySQL table into an xls file. The issue is that the table data is in hebrew and saved as utf8. for some reason when I export the file, the text is not readable by excel.

The table is utf8.

function export_excel_csv($DB_TBLName)
{

global $connectionion;
mysql_query("SET NAMES 'utf8'");   
$sql = "select * from {$DB_TBLName}";

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = DATE('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_TBLName on $now_date";


//execute query
$result = MYSQL_QUERY($sql,$connectionion)
     or DIE("Couldn't execute query:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
IF (ISSET($w) && ($w==1))
{
     $file_type = "msword";
     $file_ending = "doc";
}ELSE {
     $file_type = "application/csv";
     $file_ending = "csv";
}
//header info for browser: determines file type ('.doc' or '.xls')
HEADER("Content-Type: application/$file_type; charset='UTF-8';");
HEADER("Content-Disposition: attachment; filename=database_dump.$file_ending");
HEADER("Pragma: no-cache");
HEADER("Expires: 0");

/*    Start of Formatting for Word or Excel    */

IF (ISSET($w) && ($w==1)) //check for $w again
{
     /*    FORMATTING FOR WORD DOCUMENTS ('.doc')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\n"; //new line character

     WHILE($row = MYSQL_FETCH_ROW($result))
     {
         //set_time_limit(60); // HaRa
         $schema_insert = "";
         FOR($j=0; $j<mysql_num_fields($result);$j++)
         {
         //define field names
         $field_name = MYSQL_FIELD_NAME($result,$j);
         //will show name of fields
         $schema_insert .= "$field_name:\t";
             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 .= "\t";
         PRINT(TRIM($schema_insert));
         //end of each mysql row
         //creates line to separate data from each MySQL table row
         PRINT "\n----------------------------------------------------\n";
     }
}ELSE{
     /*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
     //create title with timestamp:
     IF ($Use_Title == 1)
     {
         ECHO("$title\n");
     }
     //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))
     {
         //set_time_limit(60); // HaRa
         $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);
         //following fix suggested by Josue (thanks, Josue!)
         //this corrects output in excel when table fields contain \n or \r
         //these two characters are now replaced with a space
         $schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
         $schema_insert .= "\t";
         PRINT(TRIM($schema_insert));
         PRINT "\n";
     }

If someone has an idea what I'm doing wrong, I'll be glad to know :) Cheers, Sagi.

Plus the heading is screwballed... application/$file_type when $file_type is application/csv gives application/application/csv — Mark Baker, Dec 21 '11 at 18:10

score 0 · Answer 1 · answered Dec 21 '11 at 18:00

0

You're not actually saving as an Excel (.xls or .xlsx) file, you're saving as a CSV file with a tab rather than a comma separator (which is a file format that Excel is capable of reading)... but just because you're sending a header to the browser to tell it that the content is UTF-8, doesn't mean that Excel will be aware of this when you open the file in MS Excel.

If you want UTF-8 data when you open this file in Excel, then either write a genuine Excel (.xls or .xlsx) file (depending on the version of Excel you're targetting). Or, try writing a BOM marker to your CSV file (no guarantees).

answered Dec 21 '11 at 18:00

Mark Baker

209,507
32
346
385

I'm not sure how am I spouse to write a genuine Excel file. basically I need to find a way to export that data into a readable excel file which is utf8. – eek Dec 21 '11 at 18:06
There's dozens of libraries available for PHP that will write Excel files - I'm the author of one.... but you can find a comprehensive list of options here http://stackoverflow.com/questions/3930975/alternative-for-php-excel or google for PHP and Excel – Mark Baker Dec 21 '11 at 18:08

score 0 · Answer 2 · edited May 23 '17 at 10:34

0

This is not an XLS file, but CSV, use this header:

header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

Also check this topic: Create a CSV File for a user in PHP

If that still causes problems we can look further

edited May 23 '17 at 10:34

Community

1
1

answered Dec 21 '11 at 18:03

Derk Arts

3,432
2
18
36

Convert MySQL table (utf8 data) to Excel file

2 Answers2