Excel SqlBulkCopy works differently on local pc, and on application server?

c# excel oledbconnection sqlbulkcopy

Question

My application truncates a database table and fills it with the rows from an excel.

Using an excel file (2000 rows), below upload code inserts all the rows to database completely in my local pc (my development environment) and it did in the server too, so I thought that I succesfully accomplished the task,

But then a user added 10 new row to my excel and tried to upload 2010 rows but except newly added 10 rows the 2000 row was inserted, So using that excel file with 2010 rows, If I upload it from the server database table populated with 2000 rows, and if I upload it using my working environment final table is 2010 rows.

Server and local application is exactly the same. I tried the formatting etc too

Edit Example Execution: File MyRecords.xlsx is located in my desktop, now I connect to my application using

http://myserver/myapplication

and upload MyRecords.xlsx, then check count in database, it says 2361 records, then open visual studio and run my application (http://localhost:58029/) and upload MyRecords.xlsx and check database again it says 2362 records.

Edit Example Execution 2: My excel has 2160 lines, If I upload it, it inserts 2160 lines from both local and in server. If I remove 1000 thousand lines and upload it, now it inserts 1160 lines from local and from server as expected. Now If I re-add this 1000 lines and upload the excel, now if I did this process using local environment it works fine 2160 lines inserted, If server, 1160 lines inserted. So any modification on excel is not seen at the serverside, so are you sure this is the reason? –

    private void UploadData(string path, string dbTableName)
    {
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel

        excelConnection.Open();
        DataTable dbSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get First Sheet Name
        OleDbCommand cmd = new OleDbCommand("Select * from [" + dbSchema.Rows[0]["TABLE_NAME"].ToString() + "]", excelConnection);

        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);

        //Give your Destination table name
        sqlBulk.DestinationTableName = dbTableName;

        try
        {
            sqlBulk.WriteToServer(dReader);
            if(dbTableName == "TempTP")
            {
                SDatabaseManagerData.DatabaseManagerData.UpdateTP();
            }

            lbl_Error.Visible = true;
            lbl_Error.Text = "Database updated!";

        }
        catch (SqlException ex)
        {
            lbl_Error.Visible = true;
            lbl_Error.Text = "Database updated edilemedi! Hata: " + ex.Message;
        }

        excelConnection.Close();
    }

Popular Answer

This happens because of "typeguessrow" functionality of excel. Change registry set the typeguessrow from 8 to 0.

Process
1.type regedit.exe on command prompt and search for typeguessrow(press F3 to open search).
2. export registry as backup

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]
"DisabledExtensions"="!xls"
"ImportMixedTypes"="Text"
"FirstRowHasNames"=hex:01
"AppendBlankRows"=dword:00000001
"TypeGuessRows"=dword:00000008
"win32"="C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE12\ACEEXCL.DLL"

  1. change "TypeGuessRows"=dword:00000008 to "TypeGuessRows"=dword:00000000
  2. import registry


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