xlsx

Load data from an XLSX file.

Introduction

Use the xlsx enrichment to load data from a whole XLSX spreadsheet file. Configure the enrichment to only collect certain parts of the file. If you load data from multiple sheets, the data from the sheets is concatenated and headers of the individual sheets are ignored.

To load data from only one sheet from a spreadsheet, use the excel enrichment.

Creating the enrichment

For more information on creating an enrichment, see Using custom scripts.

Configuring the enrichment

To configure the enrichment, fill in the following fields. Required fields are marked with an asterisk (*).

Sheets

Select the sheets within the spreadsheet to load.

To select a sheet for loading, fill in the following fields:

• In sheet, enter the name of the sheet within the spreadsheet to load.

• In row_offset, enter the number of rows to ignore. Only rows after this offset are loaded.

• In column_offset, enter the number of columns to ignore. Only columns after this offset are loaded.

• In range_string, enter the range of the data to load. For example, enter A2:F9 to only collect data within that range.

• If a sheet contains metadata, enter the range of that sheet that contains the metadata in meta-ranges.

Subtable Sheets

If the data is to be loaded into subtables, configure the subtable sheet options such as the name of the sheet and any offsets required.

Include All Sheets

Select this checkbox to load all sheets from the XLSX file.

Include Sheet Name

Select this checkbox to add a column to the data extract that contains the sheet names.

Ignore Missing Sheet

Select this checkbox to ignore any sheets selected in Sheets but not available in the XLSX file.

Keep Links

Select this checkbox to keep links to external workbooks.

Guess Types

Select this checkbox to let Adverity convert data types into an appropriate data type such as a string or integer.

Meta Target

Enter the name of the node where to save metadata. This is a mandatory field if you select the Global Meta Ranges checkbox.

Global Meta Ranges

If metadata is to be made available globally, enter the name of the sheets that contain the metadata, and provide the range that contains the metadata.

Meta Mapping

To map metadata values into new fields, enter the names of the new fields to which the metadata is to be mapped.

Data Only

Select this checkbox to prevent formulas from being parsed.

Keep Vba

Select this checkbox to keep Visual Basic code for macros.

Read Only

Select this checkbox to prevent the conversion of data types.

Force Fix

Select this checkbox to remove leading and trailing whitespace in headers.

Subtable

Enter the name for a subtable that you want to contain the enriched data. The enrichment is applied to the whole data extract, then the enriched data is output into the subtable you have named here.

This subtable is a temporary table, which means it only exists for this custom script. You can apply additional instructions within the same custom script to the subtable. However, the subtable cannot be used in any other custom scripts.