Home | Table of Contents | Data Formats | CloverETL 4.6.1
--- | --- | ---
Prev | Records and Fields | Next

### Data Formats

Date and Time Format
Numeric Format
Boolean Format
String Format

Sometimes, a Format may be defined for parsing and formatting data values.

1. Any date can be parsed and/or formatted using date and time format pattern. See Date and Time Format.

   Parsing and formatting can also be influenced by Locale (names of months, order of day or month information, etc.) and Time Zone.

2. Any numeric data type (`decimal`, `integer`, `long`, `number`) can be parsed and/or formatted using numeric format pattern. See Numeric Format.

   Parsing and formatting can also be influenced by locale (e.g., decimal dot or decimal comma, etc.). See Locale.

3. Any boolean data type can be parsed and formatted using boolean format pattern. See Boolean Format.

4. Any string data type can be parsed using string format pattern. See String Format.

| Note
Remember that both date and time formats and numeric formats are displayed using system Locale value or the Locale specified in the `defaultProperties` file, unless another Locale is explicitly specified. For more information on how Locale may be changed in the `defaultProperties` see Chapter 18, Engine Configuration.

#### Date and Time Format

A formatting string describes how date/time values should be read and written from(to) string representation (flat files, human readable output, etc.). Formatting and parsing of dates is also affected by Locale and Time Zone.

A format can also specify an engine which CloverETL will use by specifying a prefix (see below). There are two built-in date engines available: standard Java and third-party Joda (http://joda-time.sourceforge.net).

Table 32.2. Available date engines

Date engine | Prefix | Default | Description | Example
--- | --- | --- | --- | ---
Java | `java:` | yes - when no prefix is given | Standard Java date implementation. Provides lenient, error-prone and full-featured parsing and writing. It has moderate speed and is generally a good choice unless you need to work with large quantities of date/time fields. For advanced study please refer to Java SimpleDateFormat documentation. | `java:yyyy-MM-dd HH:mm:ss`
Joda | `joda:` |  | An improved third-party date library. Joda is more strict on input data accuracy when parsing and does not work well with time zones. Joda provides a 20-30% speed increase compared to standard Java. Joda may be convenient for AS/400 machines. On the other hand, Joda is unable to read time zone expressed with any number of `z` letters and/or at least three `Z` letters in a pattern. For further reading please visit the project site at http://joda-time.sourceforge.net). | `joda:yyyy-MM-dd HH:mm:ss`
`iso-8601` |  | This format offers support to parse and print dates and times formatted according to ISO-8601. The standard provides more ways of time expression, but usually the form `YYYY-MM-DDThh:mm:ss±hh:mm` is used - especially in case of data interchange using XML or JSON documents. For additional information on the standard see Wikipedia article on ISO-8601 | There are three possible format values: `iso-8601:dateTime` for timestamps `iso-8601:date` for simple dates without time information `iso-8601:time` for simple times without date information

Please note, that actual format strings for Java and Joda are almost 100% compatible with each other - see tables below.

| Important
The format patterns described in this section are used both in metadata as the Format property and in CTL.

At first, we provide the list of pattern syntax, the rules and the examples of its usage for Java:

Table 32.3. Date Format Pattern Syntax (Java)

Letter | Date or Time Component | Presentation | Examples
--- | --- | --- | ---
G | Era designator | Text | AD
y | Year | Year | 1996; 96
Y | Week year | Year | 2009; 09
M | Month in year | Month | July; Jul; VII; 07; 7
w | Week in year | Number | 27
W | Week in month | Number | 2
D | Day in year | Number | 189
d | Day in month | Number | 10
F | Day of week in month | Number | 2
E | Day in week | Text | Tuesday; Tue
u | Day number of week (1 = Monday, ..., 7 = Sunday) | Number | 1
a | Am/pm marker | Text | PM
H | Hour in day (0-23) | Number | 0
k | Hour in day (1-24) | Number | 24
K | Hour in am/pm (0-11) | Number | 0
h | Hour in am/pm (1-12) | Number | 12
m | Minute in hour | Number | 30
s | Second in minute | Number | 55
S | Millisecond | Number | 970
z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00
Z | Time zone | RFC 822 time zone | -0800
X | Time zone | ISO 8601 time zone | -08; -0800; -08:00
' | Escape for text/id | Delimiter | (none)
'' | Single quote | Literal | '

The number of symbol letters you specify also determines the format. For example, if the "zz" pattern results in "PDT", then the "zzzz" pattern generates "Pacific Daylight Time". The following table summarizes these rules:

Table 32.4. Rules for Date Format Usage (Java)

Presentation | Processing | Number of Pattern Letters | Form
--- | --- | --- | ---
Text | Formatting | 1 - 3 | short or abbreviated form, if one exists
Text | Formatting | >= 4 | full form
Text | Parsing | >= 1 | both forms
Year | Formatting | 2 | truncated to 2 digits
Year | Formatting | 1 or >= 3 | interpreted as Number.
Year | Parsing | 1 | interpreted literally
Year | Parsing | 2 | interpreted relative to the century within 80 years before or 20 years after the time when the `SimpleDateFormat` instance is created
Year | Parsing | >= 3 | interpreted literally
Month | Both | 1-2 | interpreted as a Number
Month | Parsing | >= 3 | interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name)
Month | Formatting | 3 | interpreted as Text (using Roman numbers, or abbreviated month name - if exists)
Month | Formatting | >= 4 | interpreted as Text (full month name)
Number | Formatting | minimum number of required digits | shorter numbers are padded with zeros
Number | Parsing | number of pattern letters is ignored (unless needed to separate two adjacent fields) | any form
General time zone | Both | 1-3 | short or abbreviated form, if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59])
General time zone | Both | >= 4 | full form, , if has a name. Otherwise, GMT offset value (GMT[sign][[0]0-23]:[00-59])
General time zone | Parsing | >= 1 | RFC 822 time zone form is allowed
RFC 822 time zone | Both | >= 1 | RFC 822 4-digit time zone format is used ([sign][0-23][00-59])
RFC 822 time zone | Parsing | >= 1 | General time zone form is allowed

