CODEWITHSUNDEEP
c#excelcsvexcel-2010
Melle Groenewoud
Melle Groenewoud

Reputation: 153

How to import a CSV with linebreaks as values into Excel

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.

Upvotes: 15

Views: 35811

Answers (5)

Ben Schwehn
Ben Schwehn

Reputation: 4565

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.

Upvotes: 5

Rex Andrew
Rex Andrew

Reputation: 408

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

Upvotes: 4

deepakg_rao
deepakg_rao

Reputation: 51

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.

Upvotes: 0

Stephen
Stephen

Reputation: 373

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.

Upvotes: 0

Factor Mystic
Factor Mystic

Reputation: 26760

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)

Upvotes: 2

Related Questions