Forum Discussion
Memorable MemberDataset 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:
Here's the source web page:
http://abs.gov.au/AUSSTATS/[email protected]/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?
56 Replies
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."
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!Hi Anonymous , what if I can't change the source's extension?
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?
- GilbertQ
Super User
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-32-bit-processing/
- GilbertQHi there
As per the details below it would need to be XLSX or XLSM
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-excel-workbook-files/
dcrespAdvocate IV
This one has also got 3 votes from me. We deal with a lot of ABS data and Power Query provides a great way to interact with the data. However, not being able to auto refresh the data is a killer for us. Is there any update on this one as it seems to have gone quite. This has been an ongoing issue and should be an easy one for MS to resolve. Thank you for any assistance that is provided with this.
- Jmenas
Advocate III
Hi all,
I have experienced the same issue and no luck trying to change the file or making the installation Passive. Is there another way to automatically change the file extensions at the moment?
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.
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.
DouweMeerImpactful Individual
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)
- GilbertQ
Hi there
Could a potential work around be for now, to use Power Flow, and download the file to OneDrive, and then from there Power BI can then connect to the file via OneDrive?
In doing it that way, the Power BI Service will be able to refresh every 15 minutes which is the default setting. As well as the Power Flow downloading the file to the same location?
- GilbertQ
Hi there, here is an example from Microsoft
https://flow.microsoft.com/en-us/documentation/get-started-logic-flow/
And when you get to adding an action, change that to an Excel File.
NOTE: You will have to see the format of the data you are getting and create a table in Excel that represents this dataset.
NOTE II: Also ensure that you save your Excel File in OneDrive for Business, as this will mean that you can quickly connect to it via the Power BI Service or Power BI Desktop (and get 15 minute automated refreshing)
So when you make the action to an Excel file, you will be able to map the output columns to columns in your Excel File.
Then once that is done your data source should be updated automatically.
- Oscar_Mtz_V
Kudo Commander
The only workaround is to save the xls files as xlsx, worked for me.
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
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.
