Alex Rivera | Logout

Microsoft Excel mangles Diacritics in .csv files?

Asked 2008-09-30T21:07:06.827
210

I am programmatically exporting data (using PHP 5.2) into a .csv test file.
Example data: Numéro 1 (note the accented e). The data is utf-8 (no prepended BOM).

When I open this file in MS Excel is displays as Numéro 1.

I am able to open this in a text editor (UltraEdit) which displays it correctly. UE reports the character is decimal 233.

How can I export text data in a .csv file so that MS Excel will correctly render it, preferably without forcing the use of the import wizard, or non-default wizard settings?

excel encoding csv diacritics
Edit
Report

3 Answers

1

Excel 2007 properly reads UTF-8 with BOM (EF BB BF) encoded csv.

Excel 2003 (and maybe earlier) reads UTF-16LE with BOM (FF FE), but with TABs instead of commas or semicolons.

answered 2009-11-05T09:56:03.233
0

If you have legacy code in vb.net like I have, the following code worked for me:

    Response.Clear()
    Response.ClearHeaders()
    Response.ContentType = "text/csv"
    Response.Expires = 0
    Response.AddHeader("Content-Disposition", "attachment; filename=export.csv;")
    Using sw As StreamWriter = New StreamWriter(Context.Response.OutputStream, System.Text.Encoding.Unicode)
        sw.Write(csv)
        sw.Close()
    End Using
    Response.End()
answered 2011-12-13T11:06:04.090
-2

open the file csv with notepad++ clic on Encode, select convert to UTF-8 (not convert to UTF-8(without BOM)) Save open by double clic with excel Hope that help Christophe GRISON

answered 2013-04-25T16:54:44.327

Your Answer