CODEWITHSUNDEEP
oracle-databasesql-loadercontrolfile
The PowerHouse
The PowerHouse

Reputation: 560

how to change the beginning position of the field in sql loader

I have a file which i want to load using sql loader.the sample file is as follows.

1|Deepak|1|raj|Kumar|mcapatna|powerhouse

the control file is 

LOAD DATA        
 INFILE *          
TRUNCATE 
INTO TABLE Student WHEN (1:1)= '1'
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS                           
(                 
    nickName1       position(6)  NULLIF  nickName1=BLANKS  ,
    nickName2       NULLIF  nickName2=BLANKS  ,
    class           CONSTANT '10',
    Address             CONSTANT 'NA'

)

The output what i want is nickName1=mcapatna nickName=powerhouse class=10 & Address=NA. I am getting the value for nickName1=pak & for nickName2= 1 That means it is counting based on sequence of single characters..

Upvotes: 0

Views: 1468

Answers (2)

Gary_W
Gary_W

Reputation: 10360

The control file is in data file field order. To skip the fields in the data file you do not want, you need to "consume" them by defining them as FILLER.

...
(
skip_1     FILLER,
skip_2     FILLER,
skip_3     FILLER,
skip_4     FILLER,
skip_5     FILLER,
nickname1,
nickname2,
class      CONSTANT '10',
Address    CONSTANT 'NA'
)

See this fairly recent post for more info: Skipping data fields while loading delimited data using SQLLDR

Upvotes: 2

massie
massie

Reputation: 556

By adding position(6) in your ctl file, it starts at position 6 till the next |. So pak is the expected value. If you remove position(6), it will start on position 1 automaticly.

I guess something like this, would do the job (this is not tested but gives you a direction):

LOAD DATA
INFILE *
INTO TABLE Student
FIELDS OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( nickName1         CHAR NULLIF  nickName1=BLANKS  TERMINATED BY "|"  
, nickName2         CHAR NULLIF  nickName2=BLANKS TERMINATED BY "|"  
, class             CHAR  CONSTANT '10' TERMINATED BY "|",  
, Address           CONSTANT 'NA' TERMINATED BY "|"
)

Upvotes: 0

Related Questions