5 Ways To Fix Excel External Table Is Not In The Expected Format Error

Frequently encountering the same external table is not in the expected format error each time you try connecting to Excel or while importing an Excel source?

Don’t have any idea why you are getting this external table is not in the expected format error or how to fix this?

No need to get worried as our today’s blog topic is specifically about Excel external table format error.

User’s Query:

For understanding this specific error more clearly you can read out this user query.

I am using Excel as a database. While connecting to the database I’m getting the error “External table is not in the expected format”.

Same connection string is working fine in the code before. The only difference is there in the data source. I’m getting the error when the source file is password protected.

Below is my connection string-

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$AAApplicationPath$\Automation Anywhere\My Tasks\Finance\Fixed Asset – Tracking Process1\Input\export.XLSX;Extended Properties=”Excel 12.0 Xml;HDR=YES”;

I wanted to know if there is any way to connect to Excel with a password in the connection string.

What’s This External Table Is Not In The Expected Format Error In Excel?

An external table is not in the expected format error in Excel is one such error that usually occurs when connecting to the MS Excel data source.

An error occurred while communicating with the data source.
Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.
Database error 0x80004005: External table is not in the expected format. 

Under What Circumstances Excel External Table Is Not In The Expected Format Error Occur?

Mostly this Excel external table is not in the expected format error encounters meanwhile importing the Excel source or to some target definition. At that time you will get the following error.

“[Microsoft][ODBC Excel Driver] External table is not in the expected format” 

​This issue also originates when you are using the wrong ODBC driver for importing Excel files that get saved with the .xlsx extension.

Why Do I Get Error ‘External Table Is Not In The Expected Format’ ?

 Following are the possible causes behind the occurrence of this ‘external table is not in the expected format’ Excel error

• Maybe you are not using a valid Excel workbook.
• Your Excel file is somehow got corrupted.
• The Excel version is not well supported. Supported versions are: Excel 3.0-2013.
  
• Your Excel file is encrypted with a password and the database doesn’t support password-protected files.

Helpful Article: Top 3 Methods To Unlock Password Protected Excel File

How To Resolve Excel File Import Error ‘External Table Is Not In The Expected Format’?

Method 1# Install Microsoft.ACE.OLEDB.12.0 Drivers.

If your Excel file which you need to import has the ‘.xlsx’ extension. Then Excel drivers are required for Excel version 2007 and onwards.

Suppose you need to import Excel file having extension .xlsx then you need to use the Microsoft Excel Driver (*.xls).

So it’s recommended to install new Microsoft.ACE.OLEDB.12.0 drivers.

Method 2# Check The ODBC Definition

To fix this Excel External table is not in the expected format error, check for the ODBC definition within the ODBC Manager. This is to verify whether the right driver is been used or not as per the file extension:

Method 3# Enable The Data Connection

Sometimes this problem of the external table is not in expected format error also occurs due to the disabled data connection option.

For enabling this data connection on a temporary basis, simply tap the “Enable Editing” or” Options” button.

If you wish to open only the safe Excel data files and needs to automatically enable the content. Then follow the below-mentioned steps:

• Activate MS Excel, tap the File option from the menu.
• Hit the Options>Trust Center>Trust Center Settings.

• On the other hand, choose the External Content After that tap on the “Enable all Data Connections (not recommended)” option.

• Hit the OK After that take the exit from the application and reopen your Excel spreadsheet.
• You won’t see the “Data Connections have been disabled” message on your screen after making these steps.

Method 4# Use The External Data Range

The external data range is a defined table name or any name which describes the data location from where the data is brought into the worksheet.

When any connection is made with the external data, the Excel application automatically generates an external data range.

The only exception to this is the PivotTable report which gets connected to the data source.

As the PivotTable report doesn’t make use of the external data range.  Well, you can do the formatting, use it in calculations, or set layout to the external data range.

External Data Range Properties

External data range has some additional properties (don’t get confused with the connection properties) which you can easily use for controlling up the data, like for preserving the column width and cell formatting.

For changing the properties of the external data range tap the following options: Data> Connections group> Properties. Follow the step to make the changes.

• For changing the external data ranges which are created using the Data Connection Wizard and MS Query. You need to use the dialog box of External Data Properties.

• To change the external data ranges created using Web query or an imported text file that retrieves HTML data. You need to go with the External Data Range Properties dialog box.

• For making changes in the external data ranges, which gets created from the Web query. You need to use the dialog box of XML Map Properties.

Method 5# Use Excel Repair Tool

Excel shows the external table is not in expected format error also when the file gets corrupt. If this is the case then I always recommended you all to use the most reliable solution to repair corrupt Excel files just like the Excel Repair Tool.

 Take quick glance at the features of this tool:

• Instantly recovers any type of corrupted Excel files.
• Efficiently fixes several Excel errors and repairs XLSX/XLS files.
• Makes easy recovery of Excel data such as a chart, chart sheet, formula, cell comment, image, sort, filter, fields, forms, table, and reports very precisely.

Wrap Up:

Using all the options you can easily get rid of this Excel External table is not in the expected format error

If, in case you have any additional questions concerning the ones presented, do tell us in the comments section below or you can also visit our Repair MS Excel social account Facebook and Twitter page.

Good Luck….