0

I have the following C# code that writes Umlaute characters to a csv file. Upon opening the file these characters are completely distorted. Here is the code:

string csvFile = string.Empty;
using (System.IO.StreamWriter file = new System.IO.StreamWriter(fileName))
{
    //DataTable columns are already formatted WRT commas and double quotes
    for (int i = 0; i < dt.Rows.Count; i++)
         csvFile += String.Join(",", dt.Rows[i].ItemArray) + "\r\n";

    file.WriteLine(csvFile);
}

I checked the string 'csvFile' and it does hold these characters correctly. Is this excel issue or within my code?

stuartd
  • 70,509
  • 14
  • 132
  • 163
Sandeep
  • 57
  • 1
  • 8
  • 1
    _Is this excel issue or within my code_ If you open the CSV file as text, are the characters shown correctly? If so, it's an Excel problem. – stuartd Oct 05 '16 at 09:23
  • Yes it does show up correctly in Notepad – Sandeep Oct 05 '16 at 09:24
  • http://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically – decPL Oct 05 '16 at 09:26
  • try manually creating a text file and write "ä,ö,ü,ß,Ä,Ö,Ü;" on it. save it with .csv extension and open it with excel. do you see the characters correctly? if so, the problem is the formatting of the csvFile variable – Innat3 Oct 05 '16 at 09:27
  • "Open" the file in Excel using => Data => From external => Text, then specify the encoding as UTF-8. Do they display correctly then? Excel CSV is by default not Unicode. FileStream on the other hand writes by default as UTF-8. – LocEngineer Oct 05 '16 at 09:30

1 Answers1

2

You didn't add the correct BOM characters to your output file, so Excel doesn't know with encoding it should use. To circumvent this try adding the Encoding to your Streamwriter like this:

System.IO.StreamWriter file = new System.IO.StreamWriter(fileName, new UTF8Encoding(true))

EDIT: As stuartd commented this may not work correctly in every Excel version

Community
  • 1
  • 1
R.jauch
  • 103
  • 4
  • This [may not always work](http://stackoverflow.com/a/155176/43846) in older versions of Excel – stuartd Oct 05 '16 at 09:50
  • This answer is correct. Using the `Encoding.UTF8` static property would also work since it too will emit the byte-order-mark (BOM). It is a bit confusing that `new UTF8Encoding()` is _without_ BOM while `Encoding.UTF8` is _with_ BOM. The default for `StreamWriter` when nothing is specified is to not use BOM. – Jeppe Stig Nielsen Oct 05 '16 at 10:33
  • An alternative approach is to use `Encoding.Default` which will depend on the current machine's ANSI code page. This will probably match what Excel expects. This is not a necessarily a good solution if your application is to be used on other computers than your own. – Jeppe Stig Nielsen Oct 05 '16 at 10:41