Skip to main content

SELECT.EXCEL

Zoltán Mikolás
  • Updated

 

Selects content from a source containing Microsoft Excel format.

Properties

From Name of the Input Data Source.The input-source can contain either a file-path, a PBinaryStream object or a .Net Stream object. A Stream object can only be specified programmatically.
The format of the source can be either .xls or .xlxs
To Name of the Output Data Source.
The output-source will always be a neutral Table-format.

Parameters

@Context

Specifies in which context to find data in the Excel-file.
The context is specified as follows: 'Sheet/columns/header/data'
 

  • Sheet
    • Can be specified as a single sheet
    • Name of a sheet, e.g. MyData
    • Index of a sheet, e.g. 4
    • Can be specified as multiple sheets: Names, e.g. MyData,MoreData, Specific indexes 2,5,7,8, Index range, e.g. 2:5, or 2:* (means 2 and the rest)
      If multiple sheets are specified the data in the sheets need to follow the same location pattern as specified by the other parts of the context
    • Empty Take the first sheet
  • Columns
    Specifies the range of columns where header and data is located. Column designations must be upper case.
    • B:M (read columns B to M to find header and data) (normal horizontally)
    • B (read columns B until no more columns containing data is found)
    • 4:12 (read rows 4 to 12 as columns (header and data is placed vertically in sheet)
    • Empty Same as A (read all columns from left until no more data) (most normal)
  • Header
    • 2:2 Skip 2 rows and then take 2 rows to be the header (e.g. the first 2 rows are empty or contains some other information that you want to ignore).
    • 2 Same as 2:1 (if take is not defined it will default to only take 1 row as header)
    • Empty Same as 0:1 (the most normal situation) (i.e. don't skip anything and only take 1 row as header).
  • Data
    • 2:100 Skip 2 rows after the header, and then take the next 100 rows as data (e.g. there is some space between the header and the data, and you only want the first 100 rows).
    • 2 Skip 2 rows after the header, and then take the rest as data until no more data is found.
    • Empty Same as 0 (most normal situation) (i.e. don't' skip anything and take all the rest as data)

Shortcuts
If context parameter is not supplied or is left empty, it will assume the above Empty default values, which in many cases will be the most normal situation (i.e. header is on the first row, and the rest is data, and there are no blank columns or rows in between or before header and data).
@Filter Filter the result of the selected data. See Command Maps & Parameters for more information.
@Order Order the result of the selected data. See Command Maps & Parameters for more information.

Map

From Name of Header, as it is named in the file.
If * is specified this will take all columns, that are not specified by any of the other map entries. If no headers are in the file, they are named col1, col2, col3 ….
If you leave "From" empty (No value) you can assign a default value to the feature in "To" by entering e.g. Value="NEW"; in the "Script" field.
To Name of the Header as it should be called in the Output table
Script Any Script code to execute for this header

Example 1:

The context Horizontal/B:I/3:1/1 matches a sheet as the one shown below:

… Horizontal (name of the sheet, although it cannot be seen from above example)
… B:I columns containing header and data
… 3:1 to find the header we need to skip 3 rows and then take 1 as the header
… 1 to find the data we need to skip 1 row after the header, and then take the rest

Example 2:

 

The context Vertical/4:11/1/1 matches a sheet as the one shown below:

… Vertical (name of the sheet, although it cannot be seen from above example)
… 4:11 columns containing header and data.
… 1 to find the header we need to skip 1 row and then take 1 as the header
… 1 to find the data we need to skip 1 row after the header, and then take the rest

NOTE: Notice that since Excel uses letters for columns and numbers for rows. By specifying the columns as numbers in the context, the command knows to read the rows from the Excel-file as columns, thereby reading the vertically stored data correctly.

Related articles

Comments

0 comments

Please sign in to leave a comment.