Character encoding on Excel export from PHP

Question

UPDATE: I have changed the code in order to insert a BOM, with no possitive result. Here's the updated piece of code:

$output = fopen($file, 'w');

        $utf8_output_with_bom = chr(239) . chr(187) . chr(191) . $output;

        fputcsv($output, array('NOMBRE','APELLIDO','TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t");


        if ($key1!="No definida"){            

            // fetch the data
            $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'");
            // loop over the rows, outputting them
            while ($row1 = mysqli_fetch_assoc($rows1)) {     
                fputcsv($output, $row1, "\t");        
            }
        }

I have a utf8_general_ci database, with this example of records on the two involved tables (all utf8_general_ci):

Table ws_campmsg:

ws_campmsg

Table ws_campreplies:

ws_campreplies

The only thing I need is to export this on Excel, and this is almost done. This PHP file creates a SQL and exports a correct XLS file:

$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);    
mysqli_set_charset($con, 'utf8');

[....]
        $ca_id = $_POST['ca_id'];            
        $key1 = $_POST['key1'];

        $file='../uploads/TemporalCsv_'.time().'.xls';

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


        $output = fopen($file, 'w');        


        fputcsv($output, array('NOMBRE','APELLIDO','TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t");


        if ($key1!="No definida"){            

            // fetch the data
            $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'");
            // loop over the rows, outputting them
            while ($row1 = mysqli_fetch_assoc($rows1)) {                
                fputcsv($output, $row1, "\t");        
            }
        }

        fclose($output);
        echo $file;

If I pass ca_id="438", and key1="CLAVE7", the exported file is right this one:

xls

As you see the only problem now is that it does not show properly accents like á or Á. As I have searched quite a bit about solving this, there are some tries I have made with no possitive result, like trying to export on text format dfirectly on browser (exports the same coding mistakes):

$file='../uploads/TemporalCsv_'.time().'.txt';
header("Content-Type: text/tab-separated-values");

Also tried changing headers (exports the same coding mistakes)

header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-type:   application/x-msexcel; charset=utf-8");
header("Content-Disposition: attachment; filename=ventas.xls");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);

On the last tries I found that maybe converting with iconv() would be effective, but with this modification an error emerges, as html_entity_decode() accepts strings, not arrays:

if ($key1!="No definida"){            

            // fetch the data
            $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'");
            // loop over the rows, outputting them
            while ($row1 = mysqli_fetch_assoc($rows1)) {     
                iconv("UTF-8","WINDOWS-1255",html_entity_decode( $row1 ,ENT_COMPAT,'utf-8'));
                fputcsv($output, $row1, "\t");        
            }
        }

I hope I explained myself crearly. The use of PHPExcel is my last chance, if there is any posibility of correcting this issue without using PHPExcel, I would prefer it. Very grateful.

Don't open it directly in Excel ... instead create a new spreadsheet and then do `data > import from file` ... when you do that you can specify the character encoding of the data import to use utf-8 rather than Windows-1252 — CD001, Jun 25 '15 at 10:18
Thank you. But is it not an spreadsheet what I am creating? I am confused about differences on opening in Excel and creating an sporeadsheet, is it not the same? The spreadsheet itself gets created under the $file directory, you mean taking that and doing a data > import from file...? — Biomehanika, Jun 25 '15 at 10:22
Basically once you've exported the .xls file **don't** double-click on it to open it in Excel. Instead open Excel and use the *data > import from file* tool to import the data into your spreadsheet. That import tool allows you to specify the character set that the data is in. If you double-click to open the .xls file, Excel assumes the character encoding will be Windows-1252 and scrambles your data. — CD001, Jun 25 '15 at 10:27
I understand now. The problem is that this is an AJAX aplication, and whats is being echoed (the $file value, that means the final directory and file) gets automatically opened on a new tab, so it is not manually opened by the final user. Optimal solution would be to pass a final directory and file with that file itself previously encoded. Is there any way of making this trough PHP instead of trough Excel? — Biomehanika, Jun 25 '15 at 10:31
It's more an Excel problem than a PHP problem... I run into it every time I do a data dump from PHPMyAdmin. Your best bet is probably to throw it at Google Spreadsheet instead according to this answer : http://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding — CD001, Jun 25 '15 at 10:33
Yes. As far as I've studied this, the issue is an Excell thing. Google's solution could be a very useful alternative. Very grateful — Biomehanika, Jun 25 '15 at 10:37

Benno Eggnauer · Accepted Answer · 2015-06-26T06:57:16.133

3

You have to add a BOM at the beginning of your output.

$utf8_output_with_bom = chr(239) . chr(187) . chr(191) . $utf8_output;

The BOM is not required in UTF8.

Example:

$output = fopen($file, 'w');
fwrite($output, chr(239) . chr(187) . chr(191)); // Write the BOM to the beginning of the file
fputcsv($output, <...>);
fclose($output);

Code to write the CSV:

$output = fopen($file, 'w');

fwrite($output, chr(239) . chr(187) . chr(191));

fputcsv($output, array('NOMBRE', 'APELLIDO', 'TELEFONO', 'RESPUESTA', 'DIA', 'HORA'), "\t");

if ($key1 != "No definida") {

    // fetch the data
    $rows1 = mysqli_query($con, "SELECT cm_name, cm_surname, cr_phone, cr_message, DATE_FORMAT(cr_time,'%d/%m/%Y'), DATE_FORMAT(cr_time,'%H:%i') FROM ws_campreplies JOIN ws_campmsg WHERE cr_phone=cm_phone AND cr_fk_us_id='$id' AND cr_message='$key1' AND cm_fk_ca_id='$ca_id'");
    // loop over the rows, outputting them
    while ($row1 = mysqli_fetch_assoc($rows1)) {
        fputcsv($output, $row1, "\t");
    }

}

fclose($output);

edited Jun 26 '15 at 06:57

answered Jun 25 '15 at 12:09

Benno Eggnauer

853
1
7
19

So after creating the file with **$output = fopen($file, 'w');** I must set the next line as **$utf8_output_with_bom = chr(239) . chr(187) . chr(191) . $output;** ? – Biomehanika Jun 25 '15 at 13:16
I have updated the top of the cuestion with the edition I made on the code to add the BOM. Please check. I guess I do not know how to structurate this addition on the EXCEL file... – Biomehanika Jun 25 '15 at 13:22
Could not continue with this untill today. Your help has make this work, thank you, – Biomehanika Jul 06 '15 at 04:55
Worked for me ! Thanks :) – Fakebounce Dec 08 '16 at 08:54

Character encoding on Excel export from PHP

1 Answers1