Get data from ODS files (OpenOffice/StarOffice spreadsheet)

[Anonymous]

I tried to Get data from folder with ODS files ( OpenOffice/StarOffice spreadsheets that are easily opened by Excel). However PowerBI does not recognize this format, which frankly speaking is strange, as MS Excel can easily read these files.

Could you please advise why PowerBI does not recognize this format?

Are there any plans for PBI to recognize ODS files?

I would like to avoid step with converting ODS to excel.

[Syndicate_Admin]

Hey there,
I have the same problem right now and am looking for an elegant solution. Did you by any chance find something good?

[v-juanli-msft]

Hi @Anonymous 

There is a workaround: convert ods file to excel file using online tool, then connect to excel files with Power BI.

Best Regards

Maggie 

[Anonymous]

Hi Maggie,

Thanks, but this actually does not answer my question and to be honest I would not recommend the solution you proposed.

Sharing any company reports online bears great risk of data leak. Secondy, using this tool takes longer than saving .ods file as .xlsx in Excel. Or saving .ods file as .xls. 

I would really appreciate if anyone could share information whether there are plans for PBI to recognize .ods files in the future.

[Brunner_BI]

Here is a better way without any third party tools

https://en.brunner.bi/post/how-to-open-ods-files-in-power-bi-power-query

[Syndicate_Admin]

Thanks!

How did you find this?

[Brunner_BI]

We found somewhere that .ods is similar to zip and there was this M code (as outlined in the blog post) for unzipping files in Power Query. We gave it a try and ended up with our xml file and then after some transformations were able to load the table we were expecting.

It also refreshes in the service, our public "covid report" is using this data for the demographic distribution in Austria.

Our source file is hosted here.

[Rebecca6634]

Hi Brunner_BI

I need to connect to this file Live tables on homelessness - GOV.UK (www.gov.uk). Please could you advise how to do this with your code, I can't seem to get it to work.

Thank you!

[Daryl-Lynch-Bzy]

Hi @Rebecca6634 , could you please create another post with your question?  Please include the code that you have used in that post.
Many thanks

Daryl