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;
}
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.
User contributions licensed under CC BY-SA 3.0