CODEWITHSUNDEEP
excelexport-to-excel
Shiv
Shiv

Reputation:

Exporting a Unicode .csv (comma separated) file to either Excel 2003 Or Excel 2007 results in all columns ending up in the first column in Excel

Is there anything we can do either in code (ASP/JavaScript) or in Excel so that the comma separated values end up in separate columns in Excel?

Upvotes: 1

Views: 8433

Answers (9)

JollySwagman
JollySwagman

Reputation: 81

I just tried using Tab characters for field seperators instead of commas and it worked in Excel 2007

(at least I think it's 2007, can't find Help/About in the stupid ribbon)

Upvotes: 1

will-mvn
will-mvn

Reputation: 659

Excel seems to get confused by UTF-16/UTF-8 byte order marks, so try getting rid of them.

With CSV, the contents of the cells can be unicode characters, but the separator, quote and newline characters always must be be ASCII. You can think about CSV as always being ASCII but each cell as a blob of binary and might be some unicode text.

Also, have a look at: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm for more info.

Upvotes: 0

ceetheman
ceetheman

Reputation: 836

If you use "," Excel 2007 will read it, but not 2003. And if you use ";", its the other way around.

So, the best way is to generate a html table and output it as .xls. Excel 2007 will ask if its from a trusted source.

Here is a code example of how to do it:

private void ExportToXLSFromDataTable(DataTable dtExport, string filename)
{
    StringBuilder dataToExport = new StringBuilder();

    dataToExport.Append("<table>");
    dataToExport.Append("<tr>");

    foreach (DataColumn dCol in dtExport.Columns)
    {
        dataToExport.Append("<td>");
        dataToExport.Append(Server.HtmlEncode(dCol.ColumnName));
        dataToExport.Append("</td>");
    }

    dataToExport.Append("</tr>");

    foreach (DataRow dRow in dtExport.Rows)
    {
        dataToExport.Append("<tr>");
        foreach (object obj in dRow.ItemArray)
        {
            dataToExport.Append("<td>");
            dataToExport.Append(Server.HtmlEncode(obj.ToString()));
            dataToExport.Append("</td>");
        }
        dataToExport.Append("</tr>");
    }

    dataToExport.Append("</table>");

    if (!string.IsNullOrEmpty(dataToExport.ToString()))
    {
        Response.Clear();

        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

        HttpContext.Current.Response.Write(dataToExport.ToString());
        HttpContext.Current.Response.End();
    }
}

Upvotes: 0

Andrei Kozich
Andrei Kozich

Reputation:

You can try saving the file as .txt, not .csv, this forces Excel to parse text lines into columns

Upvotes: 0

GregUzelac
GregUzelac

Reputation: 472

Do you know if the CSV file has a byte-order mark header? Maybe it doesn't have a BOM, or its not the correct BOM for the locale.

Upvotes: 0

wakingrufus
wakingrufus

Reputation: 415

once it is imported, you can go to 'Tools' -> 'Text to Columns...' menu and specify a delimiting character.

Upvotes: 2

Eduardo Molteni
Eduardo Molteni

Reputation: 39453

As Chei noted, localizations or changes in the options of Excel (List separator character) may cause wrong behaviors.

I recommend you to use Open XML for a safer output.

Check out Generating an Excel file in ASP.NET

Upvotes: 1

Chei
Chei

Reputation: 2157

Different localizations of Excel might use different characters for separating columns. Try using ";" instead of "," to see whether it helps.

Upvotes: 0

JFV
JFV

Reputation: 1783

If you open the file via Excel's 'File --> Open' menu, then it will separate the values in the cells.

Upvotes: 0

Related Questions