StreamWriter Response output - looks fine in Notepad but accents are garbage in Excel

Question

Hi there,

I'm using a technique from another stackoverflow question here, to write a CSV file to the Response output for a User to Open/Save. This is working fine and the file looks good in Notepad, but when I open it in Excel the accented characters are garbage. I assumed this was something to do with the character encoding, so tried manually to set it to utf-8 (the StreamWriter is utf-8 by default anyhow). Here is the code:

context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=registros.csv");
context.Response.ContentType = "text/csv";
context.Response.Charset = "utf-8";

using (StreamWriter writer = new StreamWriter(context.Response.OutputStream))
{
// .... write the output file
}
context.Response.End();

Any ideas as to what else I would need to do, to correctly encode the file so as Excel can view the accented characters?

Answer 1

You may have to write an UTF-8 indicator called Byte-order Mark to the beginning of the output to notify Excel about the UTF-8ness. Silly Excel.

Answer 2

Hello,

I am having the same issue. Would you be able to post how exactly you used Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble())?

I tried this but it does not work.

            Response.Clear();
            Response.AddHeader("content-disposition", "attachment; filename=" + progName + ".csv");
            Response.ContentType = "application/csv";
            Response.Charset = "utf-8";

            using (StreamWriter writer = new StreamWriter(Response.OutputStream))
            {
                Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble());
                DataTable dt = ds.Tables[0];
                int colCount = dt.Columns.Count;
                for (int i = 0; i < colCount; i++)
                {
                    writer.Write(dt.Columns[i]);
                    if (i < colCount - 1)
                    {
                        writer.Write(",");
                    }
                }
                writer.Write(writer.NewLine);
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < colCount; i++)
                    {
                        if (!Convert.IsDBNull(dr[i]))
                        {
                            writer.Write(dr[i].ToString());
                        }
                        if (i < colCount - 1)
                        {
                            writer.Write(",");
                        }
                    }
                    writer.Write(writer.NewLine);
                }
                writer.Close();
            }
            Response.End();

Many thanks, J

Answer 3

Sure James, here's a shortened version:

List<FullRegistrationInfo> fullUsers = GetFullUserRegistrations(); // This fills a list to enumerate - each record is one CSV line
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=registros.csv");
context.Response.ContentType = "text/csv";
context.Response.Charset = "utf-8";

using (StreamWriter writer = new StreamWriter(context.Response.OutputStream))
{
    for (int i = 0; i < fullUsers.Count(); i++)
    {
        FullRegistrationInfo record = fullUsers[i]; // Get the record to process

        // If it's the first record then write header
        if (i == 0)
            writer.WriteLine(Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble()) + 
                "User, First Name, Surname");

        writer.WriteLine(record.User + "," +
                         record.FirstName + "," +
                         record.Surname);
    }
}

context.Response.End();

As you can see - on the first line of the CSV file where the headers are written out, the Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble()) data is the first byte written to the file.