Folks,
we're trying to stream back a result set from a SQL query into Excel
from our ASP.NET application.
Basically, we're doing this here:
Response.ContentType = "text/csv";
Response.Charset = "utf-8";
Response.AddHeader("Content-Disposition", "attachment;
filename=data.csv");
Response.Write(sb.ToString());
Response.End();
"sb" is our StringBuilder object which contains the data we're
streaming back.
The trouble is: we have German, French and Italian special characters
in the response, stuff like German umlauts (ä ö ü) and French accents
(é è à and so forth).
When Excel (both 2003 and 2007) pops up, it reads in the contents of
the CSV stream, but it mangles the special characters. It almost looks
as if Excel insists on interpreting this as ANSI / "Windows-1252" /
ISO-8859-1 characters (even though I *specifically* told it that the
response had a "utf-8" charset) and fails to get the special ones
right....
I end up with stuff like Zürich instead of Zürich or Genève instead
of Genève and so forth.
Funny thing: if I choose to SAVE the resulting CSV file, and then open
it in Excel, I get the text file import wizard and I'm able to pick
the encoding - setting it to UTF-8 gives me the desired results.
On the other hand, if I save the file and then double-click it, I get
the same results as when opening the result stream directly - no
wizard, no way of specifying the encoding, and Excel just assuming
"ANSI" and screws up the special characters....
Any ideas or ways to get around this?? I'm totally baffled that
something as widely used as Excel has such trouble handling UTF-8
files and the special chars inside them!
Thanks for any help or pointers
Marc
