Unspecified Error when trying to read xlsx info via Microsoft.ACE.OLEDB driver

Question

I have an application which has been upgraded from Visual Studio 2010 (running on Windows Server 2003) to Visual Studio 2013 (now running on Windows Server 2008). One aspect of the app allows the user to upload an xlsx sheet to a folder, and a script validates its contents.

I have this method:

Private Function GetValuesFromExcel(ByVal strFileIn As String) As DataSet

    Dim ds As DataSet = New DataSet
    Dim strConn As String = ""
    Try

        If strFileIn.ToLower().EndsWith(".xlsx") Then
            'This one is good for files that are saved with Excel
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + strFileIn + "'; Extended Properties=Excel 12.0 Xml;"

        Else
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + strFileIn + "'; Extended Properties=Excel 8.0;"
        End If

        Dim conn = New OleDb.OleDbConnection(strConn)

        conn.Open()
        Dim dtExcelTables As DataTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

        Dim strExcel As String = "select * from [" + dtExcelTables.Rows(0)("TABLE_NAME").ToString() + "]"

        Dim myCommand = New OleDb.OleDbDataAdapter(strExcel, strConn)
        myCommand.TableMappings.Add("Table", "table1")
        'myCommand.Fill(ds, "table1")
        myCommand.Fill(ds)
        conn.Close()

    Catch ex As Exception
        DBUtils.WriteToLog("Error", "GetValuesFromExcel", ex.Message, Security.GetCurrentUser())
        Throw ex
    End Try

    Return ds

End Function

On conn.Open(), it throws an error. Specifically, the most excellent error of, "Unspecified Error". Very helpful.

We are using Office 2007, and I have checked to make sure that the 32-bit Access Database Engine redistributable is indeed installed.

What the heck is the problem?

Answer 1

I'll noodle about this a little bit, pretty unlikely you are going to get a "push this button to solve your problem" answer.

The error you are getting is E_FAIL, a generic COM error code. Commonly used by Microsoft software when it does not have a decent guess at the underlying reason for a failure. And guessing at a more specific one is too risky, sending their customers into a rabbit hole. This is a liability of COM in general, it does not directly support exceptions, only error codes. So a major feature that's lost is the stack trace, a cue that can give a lot of information about the specific layer in which the error was detected.

The only real way to deal with them is by a process of elimination. Which is feasible here, the code fails early. The only thing that you can do wrong is supplying the wrong connection string, providing bad data or running the code in the wrong execution context. Ticking off the common mistakes:

• Bad connection string syntax. Not your problem.
• Invalid path for the file. Not your problem, produces a good message
• File is not actually an Excel file. Not your problem, produces a good message
• Trying to run this in a 64-bit process. Not your problem, good message.

The not so common mistakes that are not easy to eliminate:

• Program runs as a service with an account that has accessibility problems. Eliminate that by testing it with a little console mode app.
• The file is an .xlsx file but it is subtly corrupted. Eliminate that by testing with a set of other .xlsx files.

Which does leave the most likely reason:

• The OleDb provider is not correctly installed. Classically also the common reason for Microsoft code giving up with a generic error like E_FAIL. Hard to diagnose of course, you might get somewhere by using SysInternals' Process Monitor and comparing a good trace with the bad trace. Good to discover a missing file or registry key. Do keep in mind that installing the 32-bit Access Database Engine redistributable after installing Office is not a good idea, it was meant to be used only on machines that don't have Office available. You'll have to spin the re-install wheel of fortune wheel at least once.

Perhaps the misery is also a good reason to give up on these providers. The EPPlus library is getting pretty good reviews, also scales quite well on a server.

Answer 2

I'm going to second Hans' idea of possibly giving up on these providers and looking into alternatives.

I recently went through a similar issue to that which you are facing. I developed a solution that worked well on my machine, but didn't on others due to them not having the requisite drivers installed.

My situation was a Winforms app that would be installed on clients machines and I would have no control over what external providers (ACE, JET etc) were installed. We also have no idea of what versions of Office they had installed. It was either we provide a convoluted solution that was capable of using whatever driver was installed...or look for alternatives.

We chose the latter, and went with Excel Data Reader. After about 30 mins, the solution now works without relying upon the configuration of the machine it is deployed to.

My code needed to simply read data from an Excel file (generated from an SSRS report) into an in memory DataTable for a bit of data comparison. The method we ended up with shows how simple it was to achieve;

        /// <summary>
        /// Read data from MS Excel saved as an export from their SSRS reports.
        /// This method uses ExcelDataReader <link>https://github.com/ExcelDataReader/ExcelDataReader</link>
        /// to avoid dependencies on OleDb Jet or ACE drivers. Given we can't control what is installed on 
        /// client machines, we can't assume they'll have the correct drivers installed, and therefore we'll
        /// make use of ExcelDataReader. 
        /// </summary>
        /// <param name="filename">Filename to the path of the Excel (xls or xlsx) file.</param>
        /// <returns>DataTable containing the required data or null if no data is found.</returns>
        private DataTable ReadDataFromUsingExcelReader(string filename)
        {
            DataTable returnval = null;
            DataSet result = null;

            using (FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.EndsWith("xls", StringComparison.OrdinalIgnoreCase))
                {
                    using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
                    {
                        result = excelReader.AsDataSet();
                    }
                }
                else if (filename.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase))
                {
                    using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
                    {
                        result = excelReader.AsDataSet();
                    }
                }
            }

            returnval = result != null && result.Tables[0] != null ? result.Tables[0].Copy() : null;

            return returnval;
        } 

Answer 3

Okay, so I figured out the issue as it pertains to my situation... I stuck with the ACE.OLEDB drivers, because I knew I could make them work in this scenario. Also, I figured it was something stupid and small. It was.

The xlsx sheet gets written into a folder called 'admin->excel_uploads'. Turns out that I had identity impersonate set to true in my config file. The service account I was running on did not have full read/write privs. That solved why it wasn't working locally, because as soon as I turned off impersonation, it worked great. Then on deploy, I just needed to set up permissions to the service account I was running under.