Reading and Formatting Numeric Fields

Identifying and Reading Numeric CSV Fields

Reading fields as numeric values
Interpreting values in non-local format
Printing formatted values
Changing the local format

Numeric CSV Fields

Generally, any data field in a CSV table will be handled as text. But it is also possible to extract numeric contents from a field. Then optionally doing some arithmetics with it, and format the result for output.

To make this possible, the field must contain a number in decimal format. (Scientific notation is not supported.)
So, the number shall consist of optionally a minus sign, some digits 0 to 9, and maybe a thousands separator, and/or a decimal separator and decimal places.

It is not required that the field contains only the pure number. Surrounding whitespace is allowed, and also other text before and/or after the numeric expression. In this case, the software will identify and extract the first occurrence of what seems to be the requested numeric information.

Reading fields as numeric values

Interpretation as numeric

In order to tell the software that numeric information shall be extracted, you must put the field identificator between round brackets, in addition to the usual square brackets for field access:
([MyNumericField]) .
This way, the software will identify the "17" in the sentence "This basket contains 17 apples".

Per default, numeric information will be interpreted in your local numbering style ( see also %locale). So, in the US, a comma will be read as a thousands separator and a dot is interpreted as a decimal separator, while in Germany it will be the other way round.

Interpreting values in non-local format

Changing the numbering style

You can also manually define the number format to be read, by specifying the thousands separator, the decimal separator and optionally the number of decimal places.
This information must be inserted after the closing square bracket and before the closing round bracket.
If the number of decimal places is also given, then the detected number will be rounded to these decimal places before further processing.

The order of specifying is always:
Thousands separator, decimal separator, number of decimal places.
For example: ([Total Amount],.2) means: The field "Total Amount" shall be read and its numeric value shall be extracted, using thousands comma and decimal point. When having it, it shall be rounded to two decimal places.

You do not have to specify all parameters. However, you cannot specify a thousands separator without also specifying a decimal separator; if there is only one single separator given, then this is taken as the decimal separator. In this case, no thousands separator is supported.
If there is also a number, this will define the number of decimal places the value will be rounded to. A "0" here means rounding to a whole number.

If you do not specify any separators, but only the number of decimal places, like in ([Total Amount]2), the current locale setting of your installation (country) will be assumed for the separators. So in USA will generally a decimal point be used, while in Germany a decimal comma is standard.

[MyField]: This represents the content of MyField as text, "as is". No numeric interpretation or arithmetics intended.
([MyField]): This finds and extracts numeric content of MyField. Interpretation follows the current locale.
When the contents of [MyField] is "My new Porsche costs $85,000.00 USD!", then ([MyField]) will find "85,000.00" if you reside in the USA locale, where decimal point and thousands comma are in use.
([MyField].,): This finds and extracts numeric content of MyField, interpreting the dot as thousands separator and the comma as decimal separator.
When the contents of MyField is "My new Porsche costs $85.000,00 USD!" this will fit perfectly.
([MyField],3): This extracts numeric content of MyField, interpreting the comma as decimal separator and rounding the value to 3 decimal places. A thousands separator is not given here, so you should be sure that [MyField] does not contain one.

You can theoretically specify nearly all characters as thousands or decimal separators, although only comma or point are commonly used.
However, you might already have suspected that there are limits. The following characters can not be specified as thousands or decimal separators:
All kinds of brackets ( ) [ ] { },
All arithmetic operators (see below): r ! + - * : / % ^ # @ < > ,
All digits 0 1 2 3 4 5 6 7 8 9.

some more examples...

Printing formatted values

Like knowing the correct number format is necessary for successful reading, it is also necessary to give out the resulting values in a specific format. Mostly, currency values like prices will have to be specified with two decimal places. Others, like a weight, may require three decimal places.

The formatting of the output data looks like the one used for reading, but this time the formatted value must be given in additional curly brackets.

So the following will reformat a "Weight" field. It has been read in local format (whichever one that is), but shall now be printed without a thousands separator, but with a decimal comma and three decimal places:
{([Weight]),3}

You can also format the result of a formula (see also Arithmetics) directly.
This is about money, so a decimal point and two decimal places are required:
{([GrossPrice]-[Tax]).2}

Changing the local format

As long as the CSV files follow your local settings, you will not need to specify any number format for reading (except the brackets). If there are only a few numeric fields, you can specify their format directly when reading.

As an alternative, you can change your locale setting with a %locale parameter inside CSVfox. This may be more practical when there are a lot of numeric fields in the CSV file to process, and they all follow an identical format, which is not your current locale (e.g., you are in Germany, but have to work with a CSV table that has points as decimal separators).