Csv File Doesn't Show German Characters After Downloading via .Net Web Api

Question

I have to return a csv file template which has some columns with german characters in my one of my api enpoint.

This is the simplified version of endpoint:

    [HttpGet]
    [ProducesResponseType(typeof(Stream), StatusCodes.Status200OK)]
    [Route("template")]
    public async Task GetTemplate()
    {
        Response.StatusCode = StatusCodes.Status200OK;
        Response.ContentType = "text/csv";
        Response.Headers.AddContentDispositionAttachment("Template.csv");

        CsvConfiguration csvConfiguration = new (new CultureInfo("de-de"))
        {
            Delimiter = ";",
            HasHeaderRecord = false
        };

        string header = $"Url;Beschreibung;Code;ID;Löschen;Straße;Pünklitch";

        using var streamWriter = new StreamWriter(Response.Body, Encoding.UTF8, 65536);
        await using var csvWriter = new CsvWriter(streamWriter, csvConfiguration);
        await streamWriter.WriteAsync(header);
    }

It is pretty straight forward.

The problem is; when I call endpoint via swagger in local or on server and download the file it doesn't show german characters properly. Also when I entagrate endpoint to my Front end and download file via ui it also behave as same way and doesn't show german characters. But when I call the endpoint from browser directly everything looks ok.

This is how csv file looks after download:

Any idea ? How can I fix encoding problem?

I am using .net framwork 6

Answer 1

The problem is when you open a CSV file by double clicking on it to open it in Excel, Excel assumes the file is in an older Windows encoding, rather than UTF8. You have two options:

1. Force a Byte Order Mark (BOM) at the beginning of the file for UTF8. Most newer versions of Excel will recognize the BOM and open it as UTF8 instead of the older encoding. There are some versions of Excel, however, that will ignore the BOM. (FYI your code is not using the csvWriter, but this should still work when you do output with csvWriter rather than streamWriter directly.)

using var streamWriter = new StreamWriter(Response.Body, Encoding.UTF8, 65536);
using var csvWriter = new CsvWriter(streamWriter, csvConfiguration);

var preamble = Encoding.UTF8.GetPreamble();
await streamWriter.BaseStream.WriteAsync(preamble, 0, preamble.Length);

await streamWriter.WriteAsync(header);

2. Use the older Windows encoding instead of UTF8. This should work as long as you only have German characters and not other UTF8 characters that won't convert. I'm not certain what it would be in Germany, but I'm going to guess it is Windows-1252.

using var streamWriter = new StreamWriter(Response.Body, Encoding.GetEncoding(1252), 65536);

// Or you might need to use iso-8859-1
using var streamWriter = new StreamWriter(Response.Body, Encoding.GetEncoding("iso-8859-1"), 65536);