Importing an Excel file with SqlBulkCopy fails

asp.net c# excel sqlbulkcopy

Question

SqlBulkCopy works well on my local computer, however when I use the same xlsx file on the server, I receive

External table is not in the expected format.

or

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

There are almost 20.000 rows in this excel document. However, if I try with 4,000 rows, it succeeds.

Below is my connection string:

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

This is the primary 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 this is the exemption I was given. My apologies, but I can only obtain 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)

1
0
3/31/2016 8:26:01 PM

Popular Answer

I guess the problem is that you are not batching the action without additional information. You may modify the batch size attribute of SqlBulkCopy. It's unclear if you are setting it since you didn't upload any code. If not, change the value to between 5,000 and 10,000 and try again.

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

Or maybe there is a problem with the data after the 4,000th row. Wrap your SqlBulkCopy method in a try/catch block if you aren't already doing so to determine whether the inner exception has any further information. It's probably a good idea to use a transaction as well.

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

0
3/30/2016 4:35:20 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow