Power Query doesn't read an Excel file

[CPRobc]

I have an Excel file produced by an external database (a data export).

Attempting to read this file in Power Query I am presented "File Contains Corrupted Data message".

I can Open the file in Excel without issue, so if I Open the file, save it and close it Power Query will read the file without issue.

The file is overwritten when updated daily and the probelm re-occurs.

I've tried this in Power BI (July 24) and Excel (version 2405) - same problem.

Any suggestions?

[Chewdata]

Hey,

I had a different, but slightly similar problem a while back. After an update of the library that generates the excel-file, Power BI had problems with correctly reading the file. The solution i finaly found was the following (quoting):

PowerBI has an implementation of xlsx parser that doesn't work well with specific compression methods. In my case, Apache POI 5 defaults to Zip64 mode, a compression method that PowerBI's xlsx parser struggles with, while Microsoft Excel's xlsx parser seems to be able to handle just fine.

Our workaround is to set Apache POI's compression strategy to Zip64Mode.AsNeeded, as it was in Apache POI 4.


Changing this setting solved it for me. Maybe this might solve your problem too.

[Omid_Motamedise]

It seems like Power Query is having trouble with how the file is being overwritten, even though Excel handles it fine. Here are a few suggestions to address this issue:

Check File Encoding and Format: Ensure that the file's encoding or format is consistent and correct. Sometimes, the way the file is saved or updated could introduce inconsistencies that Excel handles but Power Query doesn't.

Automate the Resave Process: If opening and resaving the file in Excel solves the issue, you might automate this step using a VBA script or a PowerShell script to run before loading the file into Power Query.

[cpwebb]

Is there a sensitivity label applied to the file? See https://blog.crossjoin.co.uk/2024/06/16/the-dataformat-error-file-contains-corrupted-data-error-in-p...

[CPRobc]

Thanks, I can open the file without issue in Excel.  I just can't read it in Power Query (either in Excel or Power BI).

There is no password on the file.

[Anonymous]

Post a download link of such a file here.

Andreas.

[CPRobc]

Sorry, it's commercially sensitive data.

[Anonymous]

Unzip the Excel file and try to import the file \xl\worksheets\sheet1.xml

What happens?

Andreas.

[Anonymous]

Hi @CPRobc 

Power Query is used to connect to data sources to load and transform data, it cannot modify the file itself that is connected to.You can try using some other methods like VBA or Office scripts to automate the Open file -> Save file -> Close file process. Here are some similar threads I found, hope they would be helpful:

automation - How do I automate the opening, saving, and closing of an Excel file on Windows? - Super...

Script to Automate an excel file to Open, wait 5 seconds, Save and Close - Software - Spiceworks Com...

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

The file might be set to confidential or must be opened with a password. In both cases the XML data in the file is encrypted and therefore PQ can not read that file.

Andreas.

[CPRobc]

There is no password on the file.  I can open the file without any problems, I just can't import from it via Power Query.