Line breaks in generated csv file driving me crazy

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)

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.

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();