Expressions and their Resolving
Expressions and their Resolving in CSVfox commands
General
Expressions are ubiquitous in CSVfox commands.They are resolved at runtime for each individual CSV data row and for each individual CSV data field.
Expressions can be used
- as content to be assigned to a CSV field:
-add[GrossPrice]=([NetPrice]*[TaxFactor]) - as a condition for a command execution:
-if{...}//set[field]= ... - as a filter condition for the output of data rows to the output file:
-filter[Fruit]!c=Apple/Pear
As a condition, an expression yields a truth value that decides whether or not that condition is satisfied.
Resolving of an Expression
Resolving means doing all necessary replacement inside an expression.
This is one of the main functions of CSVfox, building the output data from input, according to your requirements.
Resolving works line-wise, which means that all (but only the) data of the current data row is available for processing.
When resolving happens on the input side (+set, +replace, +regex, +add etc.), only data of the currently read input or merge file is available. Resolving on the output side (-set, -replace, -regex, -add etc.) will happen afterwards and works with all data of the input and merge files, combined into a single data row for output.
Resolving includes:
- inserting well-known data, like the current line number for
(#) or the original contents of the current field instead of[*] - interpreting, reformatting, and inserting numeric CSV data fields
- calculating formulas from numeric CSV data fields and expressions, and inserting the (optionally formatted) result
- and inserting all other CSV fields and variables as required.
A simple example: resolving a numerical expression
We have a simple CSV table about the prices of fruit on sale as an input file.
We want to display the gross sales price together with an advertising text. Unfortunately, we only have the net prices, but the table also contains the sales tax rate.| fruit | net price | sales tax |
|---|---|---|
| apples | 2,00 | 19 |
| pears | 3,00 | 19 |
This command adds a new column named "Special Offer" to the output file.
Then, for each individual row, this is the expression that is assigned to the column content:
As part of the expression we see a fomula that must be calculated and formatted first:
This expression must be resolved and the result become inserted into the new
For this purpose, the value of the "Net price" column is multiplied by a factor derived from the value of the "Sales tax" column. The result is formatted with decimal point and two decimal places for output. This value is embedded in a string that starts with "Only today: ", then comes the formatted number, then at the end is " Euro!".
This will be the output file:
| fruit | net price | sales tax | special offer |
|---|---|---|---|
| Apples | 2,00 | 19 | Only today: 2,38 Euro! |
| Pears | 3,00 | 19 | Only today: 3,57 Euro! |
You can find more about getting CSV field contents at CSV Field Access.
Escaping Special Characters
There are some characters that have special meanings.
[, ] denote a column name or field reference.
(, ) denote a numeric interpretation of a field, or a formula.
{, } denote either a condition expression, or a formatted field.
/ is frequently used as a separator, between conditions, commands or alternatives (and is also a formula operator)
= means an assignment inside a command
...
_ when it stands alone and not as part of another expression, means "nothing". Useful for expressing "empty" when otherwise the surrounding "/" separators would fall together and result in a misleading command construct.
If you need to use one of these characters als literals in text or expression, it must be "escaped". This prevents misinterpretation of the character when the expression is resolved by the application.
This is done by prepending a backslash (\) before them.
So, this: [Fieldname] will be replaced by the text contents of the field "Fieldname".
But this: \[Fieldname\] stands for the literal text "[Fieldname]".
For a literal backslash "\" the backslash must be duplicated: \\.