Forum Discussion
OLE DB or ODBC Error:[DataFormat.Error] invalid excel - Resolved by reducing file size
Hello,
I have encountered this error while refreshing the report, and somehow resolved it by reducing the rows in the source Excel file. I am curious about the reason.
Snapshot below is the error message of refreshing web report, which is scheduled to refresh daily. I also got the similar error when I tried to refresh the deshtop. I check the Power Query and the source Excel file without finding any problem.
I tried to resolve it by upgrading PBI to the latest version, however it didn't work.
My colleage found the Excel (xlsx.) contains 1 million data rows and suggested to remove some. Then, the report was refreshed successfully! Could anyone please advise me what's the theory behind? Is there a limit for file size? How much would that be?
Thanks in advance!
Depending on your description, you are using the import mode.
With this method, the data from the source will be uploaded to Power BI. Uploading to Power BI means consuming memory and disk space. As long as you're developing Power BI on your computer with Power BI Desktop, it would be machine memory and disk space. When you publish the report to the website, it will be memory and disk space for the Power BI cloud machine.
Power BI leverages the xVelocity compression engine and works on a columnstore in-memory technology. In-memory columnstore technology compresses data and stores it in a compressed format.
When using this method, the size of the model is limited. Your power bi (or file) model cannot exceed 1 GB. Typically, the maximum account capacity is 10 GB, but the maximum capacity per file must be 1 GB. Power BI Premium allows an additional 10 GB.
Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.
5 Replies
Depending on your description, you are using the import mode.
With this method, the data from the source will be uploaded to Power BI. Uploading to Power BI means consuming memory and disk space. As long as you're developing Power BI on your computer with Power BI Desktop, it would be machine memory and disk space. When you publish the report to the website, it will be memory and disk space for the Power BI cloud machine.
Power BI leverages the xVelocity compression engine and works on a columnstore in-memory technology. In-memory columnstore technology compresses data and stores it in a compressed format.
When using this method, the size of the model is limited. Your power bi (or file) model cannot exceed 1 GB. Typically, the maximum account capacity is 10 GB, but the maximum capacity per file must be 1 GB. Power BI Premium allows an additional 10 GB.
Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.Thank you V-lianl-msft !
Is there a different mode other than the import mode that I can use? With wider limit on the model size? We use this PBI report to monitor the manufacturing information of the whole year.
Thanks!
Hi OZMckenzie ,
The connection to excel can only be in import mode. You can consider using incremental refresh, which will improve the performance of refresh.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi V-lianl-msft
I have one further question regaring the incremental refresh, my data source are mostly Excel file, however I read from somewhere that Excel doesn't support the incremental refresh.
The major data source is loaded from SAP and export to Excel, refresh daily. The rest are Excel Queries that I created to gather data from multiple sheets. Does the incremental refresh work on these kind of file?
Could you please advise? Thank you so much.
