Line breaks in generated csv file driving me crazy

Question

I'm trying to make an export of 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.

I've searched for hours, seen many solutions, but i just can't seem to get it fixed.

The way i output my csv file: (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.

Unfortunately i can't give you an example of the real data i work with (it's all personal data), but i can give you an example of how it goes 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 quotationmarks by default. I also remove leading spaces from values by default.

I've spent at least 2 days on this seemingly simple problem, but for the life of me, i can't figure out how i can fix it. I've seen multiple topics on this same problem, but none of the solutions offered there seem to fix this.

Answer 1

This works for me:

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

b) 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 + "\"");

c) 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 to ",". 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.

Answer 2

Step 1: Use “\n” where need to break the text value to next line as shown below.

String value = "I fear not the man who has practiced 10,000 kicks once, \n but I fear the man who has practiced one kick 10,000 times.";

Step 2: Use extension method. It will check the text index to break the text value.

public static class ExtensionMethods
{
    static char[] SpecialCharacters = new char[] { ',', '"', '\r', '\n' };
    public static string ToWrap(this string val)
    {
        StringBuilder builder = new StringBuilder();
        bool firstColumn = true;

        // Add separator if this isn't the first value
        if (!firstColumn)
            builder.Append(',');
        // Implement special handling for values that contain comma or quote
        // Enclose in quotes and double up any double quotes
        if (val.IndexOfAny(SpecialCharacters) != -1)
            builder.AppendFormat("\"{0}\"", val.Replace("\"", "\"\""));
        else
            builder.Append(val);
        firstColumn = false;

        return builder.ToString();
    }
}

Step 3: After creating the extension method. Use the extension method in string variable which to wrap the text value.

Value.ToWrap();

Answer 3

You are allowed to have a line break in a CSV file as long as the field is properly double quoted. The issue here seems to be getting Excel to import the record correctly.

This has been asked previously on the site, with a few possible solutions:

• Set the encoding on the output file to ASCII or UTF-8. Since you're setting it to UTF-8 in the question, try ASCII next. (link)

• Change the file name to .csv, which might trick Excel into importing the file correctly (link)

Answer 4

I had the same problem. I arrived at the solution while browsing this blog in the section - "Export to Excel with proper formatting:"

I modified my code as follows:

string brstyle = @"<style>br { mso-data-placement:same-cell; }</style>";

Response.Write(brstyle);

Response.Write(stringWriter.ToString());

It worked for me. Now the text with linebreaks is appearing in single cells rather than in new cell (row) for each linebreak.

Answer 5

I don't think you can have a newline in a CSV field since a newline indicates a new record. Can you replace all newlines in the field value before adding it to the string? Or maybe you can build an Excel file, they should allow newlines in a field.

Answer 6

UTF files that contain a BOM will cause Excel to treat new lines literally even in that field is surrounded by quotes. (Tested Excel 2008 Mac)

The solution is to make any new lines a carriage return (CHR 13) rather than a line feed.