Exporting Data Table to Excel using OLEDB throws too many fields defined error

Question

We have a data table with 2500 columns and 2000 rows. When we try to export using OLEDB i am getting an error "too many fields defined". I cant use Excel Inter op, since it consumes more time. Is there any other way to fix this issue

using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties=" + "\"" + "Excel 8.0;HDR=NO;" + "\""))
                {
                    con.Open();
                    OleDbCommand cmdInsert;
                    if (createTable || !File.Exists(fileName))
                    {
                        sql = "CREATE TABLE " + tableName + " (";
                        for (int i = 0; i < tbl.Columns.Count; i++)
                        {
                            sql += "[" + tbl.Columns[i].ColumnName + "]";
                            if (i + 1 == tbl.Columns.Count) //Here we decide should we close insert command or appebd another create column command
                                sql += " " + GetColumnType(tbl.Columns[i]) + ")"; //Close insert
                            else
                                sql += " " + GetColumnType(tbl.Columns[i]) + ","; //there is more columns to add
                        }
                    }
                    if (!String.IsNullOrEmpty(sql))
                    {
                        cmdInsert = new OleDbCommand(sql, con);
                        cmdInsert.ExecuteNonQuery();
                    }
                    foreach (DataRow row in tbl.Rows)
                    {
                        //Dodati parametre na comandu
                        string values = "(";
                        for (int i = 0; i < tbl.Columns.Count; i++)
                        {
                            if (i + 1 == tbl.Columns.Count)
                            {
                                 if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ")";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";

                                //values += "'" + "test" + "')";
                            }
                            else
                            {
                                if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ",";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";

                                //values += "'" + "test" + "',";
                            }
                        }
                        string sqlInsert = String.Format("Insert into [{0}$] VALUES {1}", tableName, values);
                        cmdInsert = new OleDbCommand(sqlInsert, con);

                        cmdInsert.ExecuteNonQuery();
                    }
                }

Answer 1

I found out that the limitation of using oledb is 255 columns. Hence i am converting the datatable into csv using the below code and write it into response of the http page

public void CreateCSVFile(System.Data.DataTable dt, out StringWriter stw)
        {
            stw = new StringWriter();
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                stw.Write(dt.Columns[i]);
                if (i < iColCount - 1)
                {
                    stw.Write(",");
                }
            }
            stw.Write(stw.NewLine);
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        stw.Write(dr[i].ToString());
                    }
                    if (i < iColCount - 1)
                    {
                        stw.Write(",");
                    }
                }
                stw.Write(stw.NewLine);
            }
        }

now i am able to fix the issue of exporting huge data

Answer 2

Does Excel (which you are connecting too) have a limit of columns it supports via a "table" definition? that might be your bottleneck.

What I would suggest, is when building your table out, you do it for all the columns. I would suggest running it in batch cycles. Call the function and do 100 columns at a time and export only 2 or 3 rows. So, it may take 20 loop cycles to build out all 2000 columns, but you might find your break there. If you DO get to the full 2000 columns, then expand the rows being exported and find out at what ROW inserted it fails. It could be there are some NULL values floating around killing your process.