Sqlldr tab delim file load issue

Question

I'm trying to load a tab delim file into a table. Pls check the issue below and help.

LOAD DATA
INFILE 'tab1.txt'
BADFILE 'tab1.bad'
DISCARDFILE 'tab1.dsc'
REPLACE INTO TABLE CL_TAB1 
FIELDS TERMINATED BY X'9' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
A,B,C,D,E
)

tab1.txt File is like below

A   B   C   D   E
799 CP  AM  27-12-2017
800 CP      18-05-2017
801             USA

If i don't mention TRAILING NULLCOLS with column names, sqlldr loads only null values into table.
When I listed col names in TRAILING NULLCOLS, sqlldr loads like below in the table.

A   B   C   D   E
799 CP  AM  27-12-2017      (Note: this is ok record)
800 CP  18-05-2017          (Note: 18-05-2017 should be loaded onto D col, but loaded onto C)
801 USA                     (Note: USA should be loaded onto E col, but loaded onto B)

Pls help.

Popeye Popeye · Accepted Answer · 2019-07-02T06:22:19

If you don't mention OPTIONALLY ENCLOSED BY '"' --> SQLLDR is working correctly.

See my example:

DDL

DROP TABLE TAB1;

CREATE TABLE TAB1 (
    A   VARCHAR2(100),
    B   VARCHAR2(100),
    C   VARCHAR2(100),
    D   VARCHAR2(100),
    E   VARCHAR2(100)
);

Tab1.txt

A   B   C   D   E
799 CP  AM  27-12-2017
800 CP      18-05-2017
801             USA

Control file

LOAD DATA
INFILE 'tab1.txt'
BADFILE 'tab1.bad'
DISCARDFILE 'tab1.dsc'
REPLACE INTO TABLE TAB1 
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(
A,B,C,D,E
)

SQLLDR execution

>sqlldr userid=tejash/***** control=tab1.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Tue Jul 2 11:43:33 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4

Table TAB1:
  4 Rows successfully loaded.

Check the log file:
  tab1.log
for more information about the load.

Data in table TAB1

A   B   C   D   E
799 CP  AM  27-12-2017  
800 CP      18-05-2017  
801             USA

The first row of a text file is just a header and If you want to skip the header then you will need to use OPTIONS(skip=1) in control file.

Sqlldr tab delim file load issue

1 Answers