Fixed-length format definitions

Fixed-length format files must have a format definition. This topic includes examples of typical fixed-length format definitions for typical data types.

End-of-record

When fixed-format records end in a newline character, no action is required. Newline is the default end-of-record delimiter. When there is no record separator, use single quotation marks side by side, as in the following example:
RecordDelim ''
An end-of-record delimiter is required, even for the last record.

The RecordDelim option specifies a literal sequence of up to 8 bytes, which does not translate common escape representations or support functions such as char(8).

Record length

The record length is optional, but it can provide feedback that the format definition has the correct length. The length excludes the end-of-record delimiter. An example follows:
Recordlength 72

Skipping fields

The following clause skips 4 bytes:
“filler char(4)  bytes 4”
However, the preferred method is to indicate that the field is being skipped, as in the following example:
“filler fld_name char(4) bytes 4”

Temporal values

Temporal values in fixed-length format files often omit delimiters. The following table shows clauses that are used to load dates, times, and time stamps without delimiters.
Table 1. Temporal values
Data type Value Format clause
date 20101231 date1 date YMD'' bytes 8
time 231559 time1 time(6) 24hour '' bytes 6
timestamp 0101231231559 stamp1 timestamp(6) 24hour '' bytes 14
timestamp 20101231231559000001 Load as char(24) and then use insert-select statement

to_timestamp(col,'YYYYMMDDHH24MISSUS')
date 2010-12-31 date2 date YMD'-' bytes 10
time 23.15.59 time2 time(6) 24hour '.' bytes 8
timestamp 2010-12-31 23:15:59 tms2 timestamp(6) YMD '-' 24hour ':' bytes 19
timestamp 2010-12-31 23:15:59.0001 tms3 timestamp(6) YMD '-' 24hour ':' bytes 26
timetz 12:30:45+03:00 Tz1 TIMETZ(6) 24HOUR ':' bytes 14
timetz 123045+-0300 Load as char(11) and then use insert-select statement

(substring(col1,1,2)||':'|| substring(col1,3,2)||':'||substring(col1,5,5)||':'|| substring(col1,10,2))::timetz

Numeric values

The following table shows numeric values.
Table 2. Numeric values
Data type Value Format clause
integer 32767 int1 int2 bytes 5
int8 9123456789123456 int2 int8 bytes 16
numeric 2315.59 num1 numeric(6,2) bytes 7
numeric 231559 Load as char(6) and then use insert-select statement (col/100)::numeric(6,2)
floating 1.2345678 flt1 floating bytes 9
floating 12345678 Load as char(8) and then use insert-select statement(substring(col1,1,1)||'.'||substring(col1,2,7))::float
double 1.2345678 flt1 double bytes 9
double 12345678 Load as char(8) and then use insert-select statement (substring(col1,1,1)||'.'||substring(col1,2,7))::double

Logical values

The following table shows logical values.
Table 3. Logical values
Data type Value Format clause
boolean Y or y, N or n BOOL Y_N BYTES 1
boolean 1, 0 BOOL 1_0 BYTES 1
boolean T or t, F or f BOOL T_F BYTES 1

Null values

Fixed-length format files typically use "magic" values to represent nulls. Adding a nullif clause to any specification allows the column to be checked for null. A nullif clause has the following parts:
  • The keyword nullif
  • The column reference
  • The test expression
As an example, a file specification where field1 is a date and is considered null if it has the value ’99991231’ has the following characteristics:
  • The nullif specification is as follows: 
    “nullif &=’99991231’”
  • The entire specification is as follows: 
    “fld1 date YMD'' bytes 8 nullif &=’99991231’”
  • All format specifications support the nullif clause.

In addition to supporting &=, which evaluates to "string must exactly match," the nullif clause supports &&=, which allows substring matching. This is useful in cases where the string might occur anywhere in a field with space padding. For example, nullif &&=’N’ matches the different expressions “ N “, “N “, and “ N”.

The following table shows null values:
Table 4. Null values
Data type Null value Format clause
boolean ' ' (1 space) BOOL Y_N BYTES 1 NULLIF &=' '(1 space)
date 000000 DATE YMD '' BYTES 6 NULLIF &='000000'
int '      ' (6 spaces) INT BYTES 6 NULLIF &='      ' (6 spaces)