Examples of date format patterns and resulting dates follow:

Table 32.5. Date and Time Format Patterns and Results (Java)

Date and Time Pattern | Result
--- | ---
"yyyy.MM.dd G 'at' HH:mm:ss z" | 2001.07.04 AD at 12:08:56 PDT
"EEE, MMM d, ''yy" | Wed, Jul 4, '01
"h:mm a" | 12:08 PM
"hh 'o''clock' a, zzzz" | 12 o'clock PM, Pacific Daylight Time
"K:mm a, z" | 0:08 PM, PDT
"yyyyy.MMMMM.dd GGG hh:mm aaa" | 02001.July.04 AD 12:08 PM
"EEE, d MMM yyyy HH:mm:ss Z" | Wed, 4 Jul 2001 12:08:56 -0700
"yyMMddHHmmssZ" | 010704120856-0700
"yyyy-MM-dd'T'HH:mm:ss.SSSZ" | 2001-07-04T12:08:56.235-0700

The described format patterns are used both in metadata as the Format property and in CTL.

Now the list of format pattern syntax for Joda follows:

Table 32.6. Date Format Pattern Syntax (Joda)

Symbol | Meaning | Presentation | Examples
--- | --- | --- | ---
G | Era designator | Text | AD
C | Century of era (>=0) | Number | 20
Y | Year of era (>=0) | Year | 1996
y | Year | Year | 1996
x | Week of weekyear | Year | 1996
M | Month of year | Month | July; Jul; 07
w | Week of year | Number | 27
D | Day of year | Number | 189
d | Day of month | Number | 10
e | Day of week | Number | 2
E | Day of week | Text | Tuesday; Tue
a | Halfday of day | Text | PM
H | Hour of day (0-23) | Number | 0
k | Clockhour of day (1-24) | Number | 24
K | Hour of halfday (0-11) | Number | 0
h | Clockhour of halfday (1-12) | Number | 12
m | Minute of hour | Number | 30
s | Second of minute | Number | 55
S | Fraction of second | Number | 970
z | Time zone | Text | Pacific Standard Time; PST
Z | Time zone offset/id | Zone | -0800; -08:00; America/Los_Angeles
' | Escape for text/id | Delimiter | (none)
'' | Single quote | Literal | '

