(simple) CSV download with special characters drives me nuts

Question

this one is driving me nuts. And last week i thougt i cracked the nut, but unfortunately not....

so i want to create a CSV download for other users to open up in MS-Word (they want the CSV format)

so i end up with this code in a MVC2 controller:

Response.AddHeader("Content-Disposition", "attachment; filename=PersonalMessages.csv");
Response.ContentType = "application/csv";
string s = new DownloadService().GetAddresses();
Response.Write(s);
Response.End();
return null;

the string 's' contains (among other info) these chars: é å æ É à

When i open it in notepad: OK

when i open it in Notepad++: Ok

when i open it up in Excel: Not ok, it shows this: é å æ É à

(when i open it up in Notepadd++, it says it is in UTF8 encoding without BOM, whatever i tried with UTF8 encodings with booleans in the constructor to get the BOM in, it didn;t work out)

So i asked a question last week, and the answer was to let the GetAddresses() function return a byte array.

So i did convert my text using this:

// C# to convert a string to a byte array.
public static byte[] StrToByteArray(string str)
{
    System.Text.UnicodeEncoding encoding = new System.Text.UnicodeEncoding();
    return encoding.GetBytes(str);
}

, and wrote the byte array in the response and all was fine! Notepad, notepad++ and Excel! Great, live is good.

But then i found out they we're opening up the file in Word. Well, no problem i thought.

But then they aid: Word can not open the file (diretly), it asks for the encoding first, and that is a problem because they are using it in an automated process.

When they open the file in Notepad and save it as unicode, all goes well.

I've also tried this in the action method:

Response.ContentEncoding = Encoding.Unicode ;
Response.HeaderEncoding = Encoding.UTF8;

but that didn't help out.

Does anybody has any clue?

Answer 1

Try using ISO-8859-1 instead of UTF-8:

public ActionResult Index()
{
    var encoding = Encoding.GetEncoding("iso-8859-1");
    var data = encoding.GetBytes("éåæÉà;some other value");
    return File(data, "application/csv", "PersonalMessages.csv");
}

Also notice the slight simplification and the usage of FileResult.

Another possibility which is what I would recommend you is to explicitly append the preamble so that Excel can recognize the UTF-8 encoding:

public ActionResult Index()
{
    var data = Encoding.UTF8.GetBytes("éåæÉà;some other value");
    var result = Encoding.UTF8.GetPreamble().Concat(data).ToArray();
    return File(result, "application/csv;charset=utf-8", "PersonalMessages.csv");
}

Answer 2

I've tried many encodings, the one that worked is Windows-1252 :

Response.Clear();
Response.ContentType = "Application/x-msexcel";
Response.AddHeader("content-disposition", "attachment; filename=\"filename.csv\"");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("Windows-1252");
Response.Write(string.Join(Environment.NewLine, myDataLines));
Response.End();

Answer 3

Use ISO-8859-1 instead of UTF-8

Its working fine.

i tried in XLSX document.