Diagnosing OLEDB Exception with Quering Excel 2010
To query the Excel sheet via SQL, I used either:
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""
or
Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"""
Now it worked until I installed Office 2010.
Now I get
The Microsoft.Ace.OLEDB.12.0 provider is not registered on this machine for an exception.
How do I find out the correct connection / provider string?
+2
source to share
3 answers
Did you uninstall the Access Database Engine (ACE)? They are still available for download from MSDN as 2007 Office System Driver: Data Connectivity Components .
+1
source to share
I believe for Excel 2010 this is:
Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\\MyDocs\\oledb.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:Engine Type=37"
This seems to work in my visual studio, I got Excel to generate the query string and it had an extra entry.
+7
source to share
I downloaded and installed the Office System Driver: Data Connectivity Components as above and the code below:
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Password=\"\";User ID=Admin;Data Source=d:\\Sample.xlsx;Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";
OleDbConnection connection = new OleDbConnection(connectionString);
try
{
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception)
{
//throw;
}
finally
{
connection.Close();
}
+2
source to share