Format fields for export to a CSV or XLSX file

By default, when you export a container snapshot to a CSV or XLSX format file, the field values are exported verbatim. To make exported field values more readable, you can use Liberator’s format-field configuration item to transform exported field values and apply Excel number formats to spreadsheet cells.

To learn more about requesting a container snapshot, see Obtaining a container shapshot as a CSV or XSLX file.

Configuring the export format of a field

To configure the export format for a field in a container snapshot, add a format-field configuration item to the file <Framework-root>/global_config/overrides/servers/Liberator/etc/snapshot.conf.

The format-field configuration item has the following syntax:

format-field [<lang>.]<field-name> <transformation> [excel-number-format]

Where:

  • <lang> is an optional prefix that restricts the application of the formatting rule to Snapshot requests that have a matching lang query string parameter. Available since Liberator 7.0.2.

    The matching algorithm supports literal matches only. For example, a lang parameter of 'en' does not match a format-field language prefix of 'en_GB'.

    For more information on the Snapshot module’s lang parameter, see Snapshot module parameters.

  • <field-name> is the name of the field.

  • <transformation> is a string comprising one or more transformation formatters. For the syntax of this parameter, see Data transformation syntax.

  • [excel-number-format] is an optional Excel display format that is applied when exporting the field to an Excel spreadsheet (.xlsx). Available since Liberator 6.2.5.

    For information on the syntax of this parameter, see Excel number-format syntax.

Examples

Transform the value of the Bid field to a 3 decimal place number, and display it in Excel to three decimal places padded with zeros:

format-field Bid "dp:places=3;" "0.000"

For Snapshot requests containing the query string parameter lang=en_US, transform the value of the Date field from day-month-year to month-day-year:

format-field "en_US.Date" "date:from=%d/%m/%Y,to=%m/%d/%Y;"

Data transformation syntax

The <transformation> parameter for format-field consists of one or more transformation formatters. This section describes the formatters supported by the Snapshot module.

Transformation formatters change the exported value of a field. If you’re exporting to a spreadsheet, transformation contrasts with Excel display formatting, which changes how a field’s value is displayed but doesn’t change the underlying value.

Each formatter is specified with the following syntax:

<formatter-name>:[<arg>=<value>][,<arg>=<value>]…;

When multiple formatters are specified, the formatters are applied as a chain, with the output of the first formatter providing the input to the next formatter, and so on until the end of the chain. For an example of chaining formatters, see the Localtime formatter.

Timestamp formatter

The Timestamp formatter (date) transforms a date-time value from one format to another.

Syntax
"from:<formatting-syntax>,to:<formatting-syntax>;"
Name Default Description

from

-

The input format (the format of the field’s date-time value)

to

-

The output format
Date format syntax (standard strftime conversion specifications)
Token  Description

%a

The server locale’s abbreviated weekday name.

%A

The server locale’s full weekday name.

%b

The server locale’s abbreviated month name. Same as %h.

%B

The server locale’s full month name.

%c

The server locale’s appropriate date and time representation.

%C

The century number (the year divided by 100 and truncated to an integer) as a decimal number [00-99].

%d

The day of the month as a decimal number [01,31].

%D

Equivalent to the format %m/%d/%y.

%e

The day of the month as a decimal number [1,31]; a single digit is preceded by a space.

%h

The server locale’s abbreviated month name. Same as %b.

%H

The hour (24-hour clock) as a decimal number [00,23].

%I

The hour (12-hour clock) as a decimal number [01,12].

%j

The day of the year as a decimal number [001,366].

%m

The month as a decimal number [01,12].

%M

The minute as a decimal number [00,59].

%p

'AM' or 'PM' (uppercase), or the equivalent notation in the server’s locale.

%P

'am' or 'pm' (lowercase), or the equivalent notation in the server’s locale.

%r

The time in a.m. and p.m. notation. In the POSIX locale, this is equivalent to the format %I:%M:%S %p.

%R

The time in 24 hour notation (%H:%M).

%s

The time in seconds since the epoch.

%S

The second as a decimal number [00,61].

%t

A tab character.

%T

The time in format %H:%M:%S

%u

ISO 8601 weekday as number (1-7), with Monday as 1 and Sunday as 7. See also %w.

%U

The week number of the year (Sunday as the first day of the week) as a decimal number [00,53].

%V

The week number of the year (Monday as the first day of the week) as a decimal number [01,53]. If the week containing 1 January has four or more days in the new year, then it is considered week 1. Otherwise, it is the last week of the previous year, and the next week is week 1.

