0

I'm working on a PHP script that creates a .csv file from some data.

Unfortunately, in Excel Umlauts are not displayed properly: Löl becomes L√∂l (this is just in Excel, in Apple's Numbers, Atom and Textedit everything looks fine).

I have tried hundreds of functions to try and get the encoding right (see the commented functions below). Could someone please tell me what I'm doing wrong?

$rows = json_decode('[["Löl"]]');

foreach($rows as $key_row => $row) {
  foreach($row as $key_cell => $cell) {
    // $rows[$key_row][$key_cell] = utf8_decode($cell);
    // $rows[$key_row][$key_cell] = iconv('UTF-8', 'Windows-1252', $cell);
    // $rows[$key_row][$key_cell] = mb_convert_encoding($cell, 'UTF-16LE', 'UTF-8');
    // iconv('UTF-8', 'Windows-1252', $rows[$key_row][$key_cell]);
    // mb_convert_encoding($rows[$key_row][$key_cell], 'UTF-16LE', 'UTF-8');
  }
}

$temp = fopen('php://memory', 'w');
foreach($rows as $row) {
  fputcsv($temp, $row, ';'); 
}
fseek($temp, 0);
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename="test.csv";');
fpassthru($temp);
Tonald Drump
  • 1,227
  • 1
  • 19
  • 32
  • 1
    Rather than throwing functions until they stick, you need to understand what encoding you _have_, and what encoding _Excel expects_. We could help with the second part, but for the first part you need to do some more investigation so that you can [edit] the question to include a [mcve]. – IMSoP Aug 17 '20 at 11:09
  • Done :) This should be copy-pasteable to generate a .csv that shows incorrect Umlauts in Excel – Tonald Drump Aug 17 '20 at 11:26
  • `$rows[$key_row][$key_cell] = iconv('UTF-8', 'Windows-1252', $cell);` works for me - Excel is expecting Windows-1252, and the sample you've pasted here is in UTF-8. If that's not working in your real application, then your input probably isn't in UTF-8, and you need to work out what encoding it is in. (Encoding bugs are a pain, because as soon as you copy and paste text somewhere, things start interpreting it!) – IMSoP Aug 17 '20 at 11:34
  • With that function the Excel output changes to `Lˆl`. Regarding input: I'm using Atom 1.50.0 and under Preferences > Core > File Encoding is set to `Unicode (UTF-8)` – Tonald Drump Aug 17 '20 at 12:32
  • 1
    Does this answer your question? [Is it possible to force Excel recognize UTF-8 CSV files automatically?](https://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically) – JosefZ Aug 17 '20 at 18:12
  • Thank you, that does work: If I don't change the encoding as per my code above and select `Unicode (UTF-8)` during importing. However, I am sending this .csv file to customers, so it's a bit much to ask for them import the data like that.. They should be able to click-open the file. But maybe that can tell us something about the encoding? EDIT: just saw there are several upvoted answers, will check them out tomorrow :) – Tonald Drump Aug 17 '20 at 19:01
  • 1
    Maybe save the csv file with [UTF-8 BOM](https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8)? Excel recognizes the BOM and will import such `csv` file properly… – JosefZ Aug 17 '20 at 20:26
  • Hey @JosefZ, that works! I am using this weird function to convert to BOM: `$rows[$key_row][$key_cell] = chr(239) . chr(187) . chr(191) . $cell;`! Thanks so much! – Tonald Drump Aug 18 '20 at 08:49

1 Answers1

0

The following conversion to UTF-8 BOM worked:

foreach($rows as $key_row => $row) {
  foreach($row as $key_cell => $cell) {
    $rows[$key_row][$key_cell] = chr(239) . chr(187) . chr(191) . $cell;
  }
}

Thanks for everybody who chimed in :)

Tonald Drump
  • 1,227
  • 1
  • 19
  • 32