Excel

Reads a worksheet of an MS Excel file and writes the individual values to table columns in the data feed based on the specified parameters. The source table can be imported as a list or cross table.

In list tables, a corresponding column is created in the data feed for every non-empty column in the source table.

In cross tables, three columns are created in the data feed: A vertical iteration column corresponding to the first source column with the header, a horizontal iteration column defined in the operator and a value column.

MS Excel cells of data type number are extracted accurately, regardless of their formatting. Therefore, the values can be more accurate than displayed in MS Excel. MS Excel cells of data type date are extracted according to the formatting information to maintain the accuracy of the time stamp.

A cell can have a maximum of 2,000 characters.

The following parameters are available.

Parameters

Description

Source

MS Excel file (xls, xlsx)

  • URL: HTTP address of the source file

    If another operator supplies the URL dynamically, the URL cannot be edited here.

    Example

    http://myhost.company:1080/documents/rest/links/03388871-367d-4abe-a2c9-37bc9fa44580?tenantid=default&revision=1

  • URL alias: Alias of a URL configuration. Only URL aliases for that you have the Usage privilege are available. Select a URL alias. For details, see Manage URL aliases.
  • Local file: Loads file from a resource directory. Files must be located in a defined resource directory on the MashZone NextGen server.

    Path prefix (alias): Alias of the resource directory with the path to a directory on the server. Select an alias of the local resource directory.

    In the input box, enter a path to the relevant data source file for the Path prefix (alias) selected.

    Example

    /documents/rest/links/03388871-367d-4abe-a2c9-37bc9fa44580?tenantid=default&revision=1

Browse file (alias)

Enables you to browse the resource directories with the alias defined. Click the Browse file alias (...) button and select the required source file. At least one resource directory must exist. For details, see Manage resource directories.

Insert parameter (Insert parameter)

Inserts user defined input parameters at the cursor position. The button is clickable only if at least one user input parameter, for example, Text user input, has been inserted in the feed definition.

For details, see URL parameter syntax.

Parameter options (Parameter options)

Enables you to set input parameter options.

Refresh rate ()

Specifies the time until the data source is read in again. Default value is 12 h.

Sheet

Sheet in the source table to be extracted.

The default value: First sheet

Specification: Mandatory

List table / Cross table

Specifies the table type.

The default value: List table

Specification: Mandatory

For cross tables, only a single vertical iteration on the left side of the table is currently supported.

Separator

Separates the column values in the CSV file. Comma (,), semicolon (;), space, tab and pipe ((l)) are available. Default is comma.

Column name from row

Determines the names of the individual columns from a specific row, the row number of which must be specified.

This option is not available for cross tables.

Horizontal iteration from row

Determines the column names of the individual iteration steps from a specific row. The column name of the vertical iteration is also determined from this row.

Import values from row

Extracts all values from the source file starting with a specific row.

The default value: 2

Specification: Mandatory

Import data range from/to

Area of the table from which data is to be extracted, specified using column and row coordinates, for example, A3 to H128

Specification: Optional

You can only specify a single continuous data range that can contain empty rows or columns.

If no upper limit (to) is specified for the data range, all cells above the lower limit (from) are extracted.

Configure columns

Configures the columns list. You can unselect a column to exclude it from the result data. If you enter a new name for a column, this name is used in the result instead of the original column name. If you click Reset columns, the column list is reloaded from the data source and all changes in the list are undone.

Upload XLS files to the MashZone NextGen Repository If required, you can upload XLS files to the MashZone NextGen Repository.

For details, see Add External Resources as MashZone NextGen Files.

To upload a file to the MashZone NextGen Repository administration privileges are required.

If you use an absolute URL, for example, http://myhost.company:1080/documents/rest/links/03388871-367d-4abe-a2c9-37bc9fa44580, an authentication must be set. All requests to the uploaded file are made with the specified user/password combination.

If you use a relative URL, for example, /documents/rest/links/03388871-367d-4abe-a2c9-37bc9fa44580, an authentication is not required. All requests to the uploaded file are made with the current logged in user.

In some cases, it might be necessary to grant view permission for a user. This can be done with the API console, e.g.:

{

"version":"1.1",

"sid":"PolicyService",

"svcVersion":"0.1",

"oid":"addPermissions",

"params":[

"fileName","type.entity.file","VIEW",

[{"principalId":"userID","principalTypeId":"User"}]]

}