CODEWITHSUNDEEP
sql-loader
Sab
Sab

Reputation: 21

loading 1 field from CSV into multiple columns in Oracle

I am trying to insert 1 column from CSV into 2 different oracle columns. but it looks like SQL Loader looks at least n fields from CSV to load n columns in oracle and my CTL script does not work for loading n field from CSV to n+1 column in Oracle where I am trying to load one of the field into 2 different oracle columns. Plz advise

Sample data file is:

id,name,imei,flag
1,aaa,123456,Y

my oracle table has below column

create table samp (
id number,
name varchar2(10),
imei varchar2(10),
tac varchar2(3),
flag varchar2(1) )

i need to load the imei from csv onto imei in Oracle Table and substr(imei,1,3) into tac Oracle column

my Control file is:

OPTIONS (SKIP=1)
load data
 infile 'xxx.csv'
 badfile 'xxx.bad'
 into table yyyy
 fields terminated by "," 
 TRAILING NULLCOLS
 ( id,name,imei,tac "substr(:imei,1,3)", flag)

Error from the log file:

Record 1: Rejected - Error on table yyyy, column flag
Column not found before end of logical record (use TRAILING NULLCOLS)

Upvotes: 2

Views: 7171

Answers (1)

Gary_W
Gary_W

Reputation: 10360

Ok, keep in mind the control file matches the input data by field in the order listed, THEN the name as defined is used to match to the table column.

The trick is to call the FIELD you need to use twice by something other than an actual column name, like imei_tmp and define it as BOUNDFILLER which means use it like a FILLER (don't load it) but remember it for future use. After the flag field, there are no more data fields so SQLLDR will try to match using the column names.

This is untested, but should get you started (the call to TRIM( ) may not be needed):

...
( id,
  name,
  imei_tmp  BOUNDFILLER,
  flag,
  imei      "trim(:imei_tmp)",
  tac       "substr(:imei_tmp,1,3)"
)

Upvotes: 3

Related Questions