CSVfox
Leverage Your Data.

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
An expression for the contents of a field results in a string that is inserted into that field.
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.
fruitnet pricesales tax
apples2,0019
pears3,0019
Now we create another column "Special offer".
-add[Special offer]="Only today: {([net price]*(1+([sales tax]/100)),2} Euro!"

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:
Only today: {([Net price]*(1+([Sales tax]/100)),2} Euro!
As part of the expression we see a fomula that must be calculated and formatted first:
{([Net price]*(1+([Sales tax]/100)),2}

This expression must be resolved and the result become inserted into the new [Special offer] column field.
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:

fruitnet pricesales taxspecial offer
Apples2,0019Only today: 2,38 Euro!
Pears3,0019Only 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: \\.

 

Under construction, coming soon