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

What you see in Excel depends how you import the file into Excel. If it looks wrong in Excel that does not mean your code is wrong. But you could try adding `Response.ContentEncoding = Encoding.UTF8;` — Klaus Gütter, Dec 19 '22 at 11:48
@KlausGütter there is no ContentEncoding property in HttpResponse. Any equvalent ? — nzrytmn, Dec 19 '22 at 11:55
this is not an encoding issue. You just need to switch to a Font that contains the German characters. — jdweng, Dec 19 '22 at 12:24
Does this answer your question? [Is it possible to force Excel recognize UTF-8 CSV files automatically?](https://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically) — NineBerry, Dec 19 '22 at 12:34
@NineBerry thanks for link but I checked this before but I couldn't figure out the solution. And thanks for typo warning :) — nzrytmn, Dec 19 '22 at 19:26

score 3 · Accepted Answer · answered Dec 19 '22 at 17:37

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:

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);

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);

score -1 · Answer 2 · answered Dec 19 '22 at 12:29

You can make a custom Ascii converter which will replace all your chars with an ascii symbol. for example you could use Dictionary<string, string> and map each char to it's ascii

private Dictionary<string, string> CharToASCII = new Dictionary<string, string>() {
    {"Ö",  @"\'d6"},
    {"ö",  @"\'f6"}
    }

then you can just call a function with your string as an input parameter and as a result you will have an ascii written instead of German letters

   public string ConvertToAscii(string myString)
        {

            var res = CharToASCII.Aggregate(myString, (current, value) =>
                current.Replace(value.Key, value.Value));


            return res;
        }

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

2 Answers2