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.

You define the field formats in a Liberator configuration file called snapshot.conf In the Deployment Framework, this file is located in <Framework-root>/global_config/overrides/servers/Liberator/etc/

For each field to be formatted, add to snapshot.conf a format-field configuration item with the following syntax:

format-field "field-name" "CSV-format-specifications" "XLSX-format"
"XLSX-format" is optional and is only supported by Linux builds of Liberator 6.2.5 and later.

For example, to format the Bid field to three decimal places, specify the following configuration:

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

Formatting fields for export to a CSV format file

In format-field "field-name" "CSV-format-specifications" "XLSX-format"

CSV-format-specifications is a string of one or more formats to be applied to the container snapshot when it’s exported to a CSV format file. The string takes the form:

format1;format2;format3;....formatN;

Each format takes the form:

format-name:argument1=value,argument2=value,...argumentN=value

or, if the formatter doesn’t take any arguments, just:

format-name:

For example, you can format a fractional value into a decimal using the frac: formatter, and then use the dp: formatter to round the result to five decimal places:

format-field "Bid" "frac:;dp:places=5,mode=round;"

Available CSV formatters

You can use any of the formatters that are available in Transformer’s Format module. These are described on the Modules > Formatting page of the Transformer SDK For C Documentation.

You can also use the following additional formatters:

Setting a timezone offset using the localtime: formatter

You can use the localtime: formatter, together with the date: timestamp formatter described in the Transformer SDK For C Documentation, to provide a localised time in an ISO-like timestamp format.

localtime: 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").

Translating field values using the translate: formatter

You can 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"

  • 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 puts the 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 add-translation:

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.

Setting formatting aliases using the field: formatter

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 dp: formatter 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.

Formatting fields for export to an XLSX format file

Available from Liberator 6.2.5

In format-field "field-name" "CSV-format-specifications" "XLSX-format"

XLSX-format is an Excel number format code. When the field is exported to an XLSX format file, this format code determines the format of all the cells containing values of field-name. For more about Excel number format codes, see Number format codes on the Microsoft website.

If you want to format the field before exporting it to an XLSX file, but don’t need to format it for export to CSV, just specify CSV-format-specifications as str:; For example:

format-field "Bid" "str:;" "#,###.000_)"

See also: