Fixed-Width Files

Fixed-width format assigns every field an exact byte range within each record. No separator character — the column position alone identifies the value. Common in mainframe extracts (COBOL copybooks), legacy system dumps, and government data feeds.

Record declaration

INFILE 'employees_fixed.dat'  "FIX 120"   -- every record is exactly 120 bytes

The number after FIX is the record length in bytes. sqlldr reads that many bytes per record, no more, no less. A newline at position 121 is treated as data if it falls within a field.

POSITION syntax

POSITION(start:end)     -- 1-based, inclusive byte range
POSITION(start)         -- single byte at 'start'
POSITION(*+n)           -- n bytes after the end of the previous field
POSITION(*)             -- immediately after the previous field (no gap)

Full fixed-width example

-- employees_fixed.ctl
LOAD DATA
INFILE 'employees_fixed.dat'  "FIX 120"
APPEND
INTO TABLE hr.employees
(
  employee_id   POSITION(1:6)    INTEGER EXTERNAL,
  first_name    POSITION(7:26)   CHAR,
  last_name     POSITION(27:51)  CHAR,
  email         POSITION(52:91)  CHAR,
  phone_number  POSITION(92:106) CHAR,
  hire_date     POSITION(107:116) DATE "YYYY-MM-DD",
  salary        POSITION(117:120) INTEGER EXTERNAL
)

Each column is read from its exact byte range. Trailing spaces within a CHAR field are trimmed by default (suppress with PRESERVE BLANKS).

Relative positioning

Avoid counting bytes by chaining POSITION(*+gap):

(
  record_type  POSITION(1:1)    CHAR,
  order_id     POSITION(2:9)    INTEGER EXTERNAL,
  customer_id  POSITION(10:17)  INTEGER EXTERNAL,
  order_date   POSITION(*+0)    DATE "YYYYMMDD",   -- follows customer_id, no gap
  status       POSITION(*+1)    CHAR(2),            -- 1-byte gap after order_date
  amount       POSITION(*+0:*+12) DECIMAL EXTERNAL
)

POSITION(*) = POSITION(*+0) — immediately after previous field.

FILLER — skipping bytes

FILLER consumes bytes without loading them into any column:

(
  employee_id  POSITION(1:6)    INTEGER EXTERNAL,
  first_name   POSITION(7:26)   CHAR,
  padding      POSITION(27:30)  FILLER,             -- 4 bytes discarded
  last_name    POSITION(31:55)  CHAR,
  reserved     POSITION(106:120) FILLER             -- trailer bytes discarded
)

Handling packed decimal (COBOL)

COBOL COMP-3 (packed decimal) fields are binary, not text. sqlldr can decode them:

(
  record_id  POSITION(1:6)    INTEGER EXTERNAL,
  amount     POSITION(7:12)   DECIMAL(10,2),        -- 6-byte packed decimal, 2 implied decimals
  zoned_qty  POSITION(13:18)  ZONED(6,0)            -- zoned decimal (sign in last nibble)
)

Type	Meaning
INTEGER	Binary integer (2 or 4 bytes)
DECIMAL(p,s)	Packed BCD, p digits, s decimal places
ZONED(p,s)	Zoned decimal (mainframe sign encoding)
FLOAT	IEEE 754 binary float

Always clarify with the source system whether numeric fields are text (EXTERNAL) or binary. Getting this wrong produces garbage values without obvious errors.

UTF-8 and multibyte characters

Fixed-width means byte widths, not character widths. A UTF-8 character can be 1–4 bytes:

-- BAD: name is 40 bytes, not 40 characters
name POSITION(1:40) CHAR

-- GOOD: if the source charset is WE8MSWIN1252 (single-byte), this is safe
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'employees_win1252.dat' "FIX 120"
...

If the source is UTF-8 with multibyte characters, use VAR format or convert to single-byte before loading.

Verifying byte offsets

Use this one-liner to print a ruler over the first record of a fixed file:

# Python 3 ruler
python3 -c "
import sys
with open('employees_fixed.dat','rb') as f:
    rec = f.read(120)
    print(''.join(str(i%10) for i in range(1,121)))
    print(rec.decode('latin1'))
"

Or open in any hex editor: the byte offsets directly map to your POSITION() specs.

Example — COBOL mainframe extract

-- cobol_orders.ctl
LOAD DATA
CHARACTERSET WE8EBCDIC37              -- EBCDIC mainframe encoding
INFILE 'orders_mf.dat' "FIX 200"
APPEND
INTO TABLE warehouse.orders
(
  order_id      POSITION(1:8)     ZONED(8,0),
  order_date    POSITION(9:15)    DATE "YYYYJJJ",    -- Julian date
  ship_date     POSITION(16:22)   DATE "YYYYJJJ",
  customer_id   POSITION(23:32)   DECIMAL(10,0),
  net_amount    POSITION(33:39)   DECIMAL(9,2),
  gross_amount  POSITION(40:46)   DECIMAL(9,2),
  currency_code POSITION(47:49)   CHAR,
  filler        POSITION(50:200)  FILLER
)

Best practices

• Always verify byte offsets against the source system's record layout document before writing the control file
• Use FILLER liberally — future columns in the source layout won't break your load
• Prefer INTEGER EXTERNAL / DECIMAL EXTERNAL unless the source explicitly states binary/packed fields
• For EBCDIC files, set CHARACTERSET to the correct EBCDIC variant (WE8EBCDIC37, WE8EBCDIC1148, etc.)
• Run the load on 10 rows first; inspect the log's field-column map to confirm offsets are right before loading millions of rows