%w

The weekday as a decimal number (0-6), with Monday as 1 and Sunday as 0. See also %u.

%W

The week number of the year (Monday as the first day of the week) as a decimal number [00,53]. All days in a new year preceding the first Monday are considered to be in week 0.

%x

The server locale’s appropriate date representation.

%X

The server locale’s appropriate time representation.

%y

The year without century as a decimal number [00,99].

%Y

The year with century as a decimal number.

%Z

The time zone name or abbreviation, or by no bytes if no time zone information exists.

%%

A literal % character.
Date formatting syntax (Caplin extensions to strftime conversion specifications)
Token  Description

%f

The milliseconds (up to 3 digits) in the second, padded with leading zeros. Available in the 'from' parameter in Liberator 7.1.0+, and available in both the 'from' and 'to' parameters in Liberator 7.1.1+.

%J

A timestamp representing milliseconds since the UNIX epoch. Available in the 'from' parameter in Liberator 7.1.0+, and available in both the 'from' and 'to' parameters in Liberator 7.1.1+.

%n

The nanoseconds (up to 9 digits) in the second, padded with leading zeros. Available in the 'from' parameter in Liberator 7.1.0+, and available in both the 'from' and 'to' parameters in Liberator 7.1.1+.

%N†

Refinitiv High precision fractional seconds (000:000:000). Available in the 'from' parameter in Liberator 7.1.0+, and available in both the 'from' and 'to' parameters in Liberator 7.1.1+.

%q‡

The microseconds (up to 6 digits) in the second, padded with leading zeros. Available in the 'from' parameter in Liberator 7.1.0+, and available in both the 'from' and 'to' parameters in Liberator 7.1.1+.

† Renamed from %P to %N in Liberator 7.1.5
‡ Renamed from %u to %q in Liberator 7.1.5

Timestamp example
format-field "en_US.Date" "date:from=%d/%m/%Y,to=%m/%d/%Y;"

In the above example, the value of the Date field will be transformed from a day-month-year format to a month-day-year format when the Snapshot request contains the query string parameter lang=en_US.

Decimal Places formatter

The Decimal Places formatter (dp) transforms a decimal value to a specific number of decimal places.

Decimal Places arguments
Name Default Description

precision

-

Number of required decimal places. Required.

places

-

Alias of 'precision'

mode

truncation

Mode of transformation: 'round' or 'truncation'
Decimal Places example
format-field "Bid" "dp:places=3;"

For further decimal-place examples that include Excel number formatting, see Excel number-format syntax.

Localtime formatter

You can use the Localtime Formatter together with the Timestamp formatter to provide a localised time in an ISO-like timestamp format.

The Localtime formatter takes a UTC timestamp formatted as YYYYmmddHHMMSS and outputs a timestamp in the same format offset by the number of minutes specified by the Snapshot module’s zoneoffset parameter. For more information on the zoneoffset parameter, see Snapshot module parameters.

The Localtime Formatter takes no arguments.

To translate a timestamp to and from the format required by the Localtime formatter, the Localtime formatter is often used in series with the Timestamp formatter. See the example below.

The Timestamp formatter is not locale aware, so using day and month names in an exported timestamp format is not recommended.

Example:

format-field "TimeStamp" "date:from=%Y%m%d%H%M%S,to=%s;localtime:;date:from=%s,to=%Y%m%d%H:%M:%S;"

String formatter

The String formatter (str) takes no arguments and makes no changes to the field value.

Use this formatter as a 'passthrough' formatter when you want to specify an Excel number-format to format-field without specifying a transformation.

For example, to display the 'Bid' field value to three decimal places in Excel, but retain the original field value for use in later calculations, use the configuration below:

format-field Bid "str:;" "0.000"

Translate formatter

You can use the Translate Formatter to translate the values of individual fields into text in a specified language before they are exported to the CSV file.

For example, assume the records to be exported contain a field called BuySell, whose value is 1 when the record contains a Buy price, and 2 when the record contains a Sell price. In the exported CSV file, we want this field to contain the more meaningful text "Buy" or "Sell" rather than the value 1 or 2. Here’s how to set this up.

In the snapshot.conf configuration file, add a Translate Formatter to the specification of the BuySell field:

format-field "BuySell" "translate:prefix=com.caplin.xyz.;"

The Translate Formatter’s prefix argument uniquely identifies a set of add-translation items (in snapshot.conf) that specify the translations to be applied to the field:

add-translation "en.com.caplin.xyz.1" "Buy"
add-translation "en.com.caplin.xyz.2" "Sell"

Notes on the example above:

  • The en part of each add-translation item’s first argument specifies the translation language; in this case, en for "English".

  • In the first add-translation item, the 1 in en.com.caplin.xyz.1 is the value of the BuySell field that’s translated to the text “Buy”.

  • Similarly, in the second add-translation item, the 2 in en.com.caplin.xyz.2 is the value of the BuySell field that’s translated to the text “Sell”.

Finally, to make Liberator pick the English translations, make sure the client includes the query string parameter lang=en in the HTTP request:

https://myliberator:18081/exportcsv/mysnapshot.csv?sessionid=1fP_oSRrY4Al&export=/CONTAINER/SimpleContainer&fields=BuySell&lang=en

What if you want to translate the BuySell values into French text instead? Just add two more add-translation entries to snapshot.conf:

add-translation "fr.com.caplin.xyz.1" "Acheter"
add-translation "fr.com.caplin.xyz.2" "Vendre"

Now the client must change the value of the lang parameter in the HTTP request to fr, like this:

https://myliberator:18081/exportcsv/mysnapshot.csv?sessionid=1fP_oSRrY4Al&export=/CONTAINER/SimpleContainer&fields=BuySell&lang=fr

Here’s the syntax of the add-translation configuration item:

add-translation "<lang>.<prefix>.<field-value>" "<translated-value>"

Where:

  • <lang> identifies the language into which the text is being translated. It can be any string that uniquely identifies the language. For instance, you could use a two letter language code that follows the ISO 639.1 standard, such as the fr for French used above. When an HTTP request for a CSV file is sent to Liberator, the value of its lang query string parameter must match the <lang> part of an add-translation entry in snapshot.conf

  • <prefix> is a dot-separated string that matches the string in the prefix argument of the translate: formatter in just one format-field configuration item.

  • <field-value> is a value of a field, where the field’s format-field configuration item matches <prefix>.

  • <translated-value> is the text (in the language defined by <lang>) to be substituted for the field value.

Field (alias) formatter

You can use the field formatter to specify that a <field-name> parameter passed to format-field is not a real field name but an alias for another field. This is useful for creating multiple and optional field formats.

For example, to allow the front-end application to control whether the Bid field is formatted, don’t define a format for the Bid field directly, instead define a format for an alias (fmtBid) of the Bid field like this:

format-field "fmtBid" "field:fieldname=Bid;dp:places=3;"

When a front-end application requests that the Bid field is exported, then the Bid value won’t be formatted. When a front-end application requests that the fmtBid field is exported, then the Bid value will be transformed to three decimal places (dp:places=3;).

For more information on building a container snapshot request, including how to specify which fields are exported, see Requesting a container snapshot using SLJS and CT.

Excel number-format syntax

Since: Liberator 6.2.5

Excel’s number format codes provide control over the display of a cell’s value without affecting the cell’s underlying value.

excel number format dialog
Excel’s Format Cells dialog with a custom format selected

The custom formatting syntax provides fine control over the display of decimal places, the thousands separator, conditional colours, and dates. For more about Excel number format codes, see Number format codes on the Microsoft website.

Liberator’s format-field configuration item has an optional third parameter that allows you to specify an Excel custom format code, which is applied to the field’s cell when the field is exported to an Excel spreadsheet (.xlsx):

format-field <field> <transformation> [excel-number-format]

As the optional third parameter of format-field, the [excel-number-format] parameter cannot be specified without also specifying the <transformation> parameter. If you don’t want to transform the field’s value, specify the String formatter (str:;) as the <transformation> argument. The String formatter takes no arguments and makes no changes to the field value. See the Examples below.

For more information on the format-field configuration item, see Configuring the export format of a field above.

For more information about transforming a field’s value, see Data transformation syntax.

Examples

Transform the Bid field to a 3 decimal place number, but don’t specify an Excel number format code (let Excel choose how to format it):

format-field Bid "dp:places=3;"
For more information on the dp (decimal places) transformation formatter, see the Decimal Places formatter above.

Transform the Bid field to a 3 decimal place number, and display it in Excel to three decimal places padded with zeros:

format-field Bid "dp:places=3;" "0.000"

Don’t transform the value of the Bid field, but display it in Excel to three decimal places padded with zeros:

format-field Bid "str:;" "0.000"
For more information on the str (String) transformation formatter, see the String formatter above.

See also: