Clever Geek Handbook

Exporting a Unicode CSV (Comma Separated) file to Excel 2003 or Excel 2007 causes all columns to end in the first Excel column

Is there anything we can do, either in code (ASP / JavaScript) or Excel, to make comma separated values ​​go to separate columns in Excel?

+1


source to share


9 replies


after importing it, you can go to the "Tools" → "Text to columns ..." menu and specify the separator character.



+2


source


As Whose pointed out, localizations or changes in Excel options (list separator character) may cause incorrect behavior.

I recommend using Open XML for safer output.



Check out Create an Excel File in ASP.NET

+1


source


I just tried using tabs for field separators instead of commas and it worked in Excel 2007

(at least I think this is 2007, can't find Help / About in the silly feed)

+1


source


If you open the file via the File → Open menu in Excel, it will split the values ​​in the cells.

0


source


Different Excel localizations can use different symbols to separate columns. Try using ";" instead of "," to see if that helps.

0


source


Do you know if the CSV file has a byte stamp header? It may not have a BOM or its incorrect specification for the locale.

0


source


You can try saving the file as .txt rather than .csv, this makes Excel parse text strings into columns

0


source


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

So, the best way is to create an html table and output it as .xls. Excel 2007 will ask if it has a reliable source.

Here's a sample code on 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();
    }
}
0


source


Excel seems to be confused about the UTF-16 / UTF-8 byte order bytes, so try to get rid of them.

With CSV, the contents of cells can be a unicode character, but the separator, quote and newline characters must always be ASCII. You can think of CSV as ASCII as always, but each cell is a block of binary code and can be unicode text.

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

0


source






More articles:


All Articles