Unspecified Error thrown when opening connection to excel file using OLEDB

0

I am working on reading in excel files (both .xls and .xlsx) into my program which is being written into a data table. When running the code on one computer it works perfectly fine, when I attempt to run it on another computer I get the following exception:

System.Data.OleDb.OleDbException (0x80004005): Unspecified error at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

This exception is thrown when the program attempts to open the connection to the excel file to read the data in. I have installed 2010 Access Database Engine on the other computer but I still receive the same error when running the code. I was trying to get around the use of the Excel Interop services as it took too long to read in some of the file I am working with. I have done some searching around and have tried various different solutions such as different modifications to the connection string and changing with OLEDB driver I am using to open the connection to the file. This is the code I am using,

private static DataTable ReadExcelData(String path, String TableName)
    {
        string ConnectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

        DataTable dt = new DataTable(TableName);
        using (OleDbConnection conn = new OleDbConnection(ConnectionString))
        {
            conn.Open();

            DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String sheetName = table.Rows[0]["TABLE_NAME"].ToString();

            using (OleDbCommand comm = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", conn))
                using (OleDbDataAdapter da = new OleDbDataAdapter(comm))
                    da.Fill(dt);

            conn.Close();
        }

        return dt;
    }
c#
excel
oledb
oledbconnection
asked on Stack Overflow Dec 7, 2020 by HastingsN2

1 Answer

0

Figured out the issue, I had to installed the 2016 version of the driver and not the 2010 version which allowed it to work properly.

answered on Stack Overflow Dec 7, 2020 by HastingsN2 • edited Dec 7, 2020 by HastingsN2

User contributions licensed under CC BY-SA 3.0