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
mike_honey
Memorable Member
Memorable Member

Dataset Refresh error Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

I built a report using PBI Desktop - the source is an Excel file on a public website (not OneDrive).  When I publish it and then try to use the Power BI site to refresh the dataset, I get this error:

 

Data source errorExcel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider is required to connect to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

 

 

Here's the source web page:

http://abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/5368.0Apr%202016?OpenDocument

 

Here's a sample URL to one of the Excel files on that page:

http://abs.gov.au/ausstats/meisubs.NSF/log?openagent&536801.xls&5368.0&Time Series Spreadsheet&A6970A1F22604DBBCA257FC50017F8C5&0&Apr 2016&02.06.2016&Latest

 

Any ideas how I can overcome this?  

53 REPLIES 53
MattRo
Frequent Visitor

My report was compiling XLS files from a sharepoint folder. Everything worked fine on PBI Desktop. When I published to Service, I received this same error when refreshing dataset. As suggested in other answers, I converted all the XLS to XLSX and the problem went away. (Note- I did not simply rename, I opened with Excel on the web and chose Convert when it asked me.)

mst007
New Member

I had the same issue.  I was able to resolve it by deleting another table with extension as .xlsb,  You can replace the extension with .xlsx format.  There is no issue while refreshing in the local environment but in power bi services it gives error.  I hope this will resolve your issue too.

Anonymous
Not applicable

I had a different situation. PowerBI data gateway was reading Excel files from on-premises file server share. And suddenly a report datasource schedulled refresh stopped working with the above error.

-2147467259

Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

DataSource.NotFound

 

What we found out, is that a user has opened an Excel file with LibreOffice or OpenOffice Calc, and left a lock file:

.~lock.filename.xlsx#

just beside the XLSX file, that PowerBI data gateway was reading as datasource was refreshing.

Simply deleting this lock file was enough for PowerBI data gateway to successfully continue refreshing the data source. 

I saw an extra file that I deleted, referring to this element:

Simply deleting this lock file was enough for PowerBI data gateway to successfully continue refreshing the data source. 

Deleting that "extra" file in my folder solved the issue. Some cryptical name with a blank icon. I couldn't be bothered to check it first before removing, so no further details. I'm happy it runs again. 

Edit: was a temp file (.tmp)

andrerochaslc
Advocate I
Advocate I

Hi,

 

I had the same issue and the way that I resolved it was change my file extension from xls to xlsx. The problem just ocurring when I try to refresh dataset on power services.

 

"Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Workbooks must be saved as .xlsx or .xlsm file type and be under 1 GB. Some features described in this article are only available in later versions of Excel."

 

https://community.powerbi.com/t5/Service/Dataset-Refresh-error-Microsoft-ACE-OLEDB-12-0-provider-is-...

I have been dealing with this issue for a few days now and as soon as I come across this post...I was able to get the issue resolved by simply updating the file extension from .xls to .xlsx.

Thank you for this!

Good help. Thank you so much.

Hi @andrerochaslc , what if I can't change the source's extension?

Anonymous
Not applicable

This worked for my issue, thanks.

johnihab
Frequent Visitor

Hello I experienced that problem, maybe the reason is Excel File is Read-Only or you don't have the permission to edit it, So maybe it is better to convert it to CSV or Text

HI Johnihab

I couldn't work out the problem - permissions seemed ok and file wasn't read only.

Strange as the data came in but ok but the error appeared when tried to merge with another table.

However, your suggested worked and a CSV in the same sharepoint folder worked well

thanks

devhawkio
Frequent Visitor

I believe I fixed this problem! Use the data gateway to refresh this source.

 

1. Install a data gateway

2. Add the XLS data source to the data gateway

3. Check the box to Allow user's cloud data sources to refresh through this gateway cluster. These cloud data sources do not need to be configured under this gateway cluster.

4. Set the data source to refresh through the gateway

5. Refresh your dataset

I was facing the same issue and your solution solved it. Thank you. 

@devhawkio 

 

Thanks a lot, I will try it and let you know how it works. 

 

Best,

Jorge

Oscar_Mtz_V
Kudo Commander
Kudo Commander

The only workaround is to save the xls files as xlsx, worked for me.

 

Annotation 2019-10-31 123359.png

 

Anonymous
Not applicable

Hi,

 

I'm facing the very same problem but with a xlsb extension. It works fine on Power BI Desktop but when I publish to Power BI Service and try to refresh the dataset I got this error:

 

2018-12-14 14_10_19-Power BI.png

 

Is there any prevision when this will be fixed?

 

Thanks

Hello I have same problem, Power BI in 64 bit, office in 64bit, the excel is xlsb stored on cloud!

 

How it is possible to solve?

Hi there

You should just install both versions of the driver.

This can be done using the blog post below

https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and...




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I have been installed and issue is not solved

C:\Users\rdedonat\Downloads>AccessDatabaseEngine.exe /passive

C:\Users\rdedonat\Downloads>AccessDatabaseEngine_X64 /passive

I have Power 64 and Office 64 bit

the excel workbook I am reading are *.xlsb,

 

after 10 minutes the refresh on web service goes in error because I suppose the number of rows / tuples is reacheing more than one milion ....

 

Hi there

As far as I know *.xlsb is not supported in the Power BI Service.

Convert it to a *.xlsx and that should solve it.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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