Skipping data fields while loading delimited data using SQLLDR

Question

Consider below scenario:

Table T1 (f1, f2, f3);

Data files:

a|b|c|d
w|x|y|z

I want to load this data skipping the second field as follow:

f1    f2    f3 
---   ---   ---
a     d     c
w     z     y

Would really appreciate your help or any pointer in constructing the control file to achieve this.

Paralytic

Answer 1

Define the column you want to skip as FILLER. Keep in mind the order of the columns in the control file is typically the order they are in the datafile. If the name matches a column in the table, that's where it will go.

...
(
  f1 CHAR,  -- 1st field in the file, goes to column named f1 in the table
  X FILLER, -- 2nd field in the file, ignored
  f3 CHAR,  -- 3rd field in the file, goes to column named f3 in the table
  f2 CHAR   -- 4th field in the file, goes to column named f2 in the table
)

In other words, the order of the columns in the control file matches the order they are in the data file, not their order in the table. That is matched by name, not order.

EDIT - I added some comments for explanation, but I believe they can't be in that position in the actual file. See below for a full example:

Create table:

CREATE TABLE T1
(
  F1  VARCHAR2(50 BYTE),
  F2  VARCHAR2(50 BYTE),
  F3  VARCHAR2(50 BYTE)
);

The control file, example.ctl:

load data 
infile *
truncate
into table t1
fields terminated by '|' trailing nullcols
(
f1 CHAR,
x FILLER,
f3 CHAR,
f2 CHAR
)
BEGINDATA
a|b|c|d
w|x|y|z

Run it:

C:\temp>sqlldr userid=login/password@database control=example.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Apr 22 11:25:49 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 2

Select from the table:

Hopefully this helps.

Reason

Thank you so much this approach. This particular approach I had in mind and wanted to avoid due to size of the table under consideration which already have approx 35 GB data. And, this is not a one time process and hence not sure if this is good approach. – Paralytic

Could you please suggest any alternate way here? – Paralytic

I don't believe one is needed. This is how you skip a field and make a reusable control file. Unless you want to write some kind of a program to strip out fields you don't need before loading, but you'd still need a control file. Maybe I am not getting why this method is a concern? – Reason

Thank you so much! Now I got the idea. Yes, we can make the control file reusable by just defining a new DUMMY/Filler column in the table and mapping the unwanted data field to the filler. – Paralytic

No, no dummy/FILLER is needed in the table. In the example I gave above, field 'X' is the second line, which corresponds to the second field in the data file. Since it is FILLER, it is ignored. There is no column in the table named 'X'. You just name it in the control file so it does not match a column name. The table only has columns named f1, f2 and f3. – Reason

See amended example above. – Reason

Thank you so much Gary_W. I got the idea now. – Paralytic

Perfect Answer. SQL Loader usually ignore if column is not matched. We can use this approach to advantage. For us same issue got fixed with this approach. – Shut

Sidenote: If you want to skip multiple columns, each filler columns must have unique name like 'X1', 'X2' etc. Otherwise you'll get an error: SQL*Loader-404: Column X present more than once in <tablename>'s INTO TABLE block. – Mendicity