0

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..

The PowerHouse
  • 560
  • 14
  • 29

2 Answers2

2

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

Gary_W
  • 9,933
  • 1
  • 22
  • 40
0

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 "|"
)
massie
  • 556
  • 2
  • 10