Excel in Mac showing '_' instead of accents when generating report through console

Question

I have a huge query with a lot of relationships, joins and DB functions involved. Here an extract example of the query:

SELECT * FROM (
    SELECT DISTINCT 
        CASE WHEN objeto.objeto_clave IS NULL THEN objeto.objeto_numero ELSE objeto.objeto_clave END AS "Número", 
        CASE WHEN objeto.objeto_estatus = 'V' THEN 'Válido' ELSE 'No válido' END AS "Válido"
    FROM objeto

    WHERE objeto.objeto_id IN (1, 2, 3) 

    GROUP BY objeto.objeto_id, objeto.objeto_numero, objeto.objeto_clave, objeto.objeto_estatus 
) AS "Excel" ORDER BY (CASE WHEN "Número" IS NULL THEN substring("Número",3) ELSE substring("Número",6) END) ASC

Im using cakephp 2.x to generate an excel report through a method which allow you to download an excel file taking data from the database connection (db_name, user, etc...), the sql query and doing a system command to create the file filled with the data given by the query result using a -H option for HTML tabular output and -c to excecute the sql as a command:

$command = <<<CMMD
psql -h $host -p $port -d $db -U $login -H -c "$sql" > $file
CMMD;

system($command);

//Cleaning content
if (is_file($file)) {
   $content = file_get_contents($file);
   $content = substr($content, 0, strpos($content, '<p>'));
   $content = utf8_decode($content);
   file_put_contents($file, $content);
}

Everything works fine in Windows platform. The problem comes when you download the file using a Mac, the file is created but when you open it, column names and data in cells with accents like: "Número" and "Válido" are converted into "N_mero" and "V_lido".

I would like to know where the problem is related (Code, [Mac Excel] special encoding, something with utf8_decode function, another thing ?) and how can i handle it.

It doesn't show any problem if I use openOffice in the same Mac.

score 0 · Answer 1 · edited May 23 '17 at 12:06

After a day of research and viewing some other questions in this site

I found its related with Mac's excel version encoding support:

Mac does not currently support UTF-8

then i saw some work-arounds to deal with this issue and most of them pointed to use a third program to convert the file encoding to UTF16LE to do things like import/export data into excel correctly.

I solve my problem replacing the utf-8 function and replacing it like this:

//$content = utf8_decode($content);
//Converting to UTF16LE with BOM [chr(255) . chr(254)] for MAC version
$content = chr(255) . chr(254) . mb_convert_encoding($content, 'UTF-16LE', 'UTF-8');
file_put_contents($file, $content);

the data now is propertly interpreted by Mac and rendering as desired.

Explanation can be found here and here

Excel in Mac showing '_' instead of accents when generating report through console

1 Answers1