Export: downloaded Excel file does not open and Csv file does not support utf-8 character set

[furkantolu]

Opening excel file and supporting csv file with Turkish characters

1- The error of the downloaded excel file: Excel cannot open the file... because the file format or file extension is not valid. 2- CSV file content does not support UTF-8 characters.
kimai-export (3).xlsx

Steps to reproduce

[kevinpapst]

Can you please post some Turkish content, so I can test with it?!

[furkantolu]

[Book1.xlsx](https://github.com/kevinpapst/kimai2/files/4307062/Book1.xlsx) This: "Merhaba bu bir türkçe içeriktir. ÜĞIİŞÇÖçöğiş" Thanks Kevin Kevin Papst <notifications@github.com>, 9 Mar 2020 Pzt, 17:06 tarihinde şunu yazdı:

…

Can you please post some Turkish content, so I can test with it?! — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1537>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH3UXIOVLQKXIXEWCNRHFTTRGTZUXANCNFSM4LEJPJUA> .

-- Furkan TOLU Gsm: +90(546) 692 0712 www.furkantolu.net

[kevinpapst]

Where exactly did you add these characters?
I added them to a timesheet record as description and exported that record.

• XSLX worked fine for me with Apple Numbers, LibreOffice and Excel
• CSV worked for me in Apple Numbers, LibreOffice, Atom

The only problem I saw was with CSV in Excel, which did not use UTF8.
But I never use Excel, so I don't know how to change the encoding in there.
Can you please try the demo https://www.kimai.org/demo/ and verify if the problem exists there as well.

[furkantolu]

Hi Kevin, I experimented on the demo. The Excel file worked flawlessly and supports Turkish characters. The CSV file worked, but does not support Turkish characters. I installed Kimai2 1.7 stable version via FTP. What could be the source of this problem? Thanks. Kevin Papst <notifications@github.com>, 9 Mar 2020 Pzt, 18:12 tarihinde şunu yazdı:

…

Where exactly did you add these characters? I added timesheet record and exported that. - XSLX worked fine for me with Apple Numbers, LibreOffice and Excel - CSV worked for me in Apple Numbers, LibreOffice, Atom The only problem I saw was with CSV in Excel, which did not use UTF8. But I never use Excel, so I don't know how to change the encoding in there. Can you please try the demo https://www.kimai.org/demo/ and verify if the problem exists there as well. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1537>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH3UXIPVI7ZT66ZF34PNCYLRGUBMNANCNFSM4LEJPJUA> .

-- Furkan TOLU Gsm: +90(546) 692 0712 www.furkantolu.net

[kevinpapst]

Not entirely sure, but I doubt that it is related to the Kimai version.
I can't remember that anything related to encoding was changed between 1.7 and 1.8.

Maybe your PHP does not support the multibyte extension?
https://www.php.net/manual/tr/book.mbstring.php

[adrianrudnik]

Excel again, I love it ;-)

Excel depends on a BOM header to identify UTF8 files correctly while the rest of the world not really does (and most PHP functions do not force or support it).

Different approaches (depending on the Excel version):

Excel: Change default import format

Change the default import format for excel using a registry edit, i.e. for newer Excel (will work with Excel 15.0 but not 16.0 anymore):

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Options]
"DefaultCPG"=dword:00065001

Excel: Change encoding during import

If you double click the CSV you will always import the CSV without being asked the default encoding in newer versions. The proper way would be to create a blank spreadsheet, go to the Data tab and import from Text/CSV and specify UTF-8 there.

Add bom to CSV export

Add hex U+FEFF to the beginning of the UTF8 export. The only "clean" way without searching / replacing or re-cat-echoing the files would be

apt install icu-devtools
uconv --add-signature kimai-export.csv > kimai-export-fixed.csv

You could also try to fix it with other things, maybe a standardized ICU package from PHP. But this solution must be checked against older Excel versions prior 2013 to see if that does not introduce other options. Or split the CSV formats into distrinctive (UTF-8 with BOM, UTF-8 without BOM, ISO).

[kevinpapst]

Hahaha, Microsoft for the win 😁
Well, I doubt that many Excel users will use CSV, so I guess I will not do anything about it, besides adding a sentence to the documentation.
Thanks for the information @adrianrudnik

[adrianrudnik]

Checked Excel myself, looks clean. Edge case is only UTF-8 CSV export to Excel that is reproducible.

[kevinpapst]

Added that issue to the documentation for the CSV invoice format. Closing here, as I don't see what else we could do.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.