Create templates

The most important step in creating a template is to determine the expressions that define the selection patterns of the cells.

When you perform a selection of numerical data with the features on the Selection tab, an expression for the selected ranges is created in the associated fields in the Selection section. You can edit this expression using a basic syntax for which you can find examples here 3.

In the expressions on the Template tab, the syntax is enriched with dedicated symbols and functions to make selection patterns. The evaluation of these symbols and functions when interpreting the expressions returns a value which can be a cell, a column, or a row.

You can directly use a range expression of the Selection tab as an expression in the Template tab. It is a good starting point to replace the value of a cell, a column, or a row in the expression with the functions of the expression language that are returned as values according to cell,column, or row.

To create a template, we recommend you start from a selection made on loaded data.

Note

To edit an existing template, execute the template on loaded data and switch to the Template tab to edit the expression. Please refer to the Execute and manage templates section for more information on executing a template.

Example

Suppose you have a set of files and you want to import the same physical quantities (from experimental measurements) as an XYs table from each file. Create a template to automatically select the corresponding ranges as follows:

1. Open one of the files in the Data Import.

2. On the Selection tab, select the 1D/XY table type, set the first row as the Titles row and the second row as the Units row. Select column A for the X-axis and columns B and C for the Y-axes. You should get the following result:

3. The goal is to create a template that does not refer directly to columns A, B, and C. The template should contain an expression that refers to the content, i.e. the selection is always made on the Time, the Acceleration, and the Velocity data. If you write the following expression COL(FCELL("Time")) for the X-axis, at the evaluation step a search is performed to find the cell that contains the string Time (function FCELL). Then only the value of the cell's column is returned (function COL). You can repeat the same logic for the Y-axes expression: COL(FCELL("Acceleration")) COL(FCELL("Velocity")).

Please see the Examples for more advanced examples.

Syntax

The Data Import has a language that defines the symbols and functions you can use in expressions.

• Operations

This table summarizes the interpretation of operations in Data Import. Binary operations are left-associative so that, in any expression, operators are evaluated from left to right.

Operation

Action language

#

Binary operation to define a cell from a column and a row. COL(FCELL("Velocity"))#ROW(FCELL("Velocity"))

:

Both binary and unary operations to define a continuous range between two cells, two columns, or two rows. COL(FCELL("Time")):COL(FCELL("Velocity"))

/

Binary operation to separate the ranges of two slices for M1D table.

space

Binary operation to define a non-contiguous range.

Caution The space character is a special symbol to define non-contiguous ranges. None of the spaces except the one in the string "my search" in the following expression are allowed ROW( FCELL( "my search", A1,B5) )

• Symbols

This table summarizes the interpretation of built-in values in Data Import.

Symbol

Value

$BLANK

Refers to an empty content for a cell. It used as FCELL($BLANK)

$TITILESROW

Refers to row of the cells that contain the titles as defined in the Selection tab

$UNITSROW

Refers to row of the cells that contain the units as defined in the Selection tab

$LASTROW

Refers to the last row from which the cells are empty

$LASTCOL

Refers to the last column from which the cells are empty

• Functions

This table summarizes the built-in functions in Data Import.

Function

Reference

Value

COL(cell)

( cell ) → column

Returns the column of cell. COL(C20) → C

ROW(cell)

( cell ) → row

Returns the row of the cell. ROW(C20) → 20

NEXTCELLC(cell, int)

( cell, int ) → cell

Returns the cell that is located in the same row as cell and int columns further to the right. NEXTCELLC(F20,4) → J20

NEXTCELLR(cell,int)

( cell, int ) → cell

Returns the cell that is located in the same column as cell and int rows further down. NEXTCELLR(F20,4) → F24

PREVCELLC(cell,int)

( cell, int ) → cell

Returns the cell that is located in the same row as cell and int columns further to the left. NEXTCELLC(F20,4) → B20

PREVCELLR(cell,int)

( cell, int ) → cell

Returns the cell that is located in the same column as cell and int columns further up. NEXTCELLC(F20,4) → F16

FCELL("string")

( string ) → cell

Returns the first cell whose content is strictly string.*

FCELL("string",cell1,cell2)

( string, cell, cell ) → cell

Returns the first cell whose content is strictly string in the range cell1:cell2.*

** the parsing is done row by row.*

Test expressions

To trigger the validation of the expression, on the Template tab, in the Expression section, click the To selection button.

Figure 28: Expression validation: To selection

The expression is evaluated and its value is set to the corresponding selection field on the Selection tab. In addition the selection is highlighted on the spreadsheet.

Note

You can check the value of the expressions and the history of the expressions evaluation in the Template log tab. This tab is located next to the Table preview tab.

Detect errors at edit-time

When edit-time checking is triggered, the Data Import editor detects potential errors in the expressions.

The Data Import lists the errors and evaluation history in the Template log tab. For errors, a description displays the details that caused the evaluation to fail.

Figure 29: Errors in the log tab

An expression is evaluated from right to left. Each line with the information mark corresponds to a step in the evaluation of the expression; in this way you can have a history of which parts are evaluated. In the image above, for the Y-axes, COL(FCELL("Velocity")) is the first part that is evaluated in the entire expression. There is no error and so an information message returns the value of this part C and the remaining part of the expression to be evaluated. The second part of the expression has an error, so the same string as the previous message is written but with an error mark. Then the cause of the error is displayed.

You must click the To selection button to update the Template log panel and check that your corrections are validated.