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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ThreeBlindMice
Frequent Visitor

Powr Query cannot open an XLSX file

Hi

 

I have a XLSX file (generated by another non-Microsoft application)  I can open with Excel but if I go to open the same file via the Power Query - Data -> Get Data - From a File -> From Workbook I get following

 

DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
    [Binary]

 

 

If I open and just save the file (do nothing), the file size increases quite a lot but it will now be able to processed via Power Query

 

Does Power query have a minium XLSX specification it wil read? This my current best guess.

 

Alternatively the exported file is technically out of specification with some small weird bug.

 

Ideas - I don't want to be opening and saving files just to get the format correct.

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @ThreeBlindMice ,

I'm afraid it's the easiest way. Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML schema which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n…
…
[DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.\r\nStackTrace:\n…

It's enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the schema. For the routine regular tasks we use poweshell script which does exactly the same in background.

 

Reference:  "The input couldn't be recognized as a valid Excel document." - Microsoft Tech Community

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @ThreeBlindMice ,

Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

v-yanjiang-msft
Community Support
Community Support

Hi @ThreeBlindMice ,

I'm afraid it's the easiest way. Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML schema which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows

[DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n…
…
[DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.\r\nStackTrace:\n…

It's enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the schema. For the routine regular tasks we use poweshell script which does exactly the same in background.

 

Reference:  "The input couldn't be recognized as a valid Excel document." - Microsoft Tech Community

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

JirkaZ
Solution Specialist
Solution Specialist

I think the only option would be to compare the metadata of the before and after files. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors