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 writer. This helper is extremely useful when outputted data from your XLSX file ends up in one column. The example below will walk you through how to use this connector when confronted with data in a single column.
- Get data from column
When trying to create a CSV from data stored in an XLSX file the data can come sometimes come out all in one column as it did in our example. The input data for this example looks like this:
This is a step by step tutorial on how you can extract that data from an XLSX column. We will also turn that data into a CSV file using the CSV editor so that the data can be used like any other CSV.
In this example we'll be getting our data from a XLSX file that was uploaded to a webform.
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 Writer.
This is what our entire workflow looks like:
Enter the file url in the File url field of the XLSX Helper. In our case our file will be coming from the Webform Trigger so we can use a JSON path to get that file name. Alternatively you could input the file url and use basic auth. This would depend on where your file location.
Enter the worksheets to be processed. The worksheet names need to be entered exactly as they appear in the spreadsheet editor. In our example the only sheet we have is called 'Sheet1'. Please be aware that this name is case sensitive.
The column is specified using a 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. In this example all of our data is in column 1 so we will use that.
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.
Use the CSV editor and the Create CSV From File operation to create a CSV file from the output of the XLSX Helper. This newly created CSV can now be used anywhere in the workflow.
The output of the Create CSV From File operation will look like this:
As you can see we now have properly separated columns with 5 rows of data that can now be used like any other CSV.