Exporting a Unicode .csv (comma separated) file to

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

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)

Answer 2

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

Answer 3

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 4

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

Answer 5

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

Answer 6

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.