The number of symbol letters you specify also determines the format. The following table summarizes these rules:

Table 32.7. Rules for Date Format Usage (Joda)

Presentation | Processing | Number of Pattern Letters | Form
--- | --- | --- | ---
Text | Formatting | 1 - 3 | short or abbreviated form, if one exists
Text | Formatting | >= 4 | full form
Text | Parsing | >= 1 | both forms
Year | Formatting | 2 | truncated to 2 digits
Year | Formatting | 1 or >= 3 | interpreted as Number.
Year | Parsing | >= 1 | interpreted literally
Month | Both | 1-2 | interpreted as a Number
Month | Parsing | >= 3 | interpreted as Text (using Roman numbers, abbreviated month name - if exists, or full month name)
Month | Formatting | 3 | interpreted as Text (using Roman numbers, or abbreviated month name - if exists)
Month | Formatting | >= 4 | interpreted as Text (full month name)
Number | Formatting | minimum number of required digits | shorter numbers are padded with zeros
Number | Parsing | >= 1 | any form
Zone name | Formatting | 1-3 | short or abbreviated form
Zone name | Formatting | >= 4 | full form
Time zone offset/id | Formatting | 1 | Offset without a colon between hours and minutes
Time zone offset/id | Formatting | 2 | Offset with a colon between hours and minutes
Time zone offset/id | Formatting | >= 3 | Full textual form like this: "Continent/City"
Time zone offset/id | Parsing | 1 | Offset without a colon between hours and minutes
Time zone offset/id | Parsing | 2 | Offset with a colon between hours and minutes

| Important
Remember that parsing with any number of "z" letters is not allowed. And neither parsing with the number of "Z" letters greater than or equal to 3 is allowed.

See information about data types in metadata and CTL (CTL2):

- Data Types in Metadata

- Data Types in CTL2

They are also used in CTL functions. See:

- Conversion Functions

- Date Functions

- String Functions

#### Numeric Format

Scientific Notation
Binary Formats

When a text is parsed as any numeric data type or any numeric data type should be formatted to a text, format pattern can be specified. If no format pattern is specified, empty pattern is used and numbers still get parsed and formatted to text.

There are differences in text parsing and number formatting between cases with empty pattern and pattern specified.

1. No pattern and default locale

   - Used when pattern is empty and no locale is set.

   - Javolution `TypeFormat` is used for parsing

   - Formatting uses Java's `toString()` function (e.g. `Integer.toString()`)

   - Parsing uses Javolution library. It is typically faster than standard Java library but is more strict: parsing "10,00" as number fails, parsing "10.00" as integer fails. The expected format for number type is <decimal>{'.'<fraction>}{'E|e'<exponent>}.

2. A pattern or locale is set (the format from the documentation is used)

   - DecimalFormat for formatting and parsing.

   - Parsing depends on pattern, but e.g. 10,00 is parsed as 1000 (with empty pattern and US locale) and 10.00 will be parsed as valid integer (with value 10)

Parsing and formatting are locale sensitive.

In CloverETL, Java decimal format is used.

Table 32.8. Numeric Format Pattern Syntax

Symbol | Location | Localized? | Meaning
--- | --- | --- | ---
# | Number | Yes | Digit, zero shows as absent
0 | Number | Yes | Digit
. | Number | Yes | Decimal separator or monetary decimal separator
- | Number | Yes | Minus sign
, | Number | Yes | Grouping separator
E | Number | Yes | Separates mantissa and exponent in scientific notation. Need not be quoted in prefix or suffix.
; | Subpattern boundary | Yes | Separates positive and negative subpatterns
% | Prefix or suffix | Yes | Multiply by 100 and show as percentage
‰ (\u2030) | Prefix or suffix | Yes | Multiply by 1000 and show as per mille value
¤ (\u00A4) | Prefix or suffix | No | Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator.
' | Prefix or suffix | No | Used to quote special characters in a prefix or suffix, for example, "'#'#" formats 123 to "#123". To create a single quote itself, use two in a row: "# o''clock".

- Both prefix and suffix are Unicode characters from \u0000 to \uFFFD, including the margins, but excluding special characters.

