Target String Format Attributes

Most external connectors obtain data from somewhere and feed it in to the server. By default, this data is assumed to be ordinary CSV. In some cases it isn’t. For example, if the fields are separated with colons rather than commas, you need to set the fmt_field_separator attribute in the target string so the server knows how to split a record into fields.

Attribute settings appear in the target string as a name and a value, separated by whitespace. If multiple attributes are given in the target string, they are separated by commas or newlines. Attribute values may be quoted.

(8.2 onwards) If the input is in JSON format, set fmt_json to 1 and set format to the column definition list. See the Loading JSON reference sheet and the wxjson2csv manual page for more information on the column definition list.

Target String Format Options

Attribute

Type

Default

Description

character_set

int/string

1021 or UTF8

Character encoding of the input data, as defined in SYS.IPE_CHARACTER_SET.

fmt_allow_tz_offset (8.2.1)

boolean

false

Allow time and timestamp fields to have a time zone modifier ±HH[:]NN after them, and correct for it before loading.

fmt_blank_null

boolean

false

Always load blank strings as NULL, even if quoted.

fmt_codepoint_for_unknown

char

none

If a character cannot be converted to the destination column’s encoding, silently replace it with this character.

fmt_currency_symbol

char

none

Numeric fields are allowed to be preceded by this character.

fmt_date_format

string

YYYY-MM-DD

Expected format for DATE fields.

fmt_decimal_point

char

dot (.)

Decimal point character in DECIMAL and NUMERIC fields.

fmt_excluded_fields

int list

none

A space-separated list of field numbers which are expected in the input file but are not in the table. The first field is 1.

fmt_field_separator

char/string

comma (,)

The field separator character or character sequence.

fmt_filename_column

column no.

none

Project this record’s filename into this numbered column.

fmt_filename_null_value (8.2.1)

string

blank string

What NULL looks like when fmt_filename_partitions is used.

fmt_filename_partitions (8.2.1)

column name list

none

Take values for these destination columns from the path components of the filename. See the wxloader man page.

fmt_ignore_extra_fields

boolean

false

If true, records are not rejected for having too many fields.

fmt_implied_dp

boolean

false

DECIMAL fields do not have explicit decimal points. The scale is taken from the destination column.

fmt_int_allow_decimals

boolean

false

If a non-integer number is loaded into an integer column, silently round it rather than giving a conversion error.

fmt_load_no_nulls

boolean

false

Blank fields are loaded as empty strings (string columns), zero (numeric columns) or NULL (date/time columns).

fmt_min_year

int

2000

Two-digit years are assumed to refer to the earliest matching year later than or equal to this one.

fmt_naive_delimiting

boolean

true for script inputs, else false

Do not treat quotes specially. The field separator and record terminator characters never appear in fields - they are always taken as terminators regardless of quoting.

fmt_null_value

string

blank string

If a field matches this string, it is to be loaded as NULL.

fmt_pad_with_nulls

boolean

false

If fewer fields exist in a record than expected, use NULL for the remaining fields rather than giving a conversion error.

fmt_percent_encode

boolean

false for connectors, true for scripts

If true, fields are expected to be unquoted, and any problematic characters (e.g. the field separator) may be represented in hex by %xx. For example, a comma would be encoded as %2C. This is by default how data is fed to external scripts, for ease of parsing by the script.

fmt_preserve_leading_spaces

boolean

false

Don’t skip spaces at the start of an unquoted field.

fmt_record_number_column

column no.

none

Project the record’s number within this file into this column.

fmt_separator_terminated_values

boolean

false

Expect an extra field separator after the last field

fmt_thousand_separator

char

none

Ignore this character if it appears in a numeric field.

fmt_time_format

string

HH:NN:SS

Expected format for TIME fields.

fmt_timestamp_format

string

YYYY-MM-DD HH:NN:SS

Expected format for TIMESTAMP fields.

fmt_trim_varchars

boolean

false

Remove trailing spaces from fields corresponding to VARCHARs.

fmt_truncate_strings

boolean

false

Silently truncate strings too large for destination column.

format

string

automatically generated

wxloader format string to use when parsing records. If set, it overrides all other fmt_* attributes. For more on the wxloader format string, see the wxloader-format man page.

skip_first_record

boolean

false

Skip first record of each file. (Not in Map-Reduce connector.)