asked on

Oracle 9i SQL loader

I have file as follows:
"56789","Abc","","Bcde","28"
"456789","Xyz","A","Mno","30"

I want to split first field into 3 different parts as

567 first field
8 second field
9 third field

second record

4567 first field
8 second field
9 third field

I want to insert into a table using SQL loader which following fields. If i insert first record it should be as

first field: 567
second : 8
third : 9
fourth : Abc
fifth :
sixth : Bcde
seventh : 28

Can you help me out with the code.

Thanks in advance

Naveen Kumar

try something like this

LOAD DATA
INFILE a.dat
APPEND INTO TABLE dept
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( col1 char "substr(:col1,1,length(:col1)-2)",
col2 char "substr(:col1,-2,1)",
col3 char "substr(:col1,-1)",
col4 char,..
)

You can also write functions and call the same with EXPRESSION from the control file.

see the below link for more information (search for EXPRESSION if you want to write a function and call that if the above does not work ).

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm#1007849

Thanks

aaba646

ASKER

Iam able to load first three fields but the other fields iam not able to load properly.
Its treating different fields and loading differently.

Thanks

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aaba646

ASKER

Thanks nuv. Its working!!!