excel - PHP export CSV UTF-8 with BOM doesn't work -

i have been stuck days on exporting utf-8 csv chinese characters shows garbled text on windows excel. using php , have added bom byte mark , tried encoding no luck @ all.

they open fine on notepad++, google spreadsheet , on mac numbers. not on excel requirement client. when opening notepad++ encoding shown utf-8. if change utf-8 manually , save, file opens fine on excel.

it seems though bom byte mark doesn't saved in output notepad++ detect utf-8 without bom.

also, csv not saved on server. data retrieved db , exported directly out.

here codes:

// setup headers header('cache-control: must-revalidate, post-check=0, pre-check=0'); header('content-description: file transfer'); header("content-type: text/csv"); header("content-disposition: filename=".$filename.".csv"); header("pragma: no-cache");  // first method $fp = fopen('php://output', 'w'); // add bom fix utf-8 in excel, doesn't work fputs($fp, chr(0xef) . chr(0xbb) . chr(0xbf) );  if ($fp) {      fputcsv($fp, array("header"), ",");     fputcsv($fp, array($string_with_chinese_chars), ","); }  fclose($fp); exit();  // second method $csv = ""; $sep = ","; $newline = "\n"; // tried php_eol  $csv .= "header"; $csv .= $newline; $csv .= $string_with_chinese_chars; $csv .= $newline;  // tried below ways doesn't work. // method 2.1 print chr(255) . chr(254) . mb_convert_encoding($csv, 'utf-16le', 'utf-8');  // method 2.2 print chr(239) . chr(187) . chr(191) . $csv;  // method 2.3 print chr(0xef).chr(0xbb).chr(0xbf); print $newline; print $csv; 

based on comment above, looks script accidentally printing out newline (hex 0a) before utf-8 bom, causing excel not recognize output utf-8.

since you're using php, make sure there's no empty line before <?php marker in script, or in other php file might include. make sure none of files include has whitespace after closing ?> marker, if there one.

in practice, can quite hard do, since many text editors insist on appending newline end of last line. thus, safest , easiest solution leave out ?> marker php files, unless intend print out whatever comes after it. php not require ?> present, , using in files not meant mixed php , literal template html (or other text) asking bugs this.