# 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 `/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" ``` **📌 NOTE**\ `"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](../transformer/index.md) Format module. These are described on the **Modules > Formatting** page of the **[Transformer SDK For C Documentation,opts="nofollow"](https://docs.caplin.com/developer/api/transformer_c_sdk/6/)**. You can also use the following additional formatters: * [localtime:](#setting-a-timezone-offset-using-the-localtime-formatter) * [translate:](#translating-field-values-using-the-translate-formatter) * [field:](#setting-formatting-aliases-using-the-field-formatter) (configures formatting aliases) ### 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](https://en.wikipedia.org/wiki/Coordinated_Universal_Time) and have the format: `YYYYmmddHHMMSS` * For the `localtime:` formatter to work correctly, the client must first have sent Liberator the HTTP request parameter `zoneoffset=`; 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 ".." "" ``` where * `` 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 `` part of an `add-translation` entry in `snapshot.conf` * `` is a dot separated string that matches the string in the `prefix` argument of the `translate:` formatter in just one `format-field` item. * `` is a value of a field, where the field’s `format-field` item matches `` * `` is the text (in the language defined by ``) 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 **📌 NOTE**\ 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](https://support.microsoft.com/en-US/Excel/number-format-codes-in-excel-for-mac) 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:** * How can I... [Obtain a container snapshot in a CSV or XLSX file](liberator-obtain-a-container-snapshot-in-a-csv-or-xlsx-file.md)