Format pattern composes of subpatterns, prefixes, suffixes, etc. in the way shown in the following table:

Table 32.9. BNF Diagram

Format | Components
--- | ---
pattern | subpattern{;subpattern}
subpattern | {prefix}integer{.fraction}{suffix}
prefix | '\\u0000'..'\\uFFFD' - specialCharacters
suffix | '\\u0000'..'\\uFFFD' - specialCharacters
integer | '#'* '0'* '0'
fraction | '0'* '#'*

Explanation of these symbols follow:

Table 32.10. Used Notation

Notation | Description
--- | ---
X* | 0 or more instances of X
(X | Y) | either X or Y
X..Y | any character from X up to Y, inclusive
S - T | characters in S, except those in T
{X} | X is optional

| Important
The grouping separator is commonly used for thousands, but in some countries it separates ten-thousands. The grouping size is a constant number of digits between the grouping characters, such as 3 for 100,000,000 or 4 for 1,0000,0000. If you supply a pattern with multiple grouping characters, the interval between the last one and the end of the integer is the one that is used. So "#,##,###,####" == "######,####" == "##,####,####".

Remember also that formatting is locale sensitive. See the following table in which results are different for different locales:

Table 32.11. Locale-Sensitive Formatting

Pattern | Locale | Result
--- | --- | ---
###,###.### | en.US | 123,456.789
###,###.### | de.DE | 123.456,789
###,###.### | fr.FR | 123 456,789

| Note
For a deeper look on handling numbers, consult the official Java documentation of NumberFormat, and DecimalFormat.

| Space as group separator
If locale with space as group separator is used, there should be a hard space (char 160) between digits to parse the number correctly.

##### Scientific Notation

Numbers in scientific notation are expressed as the product of a mantissa and a power of ten.

For example, `1234` can be expressed as `1.234 x 103`.

The mantissa is often in the range `1.0 <= x < 10.0`, but it need not be.

Numeric data types can be instructed to format and parse scientific notation only via a pattern. In a pattern, the exponent character immediately followed by one or more digit characters indicates scientific notation.

Example: "0.###E0" formats the number 1234 as "1.234E3".

Examples of numeric pattern and results follow:

Table 32.12. Numeric Format Patterns and Results

Value | Pattern | Result
--- | --- | ---
1234 | 0.###E0 | 1.234E3
12345 | ##0.#####E0 [1] | 12.345E3
123456 | ##0.#####E0[1] | 123.456E3
1234567 | ##0.#####E0[1] | 1.234567E6
12345 | #0.#####E0 [2] | 1.2345E4
123456 | #0.#####E0[2] | 12.3456E4
1234567 | #0.#####E0[2] | 1.234567E6
0.00123 | 00.###E0 [3] | 12.3E-4
123456 | ##0.##E0 [4] | 12.346E3
[1] Maximum number of integer digits is 3, minimum number of integer digits is 1, maximum is greater than minimum, thus exponent will be a multiplicate of three (maximum number of integer digits) in each of the cases. [2] Maximum number of integer digits is 2, minimum number of integer digits is 1, maximum is greater than minimum, thus exponent will be a multiplicate of two (maximum number of integer digits) in each of the cases. [3] Maximum number of integer digits is 2, minimum number of integer digits is 2, maximum is equal to minimum, minimum number of integer digits will be achieved by adjusting the exponent. [4] Maximum number of integer digits is 3, maximum number of fraction digits is 2, number of significant digits is sum of maximum number of integer digits and maximum number of fraction digits, thus, the number of significant digits is as shown (5 digits).

##### Binary Formats

The table below presents a list of available formats:

Table 32.13. Available Binary Formats

Type | Name | Format | Length
--- | --- | --- | ---
integer | `BIG_ENDIAN` | two's-complement, big-endian | variable
`LITTLE_ENDIAN` | two's-complement, little-endian
`PACKED_DECIMAL` | packed decimal
floating-point | `DOUBLE_BIG_ENDIAN` | IEEE 754, big-endian | 8 bytes
`DOUBLE_LITTLE_ENDIAN` | IEEE 754, little-endian
`FLOAT_BIG_ENDIAN` | IEEE 754, big-endian | 4 bytes
`FLOAT_LITTLE_ENDIAN` | IEEE 754, little-endian

