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, Mar 31 '16 at 14:07
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, Mar 31 '16 at 14:08
As [this duplicate question](http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format) 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, Mar 31 '16 at 14:12

William Xifaras · Answer 1 · 2016-03-30T16:35:20.617

0

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

edited Mar 30 '16 at 16:35

answered Mar 30 '16 at 16:23

William Xifaras

5,212
2
19
21

Hello, thanks for your reply. It seems that BatchSize is not set. I have added my code. – umki Mar 31 '16 at 14:05
Batching or lack of it wouldn't cause an error, especially for such a tiny number of rows. Besides, the error complains about the *Excel* file, not SQL Server – Panagiotis Kanavos Mar 31 '16 at 14:10
Agreed, though the original post didn't have enough context. Hence why I suggested looking deeper into the exception. – William Xifaras Mar 31 '16 at 14:19
@PanagiotisKanavos, same excel file is working on local. But i am going to take exception error. – umki Mar 31 '16 at 14:42
@umki that doesn't mean much. You may have a *different* driver version on your machine. Or there *could* be a problem with the data - Excel **doesn't** have types so the driver tries to *guess* the types of columns based on the values of the first X rows. If row 4001 contains text when all previous rows contained numbers, it will fail. In this case you could add `;IMEX=1` to the Extended Properties field. If that's the case, the exception should occur while reading individual rows. Wrong driver, format etc would occur earlier. – Panagiotis Kanavos Mar 31 '16 at 14:50
I have added the exception that i received. How can i check the excel drivers ? – umki Mar 31 '16 at 20:26

Importing an Excel file with SqlBulkCopy fails

1 Answers1