CODEWITHSUNDEEP
c#.netexcelcsvutf-8
nimblebit
nimblebit

Reputation: 559

UTF-8 CSV file created with C# shows  characters in Excel

When a CSV file is generated using C# and opened in Microsoft Excel it displays  characters before special symbols e.g. £

In Notepad++ the hex value for  is: C2

So before writing the £ symbol to file, I have tried the following...

    var test = "£200.00";
    var replaced = test.Replace("\xC2", " ");

    StreamWriter outputFile = File.CreateText("testoutput.csv"); // default UTF-8
    outputFile.WriteLine(replaced);
    outputFile.Close();

When opening the CSV file in Excel, I still see the "Â" character before the £ symbol (hex equivalent \xC2 \xA3); It made no difference.

Do I need to use a different encoding? or am I missing something?

Upvotes: 20

Views: 31252

Answers (2)

nimblebit
nimblebit

Reputation: 559

Thank you @Evk and @Mortalier, your suggestions lead me to the right direction...

I needed to update my StreamWriter so it would explicitly include UTF-8 BOM at the beginning http://thinkinginsoftware.blogspot.co.uk/2017/12/correctly-generate-csv-that-excel-can.html

So my code has changed from: 

StreamWriter outputFile = File.CreateText("testoutput.csv"); // default UTF-8

To:

StreamWriter outputFile = new StreamWriter("testoutput.csv", false, new UTF8Encoding(true))

Or: Another solution I found here was to use a different encoding if you're only expecting latin characters... http://theoldsewingfactory.com/2010/12/05/saving-csv-files-in-utf8-creates-a-characters-in-excel/

StreamWriter outputFile = new StreamWriter("testoutput.csv", false, Encoding.GetEncoding("Windows-1252"))

My system will most likely use latin & non-latin characters so I'm using the UTF-8 BOM solution.

Final code

    var test = "£200.00";
    StreamWriter outputFile = new StreamWriter("testoutput.csv", false, new UTF8Encoding(true))
    outputFile.WriteLine(test);
    outputFile.Close();

Upvotes: 33

Mortaliar
Mortaliar

Reputation: 58

I tried your code and Excel does show AŁ in the cell. Then I tried to open the csv with LibreOffice Clac. At first there too was AŁ, but on import the program will ask you about encoding. Once I chose UTF-8 the £ symbol was displayed correctly. My guess is that in fact there is an issue with your encoding.

This might help with Excel https://superuser.com/questions/280603/how-to-set-character-encoding-when-opening-excel

Upvotes: 3

Related Questions