locked
Error importing data from excel into SQL db using asp.net RRS feed

  • Question

  • User1016820894 posted

    I am importing data from an excel file into SQL using VS asp.net C# and microsoft.ace.oledb.12.0. When I run it from my local machine (under IIS Express) it works fine. But when I attempt to run it on our web server using an IIS App Pool I get the error "The 'microsoft.ace.oledb.12.0' provider is not registered on the local machine.". 

    string excelstring = "provider=microsoft.ace.oledb.12.0;data source=" + rptpath + filename +
                                      ";extended properties=" + "\"excel 12.0;hdr=yes;\"";

    The properties on the project are set as follows:

    Build Platform: Any CPU

    Web Bitness: Default

    I tried changing both to X64 but them I got a different error saying it couldn't load the project (on my local machine). 

    Also tried changing the setting on the app pool to 32 bit and did not get an error but it only partially imported the data on the spreadsheet (16250 rows out of 70000+).

    Please help.

    Thanks 

    Saturday, September 19, 2020 8:14 PM

All replies

  • User475983607 posted

    The server is missing the microsoft.ace.oledb.12.0 driver.  Download and install the driver.

    https://www.microsoft.com/en-us/download/details.aspx?id=13255

    Saturday, September 19, 2020 8:17 PM
  • User1535942433 posted

    Hi baldwinjohn,

    But when I attempt to run it on our web server using an IIS App Pool I get the error "The 'microsoft.ace.oledb.12.0' provider is not registered on the local machine.". 

    This error occurs when you try to make use of Excel 2007 or higher or Access 2007 or higher files using OLEDB connection in C# or VB.Net.As it requires use of OLEDB ACE drivers for connection which do not come by default in Windows.

    However,you still have problems.So,you could  check the architecture of the Visual Studio application.I guess your Visual Studio is a 32 BIT application.

    More details,you could refer to below article:

    https://www.aspsnippets.com/Articles/The-MicrosoftACEOLEDB120-provider-is-not-registered-on-the-local-machine.aspx

    Also tried changing the setting on the app pool to 32 bit and did not get an error but it only partially imported the data on the spreadsheet (16250 rows out of 70000+).

    As far as I think, your question might involve the how data is stored in Excel.Do you have numeric with a dash in excel?

    You could use the Data|Text To Columns option in Excel, not the cell formatting option to store the numeric value as text.The numerics with a dash remained as text.

    More details,you could refer to below article:

    https://stackoverflow.com/questions/15884934/data-import-from-excel-to-sql-server-failing-to-import-all-data

    Best regards,

    Yijing Sun

    Monday, September 21, 2020 2:19 AM
  • User1016820894 posted

    I discovered that the web server already has version 16.0 of  microsoft.ace.oledb. At least I see aceoledb.dll in the C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16 folder on the server. So I updated my code to microsoft.ace.oledb.16.0 but get the same error as before on the server. 

    Just because the dll is there, does that mean it's registered and how do I check that?

    Not sure where to go from here.

    Thanks. 

    Monday, September 21, 2020 7:06 PM
  • User475983607 posted

    You can look in the registry to see if the dll is registered.   You can also look in the system ODBC Data Source.  Another option is downloading and installing the driver. 

    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    Monday, September 21, 2020 7:26 PM
  • User1016820894 posted

    Yes I just checked and I do see that the driver is installed as version 16 under ODBC 32 bit. Could that be the issue?  Should it be 64? 

    Monday, September 21, 2020 7:34 PM
  • User475983607 posted

    Yes I just checked and I do see that the driver is installed as version 16 under ODBC 32 bit. Could that be the issue?  Should it be 64? 

    I'm not sure which driver you downloaded, want, or need.  Keep in mind, you are asking about Office ODBC drivers and this is an ASP support forum.

    Monday, September 21, 2020 7:46 PM
  • User1016820894 posted

    I went back to testing the app pool with 32 bit as true and again only got partial data in SQL. I don't think it has anything to do with the data because I took out the last row loaded into SQL and several after that and it still did not load everything. The spreadsheet has about 73,000 rows. 

    Monday, September 21, 2020 9:24 PM
  • User475983607 posted

    I went back to testing the app pool with 32 bit as true and again only got partial data in SQL. I don't think it has anything to do with the data because I took out the last row loaded into SQL and several after that and it still did not load everything. The spreadsheet has about 73,000 rows. 

    I'm a little confused.  You are no longer receiving the exception in your first post?  Now the problem is importing all 73,000 records?  Sounds like a bug.  Do you have any empty catch blocks or code that ignores exceptions?

    Monday, September 21, 2020 10:00 PM
  • User1016820894 posted

    Correct, I got pasty the Microsoft Ace exception by switching the app pool to enable 32  bit processing.

    As far as it not importing all of the data, I am not getting any exceptions. It processes through successfully just not all the rows are imported. Very strange. 

    Monday, September 21, 2020 10:12 PM
  • User475983607 posted

    baldwinjohn

    As far as it not importing all of the data, I am not getting any exceptions. It processes through successfully just not all the rows are imported. Very strange. 

    Unexpected results is a bug.  Share code that reproduces this issue if you want a code review.  Otherwise, find the records that are not imported and look for data issues.  You can also write to a log.

    Monday, September 21, 2020 11:19 PM
  • User1535942433 posted

    Hi baldwinjohn,

    As far as I think,your problem is your data.I suggest you could check your log and data wheather occur these issues:

    1.data's type in excel have something sql cann't discerned.

    2.data's length too long

    3.check sql type 

    You must check your  data that has not been imported.

    Best regards,

    Yijing Sun

    Tuesday, September 22, 2020 6:04 AM
  • User1016820894 posted

    I really don't think it's the data. If I run it from my local machine all the data is imported. 

    I also found the last row that was imported when running from the app pool. It looked like only 2 fields out of about 20 were populated. So I moved that row to the very top of the spreadsheet and re-imported. Now I see all the data populated in SQL for that row.

    It seems to be something specific to the app pool on the web server. 

    Tuesday, September 22, 2020 5:17 PM
  • User475983607 posted

    baldwinjohn

    It seems to be something specific to the app pool on the web server. 

    Like what?  The application pool skips records? That seems a bit far fetched.  How are you coming to this conclusion?

    The most likely cause is a bug in the code or design.   Is the file uploaded, save on a web server, then loaded in to SQL?  Are you fetching the data from the network stream?    

    Tuesday, September 22, 2020 5:30 PM
  • User1016820894 posted

    Code is below. Excel spreadsheet is located in the C:\temp folder on the server and imported from there. 

               string excelstring = "provider=microsoft.ace.oledb.16.0;data source=" + rptpath + filename +
                                      ";extended properties=" + "\"excel 12.0;hdr=yes;\"";
    
    
                try
                {
                    OleDbConnection oledbconnection = new OleDbConnection();
                    oledbconnection.ConnectionString = excelstring;
                    oledbconnection.Open();
    
                    DataTable dtSheet = oledbconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    foreach (DataRow drSheet in dtSheet.Rows)
                    {
                        sheetname = drSheet["TABLE_NAME"].ToString();
                    }
    
                    sqlstring = "SELECT * FROM [" + sheetname + "]";
    
                    OleDbCommand oledbcommand = new OleDbCommand(sqlstring, oledbconnection);
                    oledbcommand.CommandTimeout = 240;
                    DbDataReader dr = oledbcommand.ExecuteReader();
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connsql);
                    bulkInsert.DestinationTableName = tablename;
                    bulkInsert.BulkCopyTimeout = 240;
    
                    bulkInsert.WriteToServer(dr);
    
                    oledbconnection.Close();
    
                }
                catch (Exception Ex)
                {
                    ClientScript.RegisterStartupScript(GetType(), "showError", "alert('" + Ex.Message + "');", true);
                    errormessage = Ex.Message;
                    return "error";
                }



    Tuesday, September 22, 2020 6:08 PM
  • User753101303 posted

    Hi,

    A problem could be that Excel doesn't use a "column type "and so the db driver is guessing from the first 8 rows. If guessing wrong (for example having numeric values and then later a string in the same column) the value is ignored and will be blank.

    Try perhaps :
    - use IMEX=1 and/or a dummy row with string values at the top. This is to see if it seems better if Excel processes all this as string values
    - how many rows are used for guessing can be changed in the registry (maybe it could explain why you don't get the same behavior on your own machine ???)

    I'm ready to reconsider that but for this reason I prefer to import data using an Excel library that gives access to each cell value and type so that I can handle discrepancies as I want rather than using a db driver that can introduce this kind of quirks.

    Edit: try https://jingyangli.wordpress.com/2009/02/13/imex1-revisit-and-typeguessrows-setting-change-to-0-watch-for-performance/

    (Jet but should apply as well to ace.oledb)

    Edit 2: you have also https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver?tabs=office-2016 but it may apply to the ODBC Excel driver which should be installed as well. I'm not sure if using ace.oledb still uses the Excel ODBC driver behind the hood or yet something else built in the provider.. Seems still as messy than I remembered...

    Good luck ;-) !

    Tuesday, September 22, 2020 9:06 PM