xlsx

Load data from an XLSX file.

Introduction

Use the xlsx instruction to load data from a whole XLSX spreadsheet file. Configure the instruction 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 transformation.

Creating a custom script transformation using the xlsx instruction

To create and configure a custom script using the xlsx instruction, follow these steps:

1. Create a custom script transformation.

2. In the Instructions step, select the xlsx instruction.

3. To configure the custom script instruction, 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; or a name of a named range from the Excel workbook or worksheet.

• 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 use within this custom script.

A subtable is a temporary table that 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.

If a subtable does not exist for the current custom script, the transformation is applied to the data extract, and the enriched data is output into the subtable. If the subtable already exists for the custom script, the subtable is used as the input for the transformation and optionally as the output.