how to fix encoding from a iso8859-1 php generated file to excel program

Question

I'm creating an excel file downloadable from a web page with this piece of code:

$sql = "SELECT * FROM my_table";

$ressource_sql = execute_sql($sql);

while ($row = mysql_fetch_assoc($ressource_sql)) {


    $ligne_hve.=$row['id'] . ';';
    $ligne_hve.=$row['name'] . ';';
    $ligne_hve.=$row['products'] . ';';    
    $ligne_hve .= "\n";

}
$file_hve = fopen($file_export_hve, "w+");
fwrite($file_hve, $ligne_hve);
fclose($file_hve);

The Excel file is regenerated each time the user consult the web page with this code, all the website is ISO8859-1,i know excel is using a strange encoding (windows-1252) but for now i didn' t find any tricks to get the french accent (well displayed on the website) into the excel...

Thx for help

why not use a CSV file instead of excel? The CSV file can be opened by excel, and supports UTF-8 (which easily can handle your french accents) — Jan Dragsbaek, Feb 14 '11 at 13:45
@Nayena - looking at this code, krifur __is__ creating a separated value file (using ; rather than , as a separator); __not__ an Excel file — Mark Baker, Feb 14 '11 at 16:11

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

You are not actually generating an Excel file. Your script generates a CSV file using semicolons. For a proper solution you should look into PHPExcel or another library.

But your simple CSV output can be converted into UTF-8 nevertheless. It's advisable to add a BOM so Excel detects it when it's sent with the wrong Content-Type:

file_put_contents("converted.csv", 
    "\xEF\xBB\xBF" . utf8_encode(file_get_contents("iso8859-1.csv"))
);

The utf8_encode() may or may not be required there. You didn't show any concrete example. If the text was already in UTF-8, then the BOM workaround already suffices and a second utf8_encode is not necessary.

score 0 · Answer 2 · answered Feb 14 '11 at 14:13

$sql = "SELECT * FROM my_table";

$ressource_sql = execute_sql($sql);

while ($row = mysql_fetch_assoc($ressource_sql)) {


    $ligne_hve.=$row['id'] . ';';
    $ligne_hve.= iconv(input charset, 'windows-1252//TRANSLIT', $row['name']) . ';';
    $ligne_hve.= iconv(input charset, 'windows-1252//TRANSLIT', $row['products']) . ';';    
    $ligne_hve .= "\n";

}
$file_hve = fopen($file_export_hve, "w+");
fwrite($file_hve, $ligne_hve);
fclose($file_hve);

The input charset is iso-8859-1.

how to fix encoding from a iso8859-1 php generated file to excel program

2 Answers2