locked
Error while reading the Excel files. RRS feed

  • Question

  • User416130201 posted

    We have a web application (asp.net) which reads data from excel sheets. our application just needs to identify the list of sheet names in the excel(to show these sheet names to the user in the front end to select) and the data will be extracted by the back end ssis packages into sql server. for this we are using Oledbconnection as shown below which is working fine in the local machine where MS Office is installed. However once deployed in the server it is not working we are getting different errors.

    for .Xls file selection the error is: Exception.system.data.oledb.oledbexception (0x80004005): could not find installable ISAM.

    for .Xlsx file selection the error is: microsoft.oledb.12.0 provider is not registered on the local machine. The package 32-bit mode 'Microsoft Access Database Engine 2010 Redistributable' is installed on the server already.

    code and web.config details are shown below:

    switch (ext)
                     {
                         case ".xls": //Excel 97-03
                             conStr = ConfigurationManager.ConnectionStrings["excel03Connection"].ConnectionString;
                             break;
                         case ".xlsx": //Excel 07
                             conStr = ConfigurationManager.ConnectionStrings["excel07Connection"].ConnectionString;
                             break;
                     }
    
                     conStr = String.Format(conStr, path);
                     OleDbConnection connExcel = new OleDbConnection(conStr);
                     using (connExcel)
                     {
                         OleDbCommand cmdExcel = new OleDbCommand();
                         OleDbDataAdapter oda = new OleDbDataAdapter();
                         cmdExcel.Connection = connExcel;
                         connExcel.Open();
                         //Get the Sheets in Excel WorkBook
                         var dt = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                         int count = dt.Rows.Count;
                         for (int i = 0; i < count; i++)
                         {
                             DataRow dr = dt.Rows[i];
                             if (dr["TABLE_NAME"] != null && dr["TABLE_NAME"] != "")
                             {
                                 sheetnames.Add(dr["TABLE_NAME"].ToString());
                             }
                         }
                     }
        <add name="excel03Connection" providerName="Microsoft.Jet.OLEDB.4.0;" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text&quot;"/>
        <add name="excel07Connection" providerName="Microsoft.ACE.OLEDB.12.0;" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 12.0;HDR=YES;IMEX=1;ImportMixedTypes=Text&quot;"/>
    


    Wednesday, January 15, 2014 9:37 AM

Answers

  • User-933407369 posted

    hi saicharan_k,

    Cause:

    ISAM drivers are used by Access to update file formats other than its own. If the path of the ISAM driver in your Windows Registry is not valid, or if the ISAM driver does not exist, the problem may occur.

    Resolution:

    Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs

    Please refer to the links  below for details:

    http://support.microsoft.com/kb/209805/en-us

    you can aslo check out the demo:

    Read and Import Excel Sheet into ASP.Net GridView Control

    Hope they can help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 16, 2014 2:42 AM