How to import a CSV with linebreaks as values into Excel

Question

I'm trying to export some data I have (stored in a datatable). Some of those values have a linebreak in them. Now every time I try and import the file in Excel 2010, the linbreaks get recognised as a new row, instead of an actual linebreak.

The way I output my CSV file (the variable csvfile is a stringbuilder):

context.Response.Clear();
context.Response.ContentType = "text/csv";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");
context.Response.Write(csvfile.ToString());
context.Response.End();

When I open it with Excel manually, it displays fine. But because Excel 2003 doesn't support the file format, I have to import it. With the import, it sees the linebreaks (\n in the fields) as a new row.

Here is an anonymised example of the data gone wrong:

Header1,Header2,Header3
"value1","value2","value 3
and this is where its going wrong"

It's a simple CSV file, and when you import it you'll see where it goes wrong. I encapsulate fields with double quotation marks by default. I also remove leading spaces from values by default.

Answer 1

Any of the three solutions below work for me:

• Setting Response.ContentEncoding = System.Text.Encoding.UTF8 isn't enough to make Excel open UTF-8 files correctly. Instead, you have to manually write a byte-order-mark (BOM) header for the excel file:

  if (UseExcel2003Compatibility)
  {
      // write UTF-16 BOM, even though we export as utf-8. Wrong but *I think* the only thing Excel 2003 understands
      response.Write('\uFEFF');
  }
  else
  {
      // use the correct UTF-8 bom. Works in Excel 2008 and should be compatible to all other editors
      // capable of reading UTF-8 files
      byte[] bom = new byte[3];
      bom[0] = 0xEF;
      bom[1] = 0xBB;
      bom[2] = 0xBF;
      response.BinaryWrite(bom);
  }
  

• Send as octet-stream, use a filename with .csv extension and do quote the filename as is required by the HTTP spec:

  response.ContentType = "application/octet-stream";
  response.AppendHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
  

• use double quotes for all fields

I just checked and for me Excel opens downloaded files like this correctly, including fields with line breaks.

But note that Excel still won't open such CSV correctly on all systems that have a default separator different from ",". E.g. if a user is running Excel on a Windows system set to German regional settings, Excel will not open the file correctly, because it expects a semicolon instead of a comma as separator. I don't think there is anything that can be done about that.