The floating-point formats can be used with `numeric` and `decimal` datatypes. The integer formats can be used with `integer` and `long` datatypes. The exception to the rule is the `decimal` datatype, which also supports integer formats (`BIG_ENDIAN`, `LITTLE_ENDIAN` and `PACKED_DECIMAL`). When an integer format is used with the `decimal` datatype, implicit decimal point is set according to the Scale attribute. For example, if the stored value is 123456789 and Scale is set to 3, the value of the field will be 123456.789.

To use a binary format, create a metadata field with one of the supported datatypes and set the Format attribute to the name of the format prefixed with `"BINARY:"`, e.g. to use the `PACKED_DECIMAL` format, create a decimal field and set its Format to `"BINARY:PACKED_DECIMAL"` by choosing it from the list of available formats.

For the fixed-length formats (double and float) also the Size attribute must be set accordingly.

Currently, binary data formats can only be handled by ComplexDataReader and the deprecated FixLenDataReader.

#### Boolean Format

Format for boolean data type specified in Metadata consists of up to four parts separated from each other by the same delimiter.

This delimiter must also be at the beginning and the end of the Format string. On the other hand, the delimiter must not be contained in the values of the boolean field.

| Important
If you do not use the same character at the beginning and the end of the Format string, the whole string will serve as the regular expression for the `true` value. The default values (`false|F|FALSE|NO|N|f|0|no|n`) will be the only ones that will be interpreted as `false`. Values that match neither the Format regular expression (interpreted as `true` only) nor the mentioned default values for `false` will be interpreted as error. In such a case, graph would fail.

If we symbolically display the format as:

`/A/B/C/D/`

the meaning of each part is as follows:

1. If the value of the boolean field matches the pattern of the first part (`A`) and does not match the second part (`B`), it is interpreted as `true`.

2. If the value of the boolean field does not match the pattern of the first part (`A`), but matches the second part (`B`), it is interpreted as `false`.

3. If the value of the boolean field matches both the pattern of the first part (`A`) and, at the same time, the pattern of the second part (`B`), it is interpreted as `true`.

4. If the value of the boolean field matches neither the pattern of the first part (`A`), nor the pattern of the second part (`B`), it is interpreted as error. In such a case, the graph would fail.

All parts are optional, however, if any of them is omitted, all of the others that are at its right side must also be omitted.

If the second part (`B`) is omitted, the following default values are the only ones that are parsed as boolean `false`:

`false|F|FALSE|NO|N|f|0|no|n`

If there is not any Format, the following default values are the only ones that are parsed as boolean `true`:

`true|T|TRUE|YES|Y|t|1|yes|y`

- The third part (`C`) is a formatting string used to express boolean `true` for all matched strings. If the third part is omitted, either the `true` word is used (if the first part (`A`) is complicated regular expression), or the first substring from the first part is used (if the first part is a serie of simple substrings separated by pipe, e.g.: `Iagree|sure|yes|ok` - all these values would be formatted as `Iagree`).

- The fourth part (`D`) is a formatting string used to express boolean `false` for all matched strings. If the fourth part is omitted, either the `false` word is used (if the second part (`B`) is complicated regular expression), or the first substring from the second part is used (if the second part is a serie of simple substrings separated by pipe, e.g.: `Idisagree|nope|no` - all these values would be formatted as `Idisagree`).

#### String Format

Such string pattern is a regular expression that allows or prohibits parsing of a string.

The combo box offers several pre-filled regular expressions.

The last option (excel:raw) serves to read more precise values from `.xlsx` files. See documentation on SpreadsheetDataReader.

Example 32.1. String Format

If an input file contains a string field and Format property is `\\w{4}` for this field, only the string whose length is 4 will be parsed.

Thus, when a Format property is specified for a string, Data policy may cause fail of the graph (if Data policy is `Strict`).

If Data policy is set to `Controlled` or `Lenient`, the records in which this string value matches the specified Format property are read, the others are skipped (either sent to Console or to the rejected port).

Prev | Up | Next
Data Types in Metadata | Home | Table of Contents | Locale and Locale Sensitivity