Importing an Excel file with SqlBulkCopy fails

asp.net c# excel sqlbulkcopy

Question

SqlBulkCopy is working perfectly on my local machine, but with the same xlsx file on server i am getting

External table is not in the expected format.

or

No error message available, result code: E_FAIL(0x80004005).

This excel file has more than 20.000 rows. But if i try with 4000 rows, it is working.

My connection string is as below :

<add name = "Excel07+ConString" connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'"/>

Here is the main code

  string conString = string.Empty;
        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        conString = string.Format(conString, xlsFilePath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();
            dtExcelData.Columns.AddRange(new DataColumn[12] {
            new DataColumn("Class", typeof(string)),
            new DataColumn("Dia", typeof(double)),
            new DataColumn("Unit", typeof(int)),
            new DataColumn("Train", typeof(string)),
            new DataColumn("Seq", typeof(string)),
            new DataColumn("RevisionNo", typeof(int)),
            new DataColumn("Fluid", typeof(string)),
            new DataColumn("EngineeringTransCode", typeof(string)),
            new DataColumn("Insulation", typeof(string)),
            new DataColumn("PaintCode", typeof(string)),
            new DataColumn("Pid", typeof(int)),
            new DataColumn("RalCode", typeof(string))
            });
            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }

            excel_con.Close();
            excel_con.Dispose();
            string consString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.XlsWlbIsometric";

                    //[OPTIONAL]: Map the Excel columns with that of the database table
                    //sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                    sqlBulkCopy.ColumnMappings.Add("Class", "Class");
                    sqlBulkCopy.ColumnMappings.Add("Dia", "Dia");
                    sqlBulkCopy.ColumnMappings.Add("Unit", "Unit");
                    sqlBulkCopy.ColumnMappings.Add("Train", "Train");
                    sqlBulkCopy.ColumnMappings.Add("Seq", "Seq");
                    sqlBulkCopy.ColumnMappings.Add("RevisionNo", "RevisionNo");
                    sqlBulkCopy.ColumnMappings.Add("Fluid", "Fluid");
                    sqlBulkCopy.ColumnMappings.Add("EngineeringTransCode", "EngineeringTransCode");
                    sqlBulkCopy.ColumnMappings.Add("Insulation", "Insulation");
                    sqlBulkCopy.ColumnMappings.Add("PaintCode", "PaintCode");
                    sqlBulkCopy.ColumnMappings.Add("Pid", "Pid");
                    sqlBulkCopy.ColumnMappings.Add("RalCode", "RalCode");
                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    sqlBulkCopy.Close();
                    con.Close();
                    con.Dispose();
                }
            }


        }

And here is the exception that i received. Sorry but i can get this in string format.

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at GenWork.Areas.Xls.Controllers.XlsIsometricController.ParseImportedExcel(String xlsFilePath, Int32 xlsWlbIsometricMasterId)

Popular Answer

Without having more info, I assume the issue is that you are not batching the operation. SqlBulkCopy has a batch size property that you can set. You did not post any code so its not clear if you are setting it. If not try setting it to 5,000-10,000 and try again.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx

Or there could be an issue with data after the 4,000th row. If you are not doing so, wrap your SqlBulkCopy code in a try / catch to perhaps see if there are more details in the inner exception. Using a transaction is probably a good idea as well.

https://msdn.microsoft.com/en-us/library/tchktcdk(v=vs.110).aspx



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why