Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I output a UTF-8 CSV in PHP that Excel will read properly?

I've got this very simple thing that just outputs some stuff in CSV format, but it's got to be UTF-8. I open this file in TextEdit or TextMate or Dreamweaver and it displays UTF-8 characters properly, but if I open it in Excel it's doing this silly íÄ kind of thing instead. Here's what I've got at the head of my document:

header("content-type:application/csv;charset=UTF-8"); header("Content-Disposition:attachment;filename=\"CHS.csv\""); 

This all seems to have the desired effect except Excel (Mac, 2008) doesn't want to import it properly. There's no options in Excel for me to "open as UTF-8" or anything, so … I'm getting a little annoyed.

I can't seem to find any clear solutions to this anywhere, despite a lot of people having the same problem. The thing I see the most is to include the BOM, but I can't exactly figure out how to do that. As you can see above I'm just echoing this data, I'm not writing any file. I can do that if I need to, I'm just not because there doesn't seem like a need for it at this point. Any help?

Update: I tried echoing the BOM as echo pack("CCC", 0xef, 0xbb, 0xbf); which I just pulled from a site that was trying to detect the BOM. But Excel just appends those three characters to the very first cell when it imports, and still messes up the special characters.

like image 337
Ben Saufley Avatar asked Dec 03 '10 18:12

Ben Saufley


People also ask

How do I make Excel read UTF-8?

Click Tools, then select Web options. Go to the Encoding tab. In the dropdown for Save this document as: choose Unicode (UTF-8). Click Ok.

How do I change the default Encoding to UTF-8 in CSV?

One easy way to change excel ANSI encoding to UTF-8 is the open the . csv file in notepad then select File > Save As. Now at the bottom you will see encoding is set to ANSI change it to UTF-8 and save the file as a new file and then you're done.


1 Answers

I have the same (or similar) problem.

In my case, if I add a BOM to the output, it works:

header('Content-Encoding: UTF-8'); header('Content-type: text/csv; charset=UTF-8'); header('Content-Disposition: attachment; filename=Customers_Export.csv'); echo "\xEF\xBB\xBF"; // UTF-8 BOM 

I believe this is a pretty ugly hack, but it worked for me, at least for Excel 2007 Windows. Not sure it'll work on Mac.

like image 100
Daniel Magliola Avatar answered Oct 01 '22 08:10

Daniel Magliola