Have you ever encountered an .ods file somewhere on the web?
.ods are basically open source spreadsheets (read more here) and sometimes web pages could host these instead of .csv files.
We have come across this recently and had the challenge to make it readable for Power BI.
Initially we tested an online service that converts it to .csv which was not so straight-forward to implement into M code and also was a paid service.
After a lot of research and trial & error, we found this way to convert .ods natively in Power Query to .xml (since our .ods actually contained an .xml file)
.ods files have similarities with .zip files, thus they can be uncompressed with a script which was intented for unpacking .zip files.
Credit to Ignacio Barrau for this great Power Query code.
Copy the code below and paste it into a blank query in Power Query, it will create a function.
= (ZIPFile) =>
let
Header = BinaryFormat.Record([
MiscHeader = BinaryFormat.Binary(14),
BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
]),
HeaderChoice = BinaryFormat.Choice(
BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
each if _ <> 67324752 // not the IsValid number? then return a dummy formatter
then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
else BinaryFormat.Choice(
BinaryFormat.Binary(26), // Header payload - 14+4+4+2+2
each BinaryFormat.Record([
IsValid = true,
Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
Extras = BinaryFormat.Text(Header(_)[ExtrasLen]),
Content = BinaryFormat.Transform(
BinaryFormat.Binary(Header(_)[BinarySize]),
(x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
)
]),
type binary // enable streaming
)
),
ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
Entries = List.Transform(
List.RemoveLastN( ZipFormat(ZIPFile), 1),
(e) => [FileName = e[Filename], Content = e[Content] ]
)
in
Table.FromRecords(Entries)After connecting to the .ods file (via the web connector) and pasting the code above into a blank query, your backend should look like in the picture below.
Add a new step to the .ods file that invokes the DecompressZip function. = ExtractZipFunction(Source)
In this example the decompressed .ods file consists of .xml files.
This part will vary depending on the kind of data your .ods file includes. We were looking for statistics of the population of Austria. Hence, the important file is content.xml. When clicking on "Binary" Power Query will automatically perform several steps. In this case only the first one is needed though.
Since our .ods example file contained .xml files the content was imported as an XML file (in Power Query).
Even though were more transformation steps needed the content of data can now be read by Power BI.