SQL> create table t ( empid int,empname varchar2(20),address varchar2(200),salary int,deptno int );
Table created.
SQL> host cat x.ctl
LOAD DATA
INFILE *
INTO TABLE T
TRUNCATE
FIELDS TERMINATED BY ','
(
empid position(1:1000) "substr(:empid,1,instr(:empid,',')-1)",
empname position(1:1) "substr(:empid,instr(:empid,',')+1, instr(:empid,',',1,2)-instr(:empid,',')-1)",
address position(1:1) "substr(:empid,instr(:empid,',',1,2)+1, instr(:empid,',',-1,2)-instr(:empid,',',1,2)-1)",
salary position(1:1) "substr(:empid,instr(:empid,',',-1,2)+1, instr(:empid,',',-1)-instr(:empid,',',-1,2)-1)",
deptno position(1:1) "substr(:empid,instr(:empid,',',-1)+1)"
)
BEGINDATA
1123,Swarup,PO Box 42,1407 Graymalkin Lane,Salem Center, New York 10560,10000,10
1124,Kumar,PO Box 1492,16346 E. Graham Circle,Palmer, AK 99645,20000,20
1125,Kumar,Simple Address No Commas,20000,20
1126,Kumar,Simple Address One,Comma,20000,20
SQL> host sqlldr userid=/ control=x
SQL*Loader: Release 12.1.0.2.0 - Production on Tue Nov 15 09:51:16 2016
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 4
Table T:
4 Rows successfully loaded.
Check the log file:
x.log
for more information about the load.
SQL> select * from t;
EMPID EMPNAME ADDRESS SALARY DEPTNO
---------- -------------------- ------------------------------------------------------------ ---------- ----------
1123 Swarup PO Box 42,1407 Graymalkin Lane,Salem Center, New York 10560 10000 10
1124 Kumar PO Box 1492,16346 E. Graham Circle,Palmer, AK 99645 20000 20
1125 Kumar Simple Address No Commas 20000 20
1126 Kumar Simple Address One,Comma 20000 20
SQL>