Insert part of data from csv into oracle table

Question

I have a CSV (pipe-delimited) file as below

ID|NAME|DES
1|A|B
2|C|D
3|E|F

I need to insert the data into a temp table where I already have SQLLODER in place, but my table have only one column. The below is the control file configuration for loading from csv.

OPTIONS (SKIP=1)
LOAD DATA 
CHARACTERSET UTF8
TRUNCATE
 INTO TABLE EMPLOYEE
 FIELDS TERMINATED BY '|'
TRAILING NULLCOLS 
(
NAME
)

How do I select the data from only 2nd column from the csv and insert into only one column in the table EMPLOYEE?

Please let me know if you have any questions.

Answer 1

If you're using a filler field you don't need to have a matching column in the database table - that's the point, really - and as long as you know the field you're interested in is always the second one, you don't need to modify the control file if there are extra fields in the file, you just never specify them.

So this works, with just a filler ID field added and the three-field data file you showed:

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET UTF8
TRUNCATE
 INTO TABLE EMPLOYEE
 FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
IF FILLER,
NAME
)

Dmoe'd with:

SQL> create table employee (name varchar2(30));

$ sqlldr ...

Commit point reached - logical record count 3

SQL> select * from employee;

NAME                          
------------------------------
A
C
E

Adding more fields to the data file makes no difference, as long as they are after the field you are actually interested in. The same thing works for external tables, which can be more convenient for temporary/staging tables, as long as the CSV file is available on the database server.

Answer 2

Columns in data file which needs to be excluded from load can be defined as FILLER.

In given example use following. List all incoming fields and add filler to those columns needs to be ignored from load, e.g. ( ID FILLER, NAME, DES FILLER ) Another issue here is to ignore header line as in CSV so just use OPTIONS clause e.g. OPTIONS(SKIP=1) LOAD DATA ...

Regards, R.