Elliott
Elliott

Reputation: 186

Creating a CSV file for excel with special character

I have created a simple PHP script as a test. I want to have PHP save a csv from some data. This script works, but when I open it in Excel the special characters are gibberish.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Character Test</title>
</head>

<body>
<ul>
<?php
$spanishDept = array('Swimwear'=>'Trajes de baῆo','Boys Swimwear'=>'Trajes de baῆo','Girls Swimwear'=>'Trajes de baῆo','Kids Shoes'=>'Zapatos de niῆos',"Scarves"=>'Paῆoletas','Children\'s'=>'Niῆos','Coffee Mugs'=>'Tasas para café');

$list = '"Item","Spanish"'."\r";
foreach($spanishDept AS $english=>$spanish) {
        echo '<li>'.$english.': '.$spanish.'</li>';
        $list .= '"'.$english.'","'.$spanish."\"\r";
}
$fp = fopen('theEXCELlist.csv',"w+");
fwrite($fp,$list,strlen($list)+100);
?>
</ul>
</body>
</html>

I can open it in NotePad++ or Dreamweaver and all is fine, but in Excel no good

Upvotes: 1

Views: 3063

Answers (1)

user1419445
user1419445

Reputation:

Maybe the solution to this problem lies in the "Multibyte String Functions" section of the PHP manual.
In other words, it seems that the PHP output file should be an UTF-16 little endian encoded file (with BOM).
I don't know which version of Excel you're using, but this post may be useful.
Another approach could be to open Excel, insert the special characters, save as CSV and finally check how these characters are encoded in the output file.

Upvotes: 1

Related Questions