Forum Discussion
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.
Hi Anonymous ,
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
I think the only option would be to compare the metadata of the before and after files.
Hi Anonymous ,
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
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
