Importing an Excel file with SqlBulkCopy fails

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)

Where does the exception occur? Why do you think it has to do with the number of rows? You tried with two different files and the message complains abou thte file format. Please post the full exception, including its call stack. You can get it simply with exception.ToString() — Panagiotis Kanavos
BTW that's a tiny number of rows, even for an Excel sheet. What is the file's format and how was it produced? Is it a real Excel file or some CSV/HTML with an Excel extension generated from an application? — Panagiotis Kanavos
As this duplicate question shows, this error occurs when trying to load an Excel file using the wrong version number. Are you trying to load an xls file? — Panagiotis Kanavos

William Xifaras William Xifaras · Accepted Answer · 2016-03-30T16:23:31

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

Importing an Excel file with SqlBulkCopy fails

1 Answers