OLE DB- eller ODBC-fel: [DataFormatError]External table is not in the expected format

[Evli]

I have an excelfile.xlxs in a folder that is connected to Microsoft Power BI. When I set up 3 tables from the file in relations it was all fine. When I try to update the small database this message occur "OLE DB- eller ODBC-fel: [DataFormatError]External table is not in the expected format". I can't find any solutions for it.

I have microsoft office 2016 32-system, AccessEngineDatabase 32-system and Power BI 32-system.

Suggestions anyone?

[v-eqin-msft · Accepted solution]

Hi @Evli ,

Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports.

The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.

You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula

Excel.Workbook([Contents])

If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.

Or you could try to save these down as ".XLSX" format,

Refer to:

https://community.powerbi.com/t5/Power-Query/DataFormat-Error-External-table-is-not-in-the-expected-...

http://www.repairmsexcel.com/blog/excel-external-table-is-not-in-the-expected-format

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft · Accepted solution]

Hi @Evli ,

Sometimes that error occured when Excel file is the result of export from a third-party program (accounting system, for example). These files can have a minor errors in XML schema, and they can be easily read by Excel itself, but Power Query raises an error. I met this errors permanently with my accounting system exports.

The only way I found to fix it is open in Excel, save and close. After that Power Query has no problem with these files.

You can check what file raises an error if you make a query to folder (Folder.Contents or Folder.Files, filter all xlsx files and then add a custom column with formula

Excel.Workbook([Contents])

If file has a mentioned error, this formula will return an error for this file (keep rows with errors), and you can find all files with problems.

Or you could try to save these down as ".XLSX" format,

Refer to:

https://community.powerbi.com/t5/Power-Query/DataFormat-Error-External-table-is-not-in-the-expected-...

http://www.repairmsexcel.com/blog/excel-external-table-is-not-in-the-expected-format

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Evli · Accepted solution]

Thank you for your answer. Link 2 helped. It's seemed that my ODBC-driver wasn't the correct one. 

[vkodakandla]

@Evli I have the same situation. Can you explain what you did so that I can do the same to solve the problem. 

Thanks, 

Vivek

[Evli]

Sorry, guess you want the answer in english 🙂

My version of office was 32-bit and I had downloaded and installed Power BI for 64-bit, which was wrong for my computer. Therefore, I uninstalled Power BI and installed 32-bit instead.

Then I download the provider  ACE Oledb 12.0 from Microsoft which was missing on my computer.

I used this link to find my solution to the problem https://www.repairmsexcel.com/blog/excel-external-table-is-not-in-the-expected-format 

Here is information for the provider

Download Microsoft ACE.OLEDB.12.0 Provider.

When you click Download, you will be prompted to select the architecture, ie 32 BIT (x86) or 64 BIT (x64).

After downloading, right-click the Microsoft Access Database Engine 2010 Redistributable file and click install and restart after the installation is complete.

[Evli]

Min version av office var 32-bitars och jag hade laddat ner och installerat Power BI för 64-bitar, som var fel för bara min dator. Därför avinstallerade jag Power BI och installerade 32-bitars istället. Sedan laddar jag hem en leverantör från Microsoft som saknades på min dator. 

Jag använde denna länk för att hitta min lösning på problemet  https://www.repairmsexcel.com/blog/excel-external-table-is-not-in-the-expected-format

Här är information för providern

Hur installerar jag Microsoft ACE Oledb 12.0 -leverantör?

Ladda ner Microsoft .ACE.OLEDB.12.0 -leverantör 

När du klickar på Ladda ner kommer du att uppmanas att välja arkitekturen, dvs 32 BIT (x86) eller 64 BIT (x64). När du har laddat ner, högerklickar du på filen Microsoft Access Database Engine 2010 Redistributable och klickar på installera och starta om maskinen efter installationen är klar.