External table is not in the expected format OLEDB 12.0 with Excel 2007

Question

I have developed a WEB API service which will read data from excel file when user upload.

I use OLEDB as following:

if (Path.GetExtension(filePath).ToUpper() == ".XLS")
{
    oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"");
}
else
{
    oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
}
oledbConn.Open();  //Exception thrown at here
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
cmd.Connection = oledbConn;
cmd.CommandText = "SELECT DISTINCT(["+mo_field+"]),["+model_field+"],["+content_filed+"] FROM ["+ecn_field+"] WHERE ["+mo_field+"] IS NOT NULL AND ["+active_field+"] ='1'";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds,"NewMO");

but it thrown exception:

External table is not in the expected format

My server is installed Window Server 2012 RC2 64 bit so I have tried install Microsoft Database Engine 2010 redistribute 32bit/64bit. And Microsoft Database Engine 2007 32 bit. But it still does not work. I searched for 3 days, and every posts said that install Microsoft Database Engine will fix the error. This code work well with Office 2010/2013.

Thank you so much for your help!

Answer 1

var ds = new DataSet();
var da = new OleDbDataAdapter("SELECT DISTINCT(["+mo_field+"]),["+model_field+"],["+content_filed+"] FROM ["+ecn_field+"] WHERE ["+mo_field+"] IS NOT NULL AND ["+active_field+"] ='1'", 
                              "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
da.Fill(ds,"NewMO");

and make sure that the file is really an Excel file by opening it in Excel.

Update

Here are few more connection strings from https://www.connectionstrings.com/excel/ to try

foreach (var cs in new string[] {
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES';",
    "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR = Yes; IMEX = 1';" })
    try { using (var con = new System.Data.OleDb.OleDbConnection(cs)) con.Open(); MessageBox.Show(cs + " worked bro!!"); } catch { }

foreach (var cs in new string[] {
    "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + filePath + ";",
    "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + filePath + ";",
    "Driver={Microsoft Excel Driver (*.xls)};Dbq=" + filePath + ";ReadOnly=0;"})
    try { using (var con = new System.Data.Odbc.OdbcConnection(cs)) con.Open(); MessageBox.Show(cs + " worked bro!!"); } catch { }