Writing CSV file - encoding problems

Question

I have a CSV string in PHP that's detected by mb_detect_encoding as UTF8. This string is written to a file. When I open it in a text editor (Programmers Notepad), the editor says it's UTF8 No Mark and when I change the encoding to ANSI, it opens correctly in Excel. I would like to write the file in ANSI so that I don't have to convert it in a text editor before I open it in Excel. Tried to use the following:

utf8_decode($xhtml)
iconv("UTF-8", "Windows-1252", $xhtml)
iconv("UTF-8", "ISO-8859-1", $xhtml)
mb_convert_encoding($xhtml, "ISO-8859-1", "UTF-8")

With utf8_decode the special characters show as question marks. iconv and mb_convert_encoding cut the strin at the first special character. Any idea on how to get this string into a file that opens in Excel?

Does this questions refer to PHP or Excel? You should add PHP to the tag list or just mention it in your question to receive any qualified answer. No PHP dude will find your question, although it seems to be interesting... — Jan Rothkegel
It refers to PHP. Added tag and edited the question. Thanks for pointing it out. G. — guntersammet

adilbo adilbo · Accepted Answer · 2015-12-16T14:29:25

So you probably exported your lovely database, perfectly stored in UTF-8 encoding, and then you open the csv in Excel, and see a bunch of crazy-looking characters where ä, Õ and é should be?

Well, here’s a fix: add a Byte-Order-Mark to your file. Excel just needs it desperately..

function customer_download($file) {
    header('Content-Type: application/force-download');
    header('Content-Description: File Transfer');
    header('Content-disposition: attachment; filename="'.$file.'"');
    @include_once('file_get_contents.php');
    $csv = @file_get_content($file);
    die(chr(0xEF).chr(0xBB).chr(0xBF).$csv);
}

Writing CSV file - encoding problems

1 Answers