XLSX HelperA collection of helpers to extract data from XLSX files
The XLSX Helper currently includes the ability to extract data from an XLSX file. Extracted text data can then be used in conjunction with other connectors, such as the CSV Processor.
- Get data from column
This is a step by step tutorial on how you can extract data from an XLSX column from multiple worksheets. It will also cover the use case of importing this data into the CSV Processor afterwards.
This is assuming that the data within the XLSX column is already formatted as CSV data. If the data in the XLSX column is formatted like a normal spreadsheet, please use the "Create CSV from XLSX file" operation in the CSV Processor.
First step for this operation is to enter a url of the XLSX file to be processed. Basic auth can be used here to access the file url. The basic auth option can be accessed in the advanced settings.
Enter the worksheets to be processed. The worksheet names need to be entered exactly as they appear in the spreadsheet editor. This name is case sensitive.
The column is specified using the number. The leftmost/first column would be 1, the second column would be 2 and so on. Please note that only one column can selected per operation.
The output will appear as a list of file items. Currently, files created will be text files containing all data from a column.
Each item will have a URL which can be used to access the created file containing data from the specified column. Each file item will correspond to a worksheet within the XLSX file. Each item will have the name specified with an underscore and the name of the worksheet appended.
Output can be processed by using the Loop Collection operations to iterate through the list of created files. These files can be passed to other operations to be processed.
For example, if the data in your XLSX column was already formatted as Comma Separated Values (CSV), then the output file could be imported into the CSV Processor (please see below).
This can be done by iterating through list in the "results" property in the XLSX Helpers output. Below is the JSON path you could use to do this using the Loop Collection helper.
On each iteration, the "url" value can be used to import the relevant file into the CSV Processor. Please see below for an example JSON path used in the CSV Processor import operation. Please note each new iteration will output a new CSV ID.