Java Developer
Java Developer

Reputation: 43

How to map single file(.csv) column to multiple table columns using sqlldr

I have a file(.csv) contains the columns A1,A2,A3 and Table has the columns T1,T2,T3,T4,T5.

I want to map or insert data from A2 column to T2,T3,T4.I am able to insert into T2 and T3 using desc_skip FILLER POSITION(1) in control file(.ctl) but not in T4. Could you please help me How to insert single file column value into more than Two columns in table using sqlldr.

Please find the control file below which i am using.

LOAD DATA
INTO TABLE EMP
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 
(
    "T1" INTEGER ,
    "T2" CHAR,  
    desc_skip FILLER POSITION(1),
    "T3" CHAR,
    "T4" CHAR,
    "T5" INTEGER
)

Thanks in Advance.

Upvotes: 2

Views: 1755

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

You should use something similar to

...
T2 char,
T3 expression ":T2",
T4 expression ":T2",
...

P.S. I cannot imagine what can 'not working'. My example:

Database:

SQL> create table t$loader (t1 varchar2(10), t2 varchar2(10), t3 varchar2(10), t4 varchar2(10), t5 varchar2(10));

Table created

CSV file:

echo 1,2,3 > csv.csv
echo 4,5,6 >> csv.csv
echo 7,8,9 >> csv.csv

Control file:

load data
  infile 'csv.csv'
  into table t$loader
  fields terminated by ','
(
  t1 char,
  t2 char,
  t3 expression ":t2",
  t4 expression ":t2",
  t5 char
)

Run:

sqlldr userid=user/password@db control=csv.ctl

Result:

SQL> select * from t$loader;

T1         T2         T3         T4         T5
---------- ---------- ---------- ---------- ----------
1          2          2          2          3
4          5          5          5          6
7          8          8          8          9

Isn't it what you ask for?

Upvotes: 0

Related Questions