ExcelToText
Description
Creates a list containing text representing the rows, columns, and cells of an Excel file (binary data type).
This function supports cells containing data using spreadsheet formulas. If a cell formula fails to evaluate“ the output for that record is "CELL FORMULA: <formula>, ERROR: <error message>”
Supported file types
- .xls
- .xlsx
- .xlsm
Use
- Excel file:
- Select an Excel argument. (Binary)
- Sheet name:
- The function automatically detects a sheet by the sheet name, if the sheet by name cannot be detected, the function detects it by the index.
- Skip rows:
- Enter the number of top Excel rows to skip.
- The default value is 0, no rows to skip.
- Example: If the column names are in the first row, enter 1 to skip the name row.
- Enter the number of top Excel rows to skip.
- Output field:
- Enter the Output field name.
Example
You import the following XLSX binary file with the goal of obtaining the Color column as plain text.
Using the ExcelToText function, you create a list field containing the content of the data in the spreadsheet minus the column names.
| Excel binary | ExcelToText() returns |
|---|---|
| <application/vnd.openxmlformats-officedocument.spreadsheetml.sheet [4.8 kB]> | <List [9 elements]> |
As this example had two columns in the spreadsheet, the field returned is a list of lists. The next step is to flatten the main list to seperate them.
| ExcelToText() | Flattened() returns |
|---|---|
| <List [9 elements]> | <List [2 elements]> |
To separate each element (spreadsheet cell) into a individual record, use the ListElement function.
The Color column data is the second index in the spreadsheet.
| Flattened() | ListElement() returns |
|---|---|
| <List [2 elements]> | Red |
| <List [2 elements]> | Red |
| <List [2 elements]> | Blue |
| <List [2 elements]> | Gray |
| <List [2 elements]> | Green |
| <List [2 elements]> | Gray |
| <List [2 elements]> | Blue |
| <List [2 elements]> | Black |
| <List [2 elements]> | Green |
Hero Platform_ Example
An example of this function can be found in Hero Platform_'s library of examples.
Download the example titled "function_ExcelToText" to see an example of this function.
