Delimited Files (CSV, TSV, pipe-separated)

Delimited format is the most common input for sqlldr. The column boundaries are inferred from a separator character rather than fixed byte offsets — far simpler to generate from spreadsheets, ETL tools, and database exports.

Basic CSV

LOAD DATA
INFILE 'employees.csv'
APPEND
INTO TABLE hr.employees
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  employee_id,
  first_name,
  last_name,
  email,
  hire_date  DATE "YYYY-MM-DD",
  salary     DECIMAL EXTERNAL
)

FIELDS TERMINATED BY ',' applies to every column that doesn't override it. TRAILING NULLCOLS treats a missing trailing field as NULL rather than an error.

Quoted strings — OPTIONALLY ENCLOSED BY

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

With this, the value "Smith, Jr." is read as Smith, Jr. (comma inside quotes is not a delimiter). Without it, the comma inside the quotes would split the field incorrectly.

• OPTIONALLY means quotes are not required — unquoted fields are fine too.
• Remove OPTIONALLY if every field is always quoted.

-- all fields always quoted
FIELDS TERMINATED BY ',' ENCLOSED BY '"'

-- only specific field quoted
( name  CHAR(60) TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
  code  CHAR(10) TERMINATED BY ',' )

Tab-separated (TSV)

FIELDS TERMINATED BY X'09'       -- hex 09 = ASCII tab
TRAILING NULLCOLS

Or use the keyword:

FIELDS TERMINATED BY WHITESPACE   -- any run of spaces/tabs is the separator

WHITESPACE collapses multiple spaces into one separator — useful for space-padded exports but dangerous if fields can legitimately be empty.

Pipe-separated

FIELDS TERMINATED BY '|'

Single-character delimiters can be any printable character. Multi-character terminators require the hex form or a quoted literal:

FIELDS TERMINATED BY '||'        -- two pipe characters
FIELDS TERMINATED BY X'1C'       -- ASCII file separator

Per-column terminators

Override the global terminator for individual fields:

INTO TABLE hr.events
FIELDS TERMINATED BY ','
(
  event_id    INTEGER EXTERNAL,
  event_type  CHAR(20)  TERMINATED BY ':',   -- colon after type
  payload     CHAR(2000) TERMINATED BY X'0A', -- LF ends the payload
  created_at  DATE "YYYY-MM-DD HH24:MI:SS"
)

Handling nulls

Scenario	Control file solution
Trailing columns missing	TRAILING NULLCOLS
Empty field ,, → NULL	Automatic — an empty delimited field becomes NULL
Literal string \N → NULL	NULLIF col = '\N' (covered in topic 17)
Spaces-only field → NULL	NULLIF col = BLANKS

INTO TABLE hr.employees
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  employee_id,
  manager_id  NULLIF manager_id = BLANKS,   -- "   " becomes NULL
  department_id
)

Handling embedded newlines

If a quoted field spans multiple lines, use a non-newline record terminator:

INFILE 'multiline.csv' "STR x'1E'"  -- ASCII record separator (0x1E)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

Or pre-process the file to replace embedded newlines before loading.

Character set and encoding

For UTF-8 CSV files with non-ASCII characters:

LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'customers_utf8.csv'
APPEND
INTO TABLE crm.customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( customer_id, name CHAR(200), city CHAR(100), country CHAR(60) )

Without CHARACTERSET AL32UTF8, multi-byte characters may be silently mangled or cause field-length errors.

Full example — production CSV load

-- employees_load.ctl
OPTIONS ( ERRORS=100, ROWS=5000, BINDSIZE=8388608, READSIZE=8388608 )
LOAD DATA
CHARACTERSET AL32UTF8
INFILE '/data/exports/employees_20240101.csv'
BADFILE '/data/load_logs/employees_20240101.bad'
DISCARDFILE '/data/load_logs/employees_20240101.dsc'
APPEND
INTO TABLE hr.employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  employee_id   INTEGER EXTERNAL,
  first_name    CHAR(20),
  last_name     CHAR(25),
  email         CHAR(100),
  phone_number  CHAR(20),
  hire_date     DATE "YYYY-MM-DD",
  job_id        CHAR(10),
  salary        DECIMAL EXTERNAL,
  commission_pct DECIMAL EXTERNAL,
  manager_id    INTEGER EXTERNAL,
  department_id INTEGER EXTERNAL
)

sqlldr userid=hr/hr@orcl control=employees_load.ctl log=/data/load_logs/employees_20240101.log

Best practices

• Always use OPTIONALLY ENCLOSED BY '"' for any CSV that might contain quoted strings — costs nothing on unquoted data
• Specify CHARACTERSET explicitly; never rely on NLS_LANG defaults for non-ASCII files
• TRAILING NULLCOLS should be the default; remove it only if you need to error on short rows
• Use FIELDS TERMINATED BY X'09' rather than the string '\t' — the string literal isn't supported
• Test with a small sample first: sqlldr ... ROWS=100 to validate field mapping before loading millions of rows