Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Input from a file with extension ODS

JayG
7 - Meteor

Hi 

Is there a way to read data from an file with extension "ods"?

I tried saving "ods" file in Excel as xls, but then Alteryx complained that "too many columns".

 

Could you advise.

Regards

JayG

6 REPLIES 6
ChrisTX
13 - Pulsar

Search Google to determine what type of file it is, to know if it's from special software in a proprietary format.

 

Can you open it with a text editor like Notepad or Notepad++ ?   If it's a text file, you should be able to read it as a CSV file.  In Alteryx, using a Delimiter of   /0   means no delimiter.

 

If it's some type of database file, an ODBC driver should allow you to read it.

 

Chris

 

JayG
7 - Meteor

Hi Chris

 

"ODS" means Open Document Spreadsheet

This is not a text file.

So far, I have been open this using Excel and save it as Excel workbook (xlsx) or Excel 97-2003 workbook (xls).

next challenge was because Alteryx Input refused to accept about 373 columns, I needed to delete some of the columns.

 

Is there an easier way to Input data from a file with an ODS type?

 

JayG

ChrisTX
13 - Pulsar

I haven't seen a connector for ODS files.  Consider creating a new Idea.

 

The link below is for Google Sheets, but it mentions the need to manually covert an ODS file.

 

https://community.alteryx.com/t5/Alteryx-Designer/The-How-to-Guide-to-Google-Sheets/ta-p/20354

 

The 'How to Guide' to Google Sheets

If you have Excel, .csv, .txt or .ods file types in your Google Drive, you will have to convert them to a Google Sheets file before pulling them in.

 

 

alice_burrell
5 - Atom

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:

  1. 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
  2. Dynamically list the folder contents in the directory you just unzipped to
  3. Filter to just retrieve content.xml file
  4. Read content.xml as a blob
  5. Blob convert to text with a UTF8 code page
  6. Use XML tool to get outer XML for child name table-row
  7. Record ID tool to number the rows with RowID
  8. Use XML tool again on the XML returned by the last tool, to get child values for child name table-cell
  9. Use multi-row formula to label with an ascending ColumnID, grouping by RowID
  10. 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 😊

JayG
7 - Meteor

Thank you for sharing the steps for opening an ODS file before using in an Alteryx dataflow.

 

We have tried,

 

Step1: Open a file with "ODS" in Excel

Step2: From Excel save as Microsoft Excel format

 

Ideally, Alteryx should be able to cope with file in an ODS format.

alice_burrell
5 - Atom

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 😁

Labels