loading 1 field from CSV into multiple columns in Oracle

Question

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)

Is flag 'aaa' or 'Y'? Perhaps check the FILLER option.

ergonaut
– ergonaut

2015-09-19 01:04:57 +00:00
Commented Sep 19, 2015 at 1:04 — ergonaut
– ergonaut, Commented Sep 19, 2015 at 1:04

Gary_W · Accepted Answer · 2015-09-21 23:39:07Z

3

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)"
)

answered Sep 21, 2015 at 23:39

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gsl Over a year ago

Your proposed solution does not work for me with Oracle 12. If the csv file has only 5 columns, then SQLLdr only processes 5 field entries from the control file.

Gary_W Over a year ago

Are you using the TRAILING NULLCOLS clause? You may want to start a new question where you include your data, control and log files too.

gsl Over a year ago

Yes, I were using TRAINLING NULLCOLS. Problem was that I used only FILLER and BOUNDFILLER for ALL csv columns. In that case, SQLLdr does seem to stop looking at control file lines when ${numberOfCsvFields} lines have been processed. When I map to one real column somewhere, then SQLLdr carries on with the "excess" lines.

Collectives™ on Stack Overflow

loading 1 field from CSV into multiple columns in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related