GetOleDbSchemaTable - Can get sheet name with .xlsx but not with .xls using Microsoft.ACE.OLEDB.12.0

Question

I'm updating BizTalk 2016 to BizTalk 2020 on entirely new servers, and have installed the Access OleDB provider. It seems to work with the .xlsx file, but for the .xls file, it's not finding the worksheet name dynamically. The actual routine is in a C# class library called from BizTalk, but to help debug I created the following test method so I can test it outside of BizTalk in a simple console app. I got passed the issues of the conn.open() and the library not being registered.

I think the .xls is not loading at all. If I use the hard-coded sheet name, it gives this error: "The Microsoft Access database engine could not find the object 'Sheet1$'", even though in Excel, that's the sheetname (with the $ sign added).

The existing code works on our existing system and uses what I show as connectionStringOld below. If I try that version on the .xls file it gets the "not registered" error.

Do I have to use an older version of the provider for .xls files?

static void testAccessOleDBAdapterForExcel()
{
    string methodPrefix = "method: testAccessOleDBAdapterForExcel  - ";

    string excelFilename = null;
    string worksheetName = null;

    int fileNumber = 1;  //switch between two files and corresponding sheet name 

    if (fileNumber == 1)
    {
        excelFilename = @"\\Share1\SampleExcelFiles\PublishPowerMeterData_-15_05012023060600_2023-05-01T06-04-02.xls";
        worksheetName = "Sheet1";
    }
    else
    {
        excelFilename = @"\\Share1\SampleExcelFiles\PublishForwardPrice_powerfolio_extract_05_01_2023_08_00_2023-05-01T14-56-04.xlsx"; 
        worksheetName = "South";
    }

    // We have to add  on the end of the actual Worksheet 
    worksheetName = worksheetName + "$"; 


    // original code on BT20106 used ACE.OLEDB.10.0 
    //var connectionStringOld = @"Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + excelFilename + ";Extended Properties=\"Excel 10.0;HDR=YES\"";
    var connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilename + ";Extended Properties=\"Excel 12.0;HDR=YES\"";

    Console.WriteLine(methodPrefix + "FileName=" + excelFilename);
    Console.WriteLine(methodPrefix + "attempt spreadsheet read with Access OleDB Adapter");
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        Console.WriteLine(methodPrefix + "Conn.Open() succeeded");

        var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        // The following if statement throws the ArgumentException with the .xls 
        if (schemaTable.Rows.Count < 1) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
        string dynWorksheet = schemaTable.Rows[0]["table_name"].ToString().Replace("'", "");

        Console.WriteLine(methodPrefix + "dynWorksheet = " + dynWorksheet);

        OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
        ("select * from [" + dynWorksheet + "]", conn);
        DataSet excelDataSet = new DataSet();
        objDA.Fill(excelDataSet);
        Console.WriteLine("method: testAccessOleDBAdapterForExcel  - was able to fill dataset object"); 
    }
    
}

In debug:

Using Visual Studio 2019 (to be compatible with BizTalk 2020), .NET Framework 4.7.2, and these build options:

Got same issue trying "Excel 8.0;HDR=Yes" as per this: https://www.connectionstrings.com/ace-oledb-12-0/excel-97-2003-xls-files-with-ace-oledb-120/

Answer 1

It turns out that the file that that ended with .xls was not the correct one. It had no headings and no data, and an odd sheet name. When I tested with the correct .xls file it, worked fine.

I changed the error to be more meaningful, from this:

   if (schemaTable.Rows.Count < 1) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");

to this:

 if (schemaTable.Rows.Count < 1) throw new ArgumentException(
                        "GetOleDbSchemaTable returned schemaTable.Rows.Count<0, so could not dynamically find the excel sheetname.  " + 
                        "Potentially invalid Excel worksheet, or missing data and/or headers.");

Also, since running under BizTalk, it can give odd errors and not catch the SEHException (in managed code), so I beefed up the exception messages as well:

    catch (Exception ex)
    {
        // We had this issue when running in 32-bit instead of 64-bit on BizTalk 2020 install/testing 

        string messageIdentifier = "SystemException in MapExcel_To_Xml_v0100: ";
        string errorMessage = messageIdentifier + " ex.Message=" + ex.Message;

        string specialError = "External component has thrown an exception.";
        if (ex.Message.Contains(specialError))
        {
            errorMessage = messageIdentifier + "This error might be an OLDEDB Conn.Open SEHException which .NET cannot catch correctly, " +
                " and might be related to running in the 64-bit vs 32-bit mode.  " + 
                " ConnectionString=" + connectionString + 
                " ex.errorMessage=" + ex.Message; 
        }

        System.Diagnostics.EventLog.WriteEntry(strClassName + "." + strMethodName, errorMessage, System.Diagnostics.EventLogEntryType.Error);

        throw ex;
    }