Excel Reader

This transform can read Excel files, or stream them, as a data-source.

The Excel Read transform can transfer both Excel 97-2003 and Excel 2007-2010 formats.

Reader > File Layout

The file layout tab for the Excel Read transform is where the file and hierarchical dataset options are specified.

Transform Id

The unique user-defined name for the transform.

Data Source

The controller type is defined here. This is the source from where the Excel data will be read, which can be Email, File or Http.
See Input/Output Controllers (IO Controller) for more information.

Controller Options

This is the expandable options section beneath the Data Source drop-down.

These options change according to the data source selected.

See Input/Output Controllers (IO Controller) for more information.

Worksheet Id

The name of the worksheet to be imported. The Worksheet Id can either be a number for the specific index sheet (starting at 0 for the first) of the worksheet, or a text value referring to the name of the worksheet.

Header Rows

The fixed number of rows at the start of the file which are filled with content that is not data, e.g. company logo.

Footer Rows

The fixed number of rows at the end of the file also filled with content other than data. All content within the footer rows will be ignored by the reader.

Mapping Style

By Position - Fields will be mapped according to their position within the file.

By Field Heading - Fields will be mapped according to their headings. This style of mapping is available when headings are detected within the file.

Hierarchical Data

A Hierarchical Data dataset can be generated from an Excel file when:

There is a field within the data that identifies the type of record (i.e. header/details/sub-detail etc.)
The field identifying the transaction type is located in the same field position for all records.

Example

The screenshot below illustrates a file with a hierarchical structure:

The first field identifies the transaction type for all records. In this example, “1” denotes a header record to a Receivables (A/R) Invoice, and “2” denotes a detail.

Hierarchical Dataset Options

To configure a hierarchical dataset, expand Hierarchical Dataset Options.

Hierarchical Dataset

Keyed Fields

The key field is used to construct a hierarchical dataset. For this reason, keys are assigned either on import, if the dataset is already hierarchised, or in the hierarchy transform.

By giving a record a numerical value, the parent-child relationships are defined. Child records are inserted into the dataset when its keys match those of its parent’s.

When setting the keys, each child transaction must have at least one more key defined than its parent.

These are defined in the field mapping tab of the Hierarchy transform.

Ordered Data

On import, the structure and relationship IMan identifies between fields relies on the order of the data within the file.

Record Type Field Id

If the Hierarchical Dataset box is selected, the field containing the record’s transaction type needs to be identified. That is done with this drop-down. In the screenshot example above, the transaction type is identified in field 1.

Hierarchy Detection Results

The records detected and the identifier assigned to them (in the example above, "1" or "2") are displayed here after the Refresh button is pressed.

Reader > Field Mapping

For hierarchical datasets, it is important to use a file containing all hierarchical transaction types because it is not possible to manually define additional types within this transform after import.

Transaction Identifier

If a Hierarchical Data dataset, each Transaction detected in the Excel file will be listed in this drop-down; if it is a flat dataset, only one Id will appear here. Selecting an Id from this list will open it for editing.

Changing the selected Id on this drop-down will save the currently active Id and open the newly selected transaction into the field mapping grid.

Transaction Id

The Transaction Id can be edited here, if the user chooses. To see changes made to this Id, it is necessary to close off the setup window, and load it again from the Transform Setup screen.

Parent Id(Hierarchical Datasets Only)

For flat datasets, or when the topmost parent is currently selected from the Transaction Identifier, the same transaction Id will appear in this drop-down. This is because a flat dataset has no parent-child relationships, and a topmost parent can have no parents itself. For other transaction Id's, the available parents will appear here. The appropriate Parent Id is selected in order to build the correct hierarchical relationships between the imported records.

Position

The position of the field in the file. The position of a field is fixed and cannot be edited.

Field Name

The unique name of the field.

Import

When set to True, the field is included in the resultant dataset.

When set to False, the field will be dropped from the dataset.

Type

The data Field Types of the field.

Key (Hierarchical Datasets Only)

The key applies to Hierarchical Data datasets only because it is used to insert fields into the correct place within the dataset depending on their relationship to other records. The Key fields of the record being inserted must match the parent records keys.

Each child transaction type must have at least 1 more key defined than its parent transaction type. This is due to the nature of the structure being built.

Populating the Field Mapping Grid

If valid values have been entered into the File Path, Name and Delimiter fields and there are no errors after pressing the refresh button, the fields from the Excel file will populate the grid on the Field Mapping tab. When this does occur, the file has been successfully read to extract the transaction types and fields.