Excel "External Table Is Not in the Expected Format."

Excel External table is not in the expected format.

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

Using the following connection string seems to fix most problems.

public static string path = @"C:\src\RedirectApplication\RedirectApplication\301s.xlsx";
public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

ACE OLEDB External table is not in the expected format with large text in Excel cell

You seem to have encountered an issue with the Access Database Engine ("ACE") handling of older .xls files. I could reproduce the issue using

myConnectionString =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Public\test\sample.xls;" +
        "Extended Properties=\"Excel 8.0;HDR=YES;\";" +
        "";

but no error occurred when I simply switched to the older "Jet" OLEDB provider (32-bit) ...

myConnectionString =
        "Provider=Microsoft.Jet.OLEDB.4.0;" +
        @"Data Source=C:\Users\Public\test\sample.xls;" +
        "Extended Properties=\"Excel 8.0;HDR=YES;\";" +
        "";

... and it did read all 8927 characters in the "Legal" column (i.e., it did not truncate it to 255 characters).

If you really did need to use the ACE OLEDB provider then I found that saving the .xls file as .xlsx and using

myConnectionString =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Public\test\sample2.xlsx;" +
        "Extended Properties=\"Excel 12.0;HDR=YES;\";" +
        "";

also worked. (The re-save could presumably be accomplished using COM automation of Excel from within the C# application.)

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

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 { }

Getting External table is not in the expected format. error while trying to import an Excel File in SSIS

The main error is:

External table is not in the expected format

This happens when the Excel file is corrupted and cannot be opened by Access Database Engine (OLE DB provider) even if you can open the file from Excel. In a similar case opening the file manually and saving it as a new file may do the trick.

---

Related Topics