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.

Could you load all columns and then drop the ones you don't want after loading the data? Not a perfect solution, but it should work. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 9, 2017 at 8:41
Thats possible, even in that situation we can use Filler which will skip loading all other columns. But for that I need to create separate columns. And the other part is that the number of columns are dynamic in the file, but I need to grep only from 2nd column. @TimBiegeleisen — Sarath Subramanian
– Sarath Subramanian, Commented Jan 9, 2017 at 8:49

Alex Poole · Accepted Answer · 2017-01-09 09:56:54Z

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.

robertsp · Accepted Answer · 2017-01-09 09:36:11Z

0

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.

answered Jan 9, 2017 at 9:36

robertsp

11 silver badge1 bronze badge

1 Comment

Alex Poole Over a year ago

The control file in the question already has the skip. You also don't need the filler column(s) after all the ones you care about.

Collectives™ on Stack Overflow

Insert part of data from csv into oracle table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related