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 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.

Contents:

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 <Framework-root>/global_config/overrides/servers/Liberator/etc.

The format-field configuration item has the following syntax:

format-field "[lang.]field-name" "format-specifications" ["XLSX-number-format-code"]

Where:

  • lang is 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, en_US and en_GB).

  • field-name is the name of the field.

  • format-specifications is a semi-colon separated chain of one or more Transformer formatters to be applied when exporting the field.

    The parameter takes the following format:

    formatter:[arg=value][,arg=value]…;[formatter:[arg=value][,arg=value]…;]…

    Where formatter is the name of a Transformer formatter, and arg=value is 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-code is 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 format-specifications parameter.

Examples:

# 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;"

Available formatters

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:

Localtime

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.

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;"

Notes:

  • The server timestamp must be in UTC and have the format: YYYYmmddHHMMSS
  • For the Localtime Formatter to work correctly, the client must first have sent Liberator the HTTP request parameter zoneoffset=<minutes-from-UTC>; for example, zoneoffset=60
  • 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").

Translate

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 querystring 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.FieldValue" "TranslatedValue"

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 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 item.
  • FieldValue is a value of a field, where the field's format-field item matches <prefix> 
  • TranslatedValue is the text (in the language defined by <lang>) to be substituted for the field value.

Field

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;"

The 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.


See also: