Integrate Software AG Products Using Digital Event Services : MashZone NextGen Help : Appendix : Operators : Data source operators : Excel
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.
As a list table, a corresponding column is created in the data feed for every non-empty column in the source table.
Three columns are created in the data feed as a cross table. 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.
*Cells that have the Number data type in MS Excel are extracted accurately, regardless of their formatting. Therefore, the values in MashZone NextGen can be more accurate than displayed in MS Excel. By contrast, cells that have the Date data type in Excel are extracted based on the format information to maintain the accuracy of the time stamp.
*A cell may only have up to 2000 characters.
The following parameters are available.
Parameters
Description
Source
MS Excel file (xls, xlsx)
URL: HTTP address for the source file. If another operator dynamically supplies the URL, the URL cannot be edited here.
URL alias: Alias of a URL configuration. Only URL aliases for that you have the Usage privilege are available. Select a URL alias. See Manage URL aliases for details.
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):
Path prefix (alias): Alias of the resource directory with the path to a directory on the MashZone NextGen server. Select an alias of the local resource directory.
Available for Local file and URL alias as source.
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. See Manage resource directories for details.
Refresh rate ()
Specifies the time until the source file is read in again. Default value is 12 h.
Sheet
Sheet in the source table to be extracted.
Default value: First sheet
Specification: Mandatory
List table / Cross table
Specifies the table type.
Default value: List table
Specification: Mandatory
For cross tables, only one vertical iteration on the left side of the table is currently supported.
Separator
Separates the column values in the CSV file. Available are comma (,), semicolon (;), space, tab and pipe ((l)), default is comma.
Column name from row
Determines the names of the individual columns from a particular row, whose row number must be specified.
Blank cells in the row with the column name are named Unnamed column + the consecutive number of the unnamed columns, if the affected columns contain further data.
This option is not available for cross tables.
Horizontal iteration from row
Determines the column names of the individual iteration steps from a particular 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 from a particular row.
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, e.g., A3 to H128
Specification: Optional
Only a single continuous data range is possible, but it may 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. Unselect a column for excluding it from the result data. Entering a New name for a column will cause that to be used instead of the original column name in the result. Clicking Reset columns will reload the column list from the data source and undo all changes in the list.
Upload XLS files to the MashZone NextGen Repository
If required, you can upload XLS files to the MashZone NextGen Repository.
See Add External Resources as MashZone NextGen Files for details.
To upload a file to the MashZone NextGen Repository administration privileges are required.
If you use an absolute URL, e.g., http://anyhost:8080/mashzone/files/admin_xls?1454486242000, then an authentication has to be set. All requests to the uploaded file will be made with the specified user / password combination.
If you use a relative URL, e.g., /mashzone/files/admin_xls?1454486242000, then an authentication is not required. All requests to the uploaded file will be 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"}]]
}
*fileName is the name that was specified while uploading the file.
*userID is the id of the user, who should get the permission.
Copyright © 2017 Software AG, Darmstadt, Germany.
Product LogoContact Support   |   Community   |   Feedback