This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I was asked this question today and it turns out there is a way to read in ODS files. They're just XML files zipped up! Here's how I did it:
Use the CMD tool to run 7z against the ODS file to unzip it into a folder. My arguments were something like x -y -o* example.ods
Dynamically list the folder contents in the directory you just unzipped to
Filter to just retrieve content.xml file
Read content.xml as a blob
Blob convert to text with a UTF8 code page
Use XML tool to get outer XML for child name table-row
Record ID tool to number the rows with RowID
Use XML tool again on the XML returned by the last tool, to get child values for child name table-cell
Use multi-row formula to label with an ascending ColumnID, grouping by RowID
Crosstab, grouping data by RowID, using ColumnID as headers and the value "p" for the values
Then I wrapped the whole lot up inside a macro. I like to use %Engine.TempFilePath% to do my unzipping and file manipulations in the workflow temp directory so that the files don't hang around forever. Sometimes I also use the CMD tool to write out and execute a batch file to explicitly delete the unzipped files after step 4 or 5.
This worked for me, at least for reasonably small ODS files. With a bit more effort you can probably wrap it into a nicer macro with a bit more logging and make it available to others to use also.
The nice thing about ODS is that it's an open format 😊
I understand many conversion utilities outside Alteryx are available, but my aim was to build a way to open it INSIDE alteryx so that I could use the Gallery to schedule the flow to read ODS files automatically without my having to manually perform the conversion - I personally don't fancy converting ODS files daily to feed my automation! Hence my description of the steps with a list of Alteryx tools. Once built, you can save it so that it becomes a "read ODS" macro you can make appear on the IN/OUT palette, just like any other data input. Basically, I'm telling you how to build yourself the facility to read ODS natively in Alteryx without having to wait for them to release it in the next version 😁