Writing a string to MemoryStream corrupts the input

Question

I have an ASP.net project and want to return a CSV file when an AJAX post is sent (yes it works. See Handle file download from AJAX post). The special thing is, that I want to create the result in a MemoryStream to return it as FileResult. But my problem is now, that German umlauts (ä, ö, ü) get corrupted. So here is my code:

public ActionResult Download(FormCollection form) {
string[] v = new string[16];
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream, 
    System.Text.Encoding.GetEncoding("Windows-1252"));
SqlCommand cmd = dbconn.CreateCommand();
//create SQL command
while (rs.Read()) {
  v = new string[16];
  v[0] = rs.GetString("IstAktiv");
  v[1] = rs.GetString("Haus");
  //cache all the values
  ...
  //write cached values
  for (int i = 0; i < v.Length; i++) {
    if (i > 0) writer.Write(";");
    writer.Write(v[i]);
    writer.Flush();
  }
  writer.Write("\r\n");
  writer.Flush();
} //end while rs.Read()

FileContentResult ret = new FileContentResult(stream.ToArray(), "text/csv");
ret.FileDownloadName = "Kontakte.csv";
writer.Close();
return ret;
} //end method

So when I open the resulting file in Excel the umlauts are converted into something strange. For example the upper case letter "Ä" is changed to "�". So is there any possibility to solve this issue?

Best regards

Answer 1

To have Excel read CSV files correctly, it expects the CSV file to be in the UTF-8 encoding (with BOM).

So, without a doubt, your StreamWriter would have to be set this way:

StreamWriter writer = new StreamWriter(stream, 
    System.Text.Encoding.GetEncoding("UTF-8"));

However, if that doesn't work for you, then it's very likely that it's because the characters are being corrupted before you even get a chance to write them to the stream. You may be facing an encoding conversion problem as you are reading the data from the database.

v = new string[16];
v[0] = rs.GetString("IstAktiv");
v[1] = rs.GetString("Haus");

To validate that, place a breakpoint as you read the values into the 'v' array, and check that the characters still look ok at this step. If they are corrupted, then you know that the problem is between the code and the database, and the writing to the CSV is not the problem.

EDIT: Here is an isolated test case you can use to prove that UTF-8 is the correct encoding to write CSVs. Perhaps you can try that first:

 Encoding enc = Encoding.GetEncoding("UTF-8");
 using (StreamWriter writer = new StreamWriter(@"d:\test\test.csv", false, enc))
 {
    writer.Write(@"""hello ä, ö, ü world""");
 }