Creating a CSV file for excel with special character

Question

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

You may need to do some character conversion before excel can handle it. — Matt
– Matt, Commented Aug 6, 2012 at 16:21
If you're writing csv, consider PHP's fputcsv() function rather than creating your own csv string — Mark Baker
– Mark Baker, Commented Aug 6, 2012 at 16:23
As much trouble as they generally cause, a byte order marker might actually help here as (one would hope) it will force Excel into UTF8 mode. You should certainly look at fputcsv() here though, it will make your life sooo much easier and your code less error-prone. — DaveRandom
– DaveRandom, Commented Aug 6, 2012 at 16:24

user1419445 · Accepted Answer · 2012-08-06 20:19:48Z

1

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.

answered Aug 6, 2012 at 20:19

user1419445

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a CSV file for excel with special character

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related