I Could not able to open OleDbConnection, I get exception when code calls Open() method. What i am trying to do is load from from excel to Data table.
My local machine has Office 2016 (64), I have installed AccessDatabaseEngine_X64,
Sometime I get error as : external table is not in the expected format excel 2016.
And Sometime I get error as : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
This is my code :
private DataTable ExcelToDataTable(string dataSource, string selectColumn, string keyword)
{
string provider = OleDBProvider;
string ds = dataSource;
string extendedProperties = ExtendedProperties;
string cs = String.Format("Provider={0}; Data Source={1}; Extended Properties={2};", provider, ds, extendedProperties);
var xlConn = new OleDbConnection(cs);
DataTable dtXlSchema;
dt = new DataTable("ReviewTable");
try
{
xlConn.Open();
dtXlSchema = xlConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
for (int i = 0; i < dtXlSchema.Rows.Count; i++)
{
string sTableName = dtXlSchema.Rows[i]["Table_Name"].ToString();
string query = "";
if (!String.IsNullOrWhiteSpace(keyword))
{
query = "Select [" + selectColumn + "] from [" + sTableName + "] where [" + selectColumn + "] like '%" + keyword + "%' Order by [" + selectColumn + "]";
}
else
{
query = "Select [" + selectColumn + "] from [" + sTableName + "]Order by [" + selectColumn + "]";
}
OleDbDataAdapter da = new OleDbDataAdapter(query, xlConn);
da.AcceptChangesDuringFill = true;
da.Fill(dt);
da.Dispose();
}
}
catch (Exception ex)
{
WriteError(ex);
}
finally
{
xlConn.Close();
}
return dt;
}
What I have tried:
I have tried all of these settings.
<appSettings>
<add key="OleDBProvider" value="Microsoft.ACE.OLEDB.12.0"/>
<!--<add key="ExtendedProperties" value="'Excel 8.0;HDR=Yes;IMEX=1'"/>-->
<!--<add key="ExtendedProperties" value="'Excel 12.0;HDR=Yes;'"/>-->
<add key="ExtendedProperties" value="'Excel 12.0 Xml;HDR=Yes;'"/>
</appSettings>