how to open csv with unicode data without import document

Question

Below is how i write data with unicode character in root folder

<?php
mysql_connect('localhost','root','');
mysql_query ("set character_set_client='utf8'"); 
mysql_query ("set character_set_results='utf8'"); 
mysql_query ("set collation_connection='utf8_general_ci'"); 
mysql_select_db('test') or die('3');

$q = mysql_query("SELECT * FROM `t1`");
$d = '';
while ($r = mysql_fetch_array($q)) {
    $d .= $r['col1'];
}

function writeUTF8File($filename,$content) {
    $f=fopen($filename,"w");

    # Now UTF-8 - Add byte order mark
    fwrite($f, pack("CCC",0xef,0xbb,0xbf));
    fwrite($f,$content);
    fclose($f);
}

writeUTF8File('utf.csv', $d);
?>

but when open this document with Microsoft Office Excel 2007 , the data become

enter image description here

after do quick research i need to import using "From Other Sources" then i can see correct data

enter image description here

So the question is how to open this document without import("From Other Sources"), because later this document i need pass to my customer(not sure they know about import and do some setting before can see all data).

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

Mine Excel performs well on UTF8 csv files with BOM.

Check your csv file with hex editor. I'm pretty sure you have characters shown in Excel encoded, instead of desired ones.

Try following:

$f=fopen($filename,"wb");

This will write PHP string as is in binary form, instead of encoding all characters.

Additional check: If you output $d to HTML, would it show correct characters?

I got also some information from fwrite() and UTF8.

edited May 23 '17 at 11:53

Community

1
1

answered Sep 12 '13 at 08:43

LS_ᴅᴇᴠ

10,823
1
23
46

result from hex editor is something like this `EF BB BF E6 89 93 E6 8B 9B E5 91 BC E7 9A 84 E6` and output for `$d` in my browser is `打招呼的朋友` – rusly Sep 12 '13 at 15:39

how to open csv with unicode data without import document

1 Answers1