# 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](liberator-obtain-a-container-snapshot-in-a-csv-or-xlsx-file.md).

## 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](liberator-obtain-a-container-snapshot-in-a-csv-or-xlsx-file.md#the-http-request-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](#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](#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](#excel-number-format-syntax), 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](#localtime-formatter) 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&#160; | 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&#160; | 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+. |

[small]#† 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](#excel-number-format-syntax).

### Localtime formatter

You can use the Localtime Formatter together with the [Timestamp](#timestamp-formatter) 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](liberator-obtain-a-container-snapshot-in-a-csv-or-xlsx-file.md#the-http-request-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.

**📌 NOTE**\
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 <q>``Buy``</q>.
* 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 <q>``Sell``</q>.

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;`).

**💡 TIP**\
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](liberator-obtain-a-container-snapshot-in-a-csv-or-xlsx-file.md#building-the-url).

## 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’s Format Cells dialog with a custom format selected**

![excel-number-format-dialog](../../../images/excel-number-format-dialog.png)

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](https://support.microsoft.com/en-US/Excel/number-format-codes-in-excel-for-mac) 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](#string-formatter) (`str:;`) as the `<transformation>` argument. The String formatter takes no arguments and makes no changes to the field value. See the [Examples](#examples) below.

For more information on the `format-field` configuration item, see [Configuring the export format of a field](#configuring-the-export-format-of-a-field) above.

For more information about transforming a field’s value, see [Data transformation syntax](#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;"
```

**💡 TIP**\
For more information on the `dp` (decimal places) transformation formatter, see the [Decimal Places formatter](#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"
```

**💡 TIP**\
For more information on the `str` (String) transformation formatter, see the [String formatter](#string-formatter) above.

---

**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)