Excel to JSON

With this node you can interpret Excel data and convert it to a JSON object.

It has the following configuration options:
  • Excel Data: here you can define the URL of the excel file that contains the data. Select the pencil to open Expression Builder.
    Note: Only Excel files with extension .xls are supported.
  • Conversion Mode: here you can specify how to read or convert the Excel data. Options:
    • Row By Row: if you select this option, the data is read row by row and the results are stored as records. Each record contains an object where you can access the data by key (derived from the data in the first row).
    • Column By Column: if you select this option, the data is read column by column and the results are stored as records. Each record contains an object where you can access the data by key (derived from the data in the first column).
    • As Grid: if you select this option, the data is read and the results are stored as three nested arrays/objects, where the first level are the sheets, the second level are the rows, and the third level are the columns. The sheet level will not be present if you explicitly select a sheet.
  • Sheet Name: here you can define the sheet name to get the data from.
    • If you set this parameter to an empty string and if the Conversion Mode is Row By Row or Column By Column, the first sheet will be used.
    • If you set this parameter to an empty string and if the Conversion Mode is As Grid, all sheets will be returned.
  • Field Names: here you can specify the expected row or column names in the first record. Only columns or rows that match the specified names will be extracted.
    • If no field names are specified, all non-empty rows or columns will be read.
    • When the Conversion Mode is As Grid, this parameter is ignored.
  • No Field Names:
    • If the Excel data has no row or column names in the first line, you need to select this checkbox. In this case, you need to specify the Field Names. Those names will be used to create the JSON data. If the Excel data has column names, you need to deselect this checkbox. When the Conversion Mode is As Grid, this parameter is ignored.
  • Skip First: if you want to exclude the first line from the data saved to JSON, you need to select this checkbox, even though the data in the first row will still be used to check/define the field names. If you want to include the first line, you need to deselect this checkbox. When the Conversion Mode is As Grid or when the checkbox No Field Names is unselected, his parameter is ignored.
  • Support Unicode: if you select this checkbox, Unicode text (text with for example accented letters) is supported. This option will however prevent some complex files to be processed.
  • JSON Output: here you can specify the output path of the JSON data.
    • If you leave this field blank, the data will be sent to a container in the database and no physical file will be created.
    • If you define an absolute CLOUDFLOW URL containing a filename (cloudflow://..., see Server URL for more information), an output file with the specified file name will be written in the specified folder.
    • If you define an absolute CLOUDFLOW URL without a filename (cloudflow://..., see Server URL for more information), an output file with the original file name will be written in the specified folder.
    • If you enter a relative path containing a file name, an output file with the specified file name will be written in the specified folder based on the original input folder.
    • If you enter a relative path without a file name, an output file with the original file name will be written in the specified folder based on the original input folder.
  • Overwrite existing file: if you select this checkbox, files that already exist will be overwritten. If you leave this checkbox unselected, existing files will not be overwritten and an error is displayed.