Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

OLE DB or ODBC Error: [DataFormat.Error] External table is not in the expected format...

Hi All,

 

I have been using a pbix for monthly reporting. I add excel files on a monthly basis to a folder and combine them to generate the required figures and chart. Today I got the error message described in the title though until now everything worked fine. I tried to remove the new files and refresh but still it does not work.

I have been searching on the forum and on the web to fix the issue, found a few information and nothing worked - could you please suggest how I should fix this?Képkivágás.JPG

 

 

10 REPLIES 10
shizuku0516
New Member

I meet same situation about "OLE DB or ODBC Error: [DataFormat.Error]", I read below reply and solved this problem.

 

My data source located a folder in company cloud driver, today I put a BI file into this folder then refresh data, I got this error information, when I remove this file everything back to normal.

 

Make sure data source folder clean and keep source file only.

 

Thanks for reply from chendo1981 and another anonymous user, I click "Like" for you.

chendo1981
New Member

Hi,

 

Yo have to alocate only xlsx into the folder to import in PowerBI, the rest of archives can´t alocate into this folder.

 

For example, if you import one folder whit more than one archives, and also, one archive, you have to separated both:

 

......descktop/archivo1.xlsx

......descktop/data/

 

Archivo1 doesn´t alocate in data folder.

 

Ciao

v-yuezhe-msft
Employee
Employee

@Anonymous,

How about you re-connect to the folder in a new Power BI Desktop file?

Also please directly open the Excel file, check if you get restricted Access message, and check if the sheets are locked. Based on my research, the above error could occur due to the two aspects.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for the answer. None of the files are restricted and none of them have locked sheets.

I tried to re-load the folder in a new Power BI Desktop file (on another laptop) and had the following error:

 

"Failed to save modifications to the server. Error returned: OLE DB or ODBC error: [DataSource.NotFound] Excel Workbook: The 'Microsoft.ACE.OLEFB.12.0' provider is not registered on the local machine. The 64 bit Database Engine OLEDB provider may be required to read ...xlsx. To download the client software... etc."

 

I will get it installed today with our IT department and see if that helps, however I have it on my home pc and the file does not work there either.

@Anonymous,

Do you use 64 bit or 32 bit Power BI Desktop? Ensure that you install the correct version of Database Engine OLEDB provider, and the version matches Power BI Desktop.

In your scenario, uninstall Database Engine OLEDB provider, reboot machine,re-install Database Engine OLEDB provider. then check if the error still occurs.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I am trying to solve this problem on 2 computers at the same time (as I am waiting for IT to resolve my ticket on my corporate laptop.)

 

Computer (1) (home PC) I have x64 Office, x64 Power BI and x64 OLEDB provider. Still, I receive the error that the external table is not in the expected format.

I re-installed as you suggested and it did not worked.

 

Computer (2) (corporate laptop) I have x32 Office, x64 Power BI and no OLEDB provider (I guess 32 bit by default - waiting for IT to install it). I receive the error message to install the x64 OLEDB provider, but I guess I won't be able to do that as there is x64 Office.

 

Data refreshing and Power BI in general worked perfectly on both machines with the current setup until the day before yesterday for months.

@Anonymous,

For the first computer, have you rebooted the machine?

For the second computer, please install 32 bit Power BI Desktop instead and install 32 bit ACE provider, then check if you get errors.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi guys,

 

any solution to this problem already?

 

I got the same error and all included files are actually in the same format already.

Anonymous
Not applicable

Hi Snoggi,

 

2 things helped me after all which I think you could try:

 

1) Check if you surely not have any hidden files in a folder which you pull in to your model as a datasource. Damaged xlsxes and any hidden files can mass up your dataflow. (I had those when I tried to save a file on our shared drive and had some network error in the meantime.) Easiest if you check in your query editor the list of files so you can easily spot whether there is something that does not make sense.

 

2) Delete the connections in your data model between your different tables, and re-make them. 

Hello,

 

I have the same issue. re-install Office 365 but don't fix the PB. No hidden File. I recreate Excel fils. 

Any ideas ?

I am workind under the latest Windows 10 and the latest Office 365 on e VM Machine ( MAC) all softwares are up to date.

 

Bruno

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors