By default, when you export a container snapshot to a CSV or XLSX format file, the fields have the same format as those in the original record(s). However, to make the fields more readable, you can format them before they’re exported to the file. Here’s how to do this.
Specifying field formats
For each field to be formatted, add a
format-field configuration item to Liberator’s
snapshot.conf file, which is located in
format-field configuration item has the following syntax:
format-field "[lang.]field-name" "format-specifications" ["XLSX-number-format-code"]
langis an optional language code that restricts the application of the formatting rule to a single language. Available since Liberator 7.0.2.
The language code is passed to Liberator as a querystring parameter in the snapshot’s URL e.g. https://myliberator:18081/exportcsv/mysnapshot.csv?…lang=fr
The language code is not restricted to a list of standard country codes and can be any arbitrary sequence of characters. An example use of this flexibility would be defining different formatting rules for different locales (for example,
field-nameis the name of the field.
format-specificationsis a semi-colon separated chain of one or more Transformer formatters to be applied when exporting the field.
The parameter takes the following format:
formatteris the name of a Transformer formatter, and
arg=valueis an argument to the formatter supplied as a name-value pair.
For more information on the format of this parameter, and for a list of available formatters, see Available formatters.
XLSX-number-format-codeis an Excel number code to be applied when exporting the field to an XLSX file. Available since Liberator 6.2.5.
For more information on Excel number codes, see Number format codes on the Microsoft Office website.
To specify an XLSX number format code without changing the underlying value, specify the String formatter without any arguments (
str:;) as the value of the
# Truncate the exported Bid field to 3 decimal places. format-field "Bid" "dp:places=3;" # Truncate the exported Bid field to 3 decimal places. # In XLSX spreadsheets, format the Bid cell as a three-decimal place number. format-field "Bid" "dp:places=3;" "0.000" # Export the Bid field verbatim. # In XLSX spreadsheets, format the Bid cell as a three-decimal place number. format-field "Bid" "str:;" "0.000" # Convert the exported Date field from en_GB locale to en_US locale # if 'lang=en_US' is specified in the querystring. format-field "en_US.Date" "date:from=%d/%m/%Y,to=%m/%d/%Y;"
For a list of formatters, with examples, see Modules > Formatting API in the documentation for the Transformer C SDK.
You can also use the following additional formatters:
You can use the Localtime Formatter, together with the Timestamp Formatter, described in the Transformer SDK For C Documentation, to provide a localised time in an ISO-like timestamp format.
The Localtime Formatter takes no arguments.
format-field "TimeStamp" "date:from=%Y%m%d%H%M%S,to=%s;localtime:;date:from=%s,to=%Y%m%d%H:%M:%S;"
The server timestamp must be in UTC and have the format:
For the Localtime Formatter to work correctly, the client must first have sent Liberator the HTTP request parameter
zoneoffset=<minutes-from-UTC>; for example,
Localisation of day and month names isn’t supported. For example, in a German language locale, “
Thu” (English "Thursday") won’t be translated to “
Don” (German "Donnerstag").
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.
snapshot.conf configuration file, add a Translate Formatter to the specification of the
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:
enpart of each
add-translationitem’s first argument specifies the translation language; in this case,
In the first
en.com.caplin.xyz.1is the value of the
BuySellfield that’s translated to the text “
Similarly, in the second
en.com.caplin.xyz.2is the value of the
BuySellfield that’s translated to the text “
Finally, to make Liberator pick the English translations, make sure the client includes the querystring parameter
lang=en in the HTTP request:
What if you want to translate the BuySell values into French text instead? Just add two more
add-translation entries to
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:
Here’s the syntax of the
add-translation configuration item:
add-translation "lang.prefix.FieldValue" "TranslatedValue"
langidentifies 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
frfor French used above. When an HTTP request for a CSV file is sent to Liberator, the value of its
langparameter must match the
<lang>part of an
prefixis a dot separated string that matches the string in the
prefixargument of the
translate:formatter in just one
FieldValueis a value of a field, where the field’s
TranslatedValueis the text (in the language defined by
<lang>) to be substituted for the field value.
A client can optionally request formatting of a field at run time by specifying the field as an alias. To allow this, you first must configure the field as an alias in Liberator’s
snapshot.conf file, using the Field Formatter in a
format-field item. As an example, assume that you want to optionally format the
Bid field to three decimal places. Then you provide the following configuration:
format-field "fmtBid" "field:fieldname=Bid;dp:places=3;"
Compare this against the example at the start of this page:
format-field "Bid" "dp:places=3;"
format-field item now specifies a field alias called
fmtBid, which is the name of the optionally formatted field with the prefix
fmt. The Field Formatter specifies through its
fieldname argument that
fmtBid is an alias for the
Bid field. The Decimal Place Formatter (dp) converts the value of the Bid field to three decimal places, as previously described.
How do you use the alias? Well, if the client sends Liberator a CSV snapshot request with the
Bid field in its
fields parameter, just the raw values of the field are returned in the CSV file. If instead, the request has the
fmtBid alias in its fields parameter, Liberator obeys the
format-field "fmtBid"… configuration and formats the values of the
Bid field to three decimal places.