TIP: How to bypass unwanted columns of the text file to load into the database from sql*loader

 

There are two method to do this

 

1.              Recreate your text file with all the wanted columns.

2.              Use of filler clause available from oracle 8.1.6

 

I am trying to explain  method 2 here.

 

 

Assume we have following files as follows :-

 

Text file (c:\temp\ldr2.txt)

 

7369,SMITH,dummy,CLERK    ,7902,17-DEC-80,800, ,20                                              

7499,ALLEN,dummy,SALESMAN ,7698,20-FEB-81,1600,300,30                                               

7521,WARD,dummy,SALESMAN ,7698,22-FEB-81,1250,500,30                                              

7566,JONES,dummy,MANAGER  ,7839,02-APR-81,2975, ,20                                              

7654,MARTIN,dummy,SALESMAN ,7698,28-SEP-81,1250,1400,30                                              

7698,BLAKE,dummy,MANAGER  ,7839,01-MAY-81,2850,,30                                              

 

In the above text file I do not want to load 3rd column i.e having a value dummy in it.

 

CONTROL FILE (ldrtest.ctl)

Load data

infile 'c:\temp\ldr2.txt'

 replace into table scott.empldr

fields terminated by "," optionally enclosed by '"'

 

(

 EMPNO,

 ENAME,

 xx filler,

 JOB,

 MGR,

 HIREDATE,

 SAL,

 COMM,

 DEPTNO

)

 

The column xx is corresponding to 3rd column of text file which I want to bypass

 

Database table

 

SQL> desc empldr

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

 

Table does not have database column name xx  as you are seeing it in the control file.

 

To bypass this column ,oracle 8.1.6 has filler clause. You have to create file as above to bypass the unwanted columns from the text file. You have to put filler clause in front of the column which you want to bypass.

 

Load the file through sql*loader

 

sqlldr userid=scott/tiger control='d:\mydoc\sqlldr\control\ldrtest.ctl' log=c:\temp\ldr.log

 

 

End of Tip

Wadhwa_s@Hotmail.com

 
Copyright © Oracle Techniques All Rights Reserved