Could not able to open OleDb Connection

Nepethya Rana

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 and I have tried all of these settings:

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 + "]";

// query = "Select [s-ip] from [" + sTableName + "] where [s-ip] like '%" + kw + "%' Order by [s-ip]";

}

else

{

query = "Select [" + selectColumn + "] from [" + sTableName + "]Order by [" + selectColumn + "]";

// query = "Select [cs-uri-stem] from [" + sTableName + "]Order by [cs-uri-stem]";

// query = "Select [s-ip] from [" + sTableName + "]Order by [s-ip]";

}

OleDbDataAdapter da = new OleDbDataAdapter(query, xlConn);

da.AcceptChangesDuringFill = true;

da.Fill(dt);

da.Dispose();

}

catch (Exception ex)

{

WriteError(ex);

}

finally

{

xlConn.Close();

}

return dt;

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nepethya Rana9y ago
This is what I have realized. Since my local machine is 64bit, I have 64bit office, access db engine is 64 bit. So I changed my application property and set it to 64bit only and it worked. And I my extended properties is : 12.0 Xml; HDR=Yes; IMEX=1. I hope this may help someone in future. Thank you guys for coming out and helping me. Thank you very much.
0
Manas Mohapatra9y ago
I think you need to change in Connection string or install drivers. Please follow below links:

https://www.codeproject.com/Questions/537510/Externalplustableplusisplusnotplusinplustheplusexp

https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format
0
Amit Gupta9y ago
Hey

Try using IMEX =1 in your c9nnection string
Eg.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
* Try Repairing the oledb driver.

Good Luck !!
0
Nepethya Rana9y ago
Most of the time I face this error : external table is not in the expected format excel 2016.

I am bulding up console appliation. I
0
Manas Mohapatra9y ago
Follow below link that may help you:

https://www.mikesdotnetting.com/article/280/solved-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine
0