How to Fix ‘External Table Is Not in the Expected Format’ in Microsoft Excel

Importing data sets into Microsoft Excel, querying workbooks through Power Query, or connecting database applications using Microsoft OLE DB or ODBC drivers should allow smooth data integration. However, attempting to establish a connection or open an imported spreadsheet only to be halted by the error: “External table is not in the expected format” brings data extraction and reporting pipelines to a complete stop. In Microsoft Excel and Microsoft Access Database Engine (ACE.OLEDB), this diagnostic error indicates that the database driver attempted to parse a file according to a specific schema (such as Excel 97-2003 .xls or OpenXML .xlsx), but the physical file structure does not match the driver's expected binary headers. This commonly occurs when HTML tables or CSV files are saved with an .xlsx extension, or when connection strings specify incorrect Extended Properties. This technical guide outlines how Excel OLE DB parsing works and provides proven methods to resolve format conflicts.

What Causes ‘External Table Is Not in the Expected Format’?

The Microsoft Access Database Engine reads the magic bytes in file headers to determine how to decompress and parse tabular data. Common triggers for this error include:

  • File Extension Mismatch: A file generated by an enterprise web application (such as SAP, Salesforce, or QuickBooks) formatted internally as an HTML table or comma-separated CSV but given a .xlsx or .xls file extension.
  • Incorrect OLE DB Connection String Extended Properties: Specifying Extended Properties="Excel 8.0" for a modern .xlsx OpenXML file, or specifying Extended Properties="Excel 12.0 XML" for a legacy binary .xls workbook.
  • Password-Protected Workbooks: Attempting to query an encrypted or password-protected Excel file via OLE DB or Power Query without providing decryption keys.
  • Missing or Outdated Microsoft Access Database Engine Redistributable: Systems lacking the updated 64-bit Access Database Engine (ACE) drivers required to parse newer OpenXML spreadsheet formats.
  • Corrupted Workbook XML Streams: Damaged zip containers or broken XML manifests within the .xlsx package.

Method 1: Verify the Genuine Internal File Format

Determining whether the file is truly an OpenXML spreadsheet or an HTML table disguised as an Excel workbook resolves the mystery immediately.

  1. Locate the problematic file in Windows File Explorer.
  2. Right-click the file and select Open with > Notepad (or a text editor like VS Code or Notepad++).
  3. Examine the very first few characters at the top of the file:
    • If the file begins with PK..., it is a genuine OpenXML zip archive (true .xlsx).
    • If it begins with <html>, <table>, or <?xml, the file is a web page or XML table masquerading as an Excel workbook.
    • If it begins with plain text separated by commas or tabs, it is a CSV or TSV file.
  4. If the file contains HTML tags, rename the file extension from .xlsx to .html, open it in a web browser, select and copy the table, and paste it into a clean Excel sheet.
  5. If it contains delimited text, rename the extension to .csv and import it using Excel's standard text import wizard.

Method 2: Correct OLE DB / ODBC Connection String Extended Properties

If this error occurs within a C#, VB.NET, Python, or SSIS integration script, ensure your connection string matches the target file format exactly.

  1. For modern OpenXML workbooks (.xlsx), use the ACE.OLEDB.12.0 (or 16.0) provider with Excel 12.0 XML:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\file.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
  2. For macro-enabled workbooks (.xlsm):
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";
  3. For legacy binary workbooks (.xls 97-2003):
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\file.xls;Extended Properties="Excel 8.0;HDR=YES";
  4. For Excel binary workbooks (.xlsb):
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\file.xlsb;Extended Properties="Excel 12.0;HDR=YES";

Method 3: Install the Microsoft Access Database Engine Redistributable

Systems running custom automated scripts or reporting tools often require the standalone ACE engine.

  1. Visit the official Microsoft Download Centre.
  2. Search for and download the Microsoft Access Database Engine Redistributable (choose the 64-bit version AccessDatabaseEngine_X64.exe if your operating system and Office install are 64-bit).
  3. Run the installer with administrative privileges.
  4. Restart your computer to register the updated OLE DB and ODBC drivers in the Windows Registry.

Method 4: Remove Password Encryption from the Source Workbook

Standard OLE DB providers cannot parse encrypted Excel files directly without prior decryption.

  1. Open the source Excel file manually in Microsoft Excel.
  2. Enter the password when prompted.
  3. Navigate to File > Info > Protect Workbook.
  4. Click Encrypt with Password.
  5. Clear the password field completely and click OK.
  6. Save the unencrypted workbook and re-run your automated connection or import script.

Method 5: Re-Save the File via Excel Open and Repair

If minor XML schema corruption is present inside the archive, Excel's repair engine can reconstruct valid tables.

  1. Launch Microsoft Excel.
  2. Click File > Open > Browse.
  3. Select the problematic workbook.
  4. Click the small arrow next to the Open button in the bottom-right corner of the dialogue box.
  5. Select Open and Repair…
  6. Click Repair to recover data structures.
  7. Once opened, save the file under a new name and retry the connection.

For more spreadsheet integration guides and formula troubleshooting advice, visit our Digitash Excel category.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.