PHP encoding Umlauts and Spaces in .csv for Excel

Question

I have some json data that I need to email to my users as a .csv file.

The following code works as expected for Hällowörld, however, once I put a space in there, Hällo wörld, the .csv file reads Hällo wörld when opened in Excel (for Mac).

$temp = fopen('php://temp/maxmemory:10485760', 'w');
$rows = json_decode('[["Hällowörld"]]'); // -> Hällowörld
//$rows = json_decode('[["Hällo wörld"]]'); // -> ÔªøH√§llo w√∂rld
foreach($rows as $row) {
  $row = array_map(function($cell) {
    return chr(239).chr(187).chr(191).$cell;
  }, $row);
  fputcsv($temp, $row, ';');
}
rewind($temp);
$csv = stream_get_contents($temp);
fclose($temp);
$csv = base64_encode($csv);
// -> post $csv to my email provider's API

A few notes:

• My code is in UTF-8
• If I open the file with Apple's numbers or textedit, the content is displayed as expected.
• If I don't do the mapping with chr(239).chr(187).chr(191).$cell, I get Hällowörld.
• If instead, I use mb_convert_encoding($cell, 'UTF-16LE', 'UTF-8') or mb_convert_encoding($cell, 'Windows-1252', 'UTF-8'), as is often suggested, I get H‰llowˆrld.
• The final base64_encode() is necessary, because my email provider needs the attachment to be base_64-encoded.

Answer 1

I found the solution! :) Replace the above foreach loop with the following:

array_unshift($rows, [chr(239).chr(187).chr(191)]);
foreach($rows as $row) {
  fputcsv($f, $row, ',');
}