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

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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 http://stackoverflow.com/questions/150339/generating-an-excel-file-in-aspnet#150448

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

Answer 8

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.