PURPOSE
-------
Users will often need to load data where they wish to omit certain fields in 
the data file, so only desired the fields will be loaded into the table. 
This situation allows several alternative approaches but the ideal would be for 
the undesired columns to be ignored by SQL*Loader as it performs the load. 

If the data is position-based and fixed-length, this will require simply 
specifying the POSITION clauses in the control file so that the undesired 
fields are bypassed. 

Delimited data, however, presents more complex scenario and this article 
will look at a few possible methods of dealing with the issue.

This bulletin describes some methods to achive the goal.


SCOPE & APPLICATION
-------------------
This bulletin applies to various SQL*Loader versions and all platforms. 


How to Skip Columns in a Delimited Datafile using SQL*Loader
------------------------------------------------------------

What is 'delimited' data?
-------------------------

In a delimited data file, fields are of variable lengths and delimited by 
special characters, most typically  by commas. Such a datafile is often 
described as a CSV ( Comma Separated Variable) file.

As a field can only be skipped if the position of the next valid field can be 
given, skipping a delimited field was not possible until Oracle 8i , though 
there are workarounds for v7.X and v8.0

Oracle8i (8.1), or higher has the option of using the FILLER field identifier 
to mark a data column as a condition qualifier.



1)  FILLER fields - Oracle8i (8.1), or higher
-----------------------------------------------

A filler field is a datafile mapped field which corresponds to no database 
column. The filler field is assigned values from the datafield to which it 
is mapped and can be used as an argument to a number of functions.

Filler fields can be used in field condition specifications in NULLIF, 
DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings and 
they cannot themselves contain a NULLIF/DEFAULTIF clause.

For each field from the input data to be skipped, specify column_name 
FILLER DATATYPE within the load data statement.



Example:-

LOAD DATA
INFILE *
INTO TABLE test
FIELDS TERMINATED BY ","
(
c1        		INTEGER,
c2 	FILLER CHAR,
c3        		CHAR
)
BEGINDATA
7782,SALES,CLARK
7839,MKTG,MILLER
7934,DEV,JONES


This will cause SQL*Loader to treat the field as a condition qualifier. 
You could then use the 'C2' qualifier in a where clause to perform. a 
conditional load based on the value of 'C2'.



2) Load into a temporary table and then remove the unwanted columns 

i)  Load all the columns into a temporary table 

Example:-

LOAD DATA
INFILE *
INTO TABLE temp
FIELDS TERMINATED BY ","
	 (	
c1        	INTEGER,
c2 	CHAR,
c3        	CHAR
  	)
BEGINDATA
7782,SALES,CLARK
7839,MKTG,MILLER
7934,DEV,JONES


(note:- with large loads, if disc space space is a problem, tell SQL*Loader to 
put null into the unrequired columns ie put null in col2 instead of the datatype) 


ii)  Isolate the desired columns for your desired table

CREATE TABLE target AS SELECT c1, c3 FROM temp

iii) drop the temporary table 

DROP TABLE temp

        


3) Use O/S utilities to trim the undesired fields out of the datafile

Text editors can often provide useful features to prepare the datafile in such 
a way that the unwanted fields can be removed prior to using SQL*Loader. 
For unix users, further details can be found in :-

Note:1012594.6 Useful UNIX Utilities to be used with SQL*LOADER 




4) Use SQL within SQL*Loader 

Occasionally it may be the case that a pre 8i database needs to perform. a load 
where the methods in 2) and 3) are not viable and the control file itself has 
to be edited to achieve the same objectives. In such rare cases, we can use 
SQL functions within the control file. This use of SQL strings is only permitted 
with convential path loads and cannot be used with the direct load option.


Example:-

with the following data

  123,2563,"John Smith",1232
  726,21,"Peter,Marks",12

To skip the first column (values 123 and 726) and the third column with the 
names ("John Smith" and "Peter,Marks"), the following technique can be used.


CREATE TABLE test  (col2 number, col4 number);

LOAD DATA
INFILE *
INTO TABLE test
(col2 char(80)
"substr(:col2,instr(:col2,',')+1,instr(:col2,',',1,2)-instr(:col2,',')-1)",
col4 "substr(:col2,instr(:col2,',',-1,1)+1)")
BEGINDATA
123,2563,"John Smith",1232
726,21,"Peter,Marks",12


SQL> select * from test;

COL2		|	COL4
---------------------	|-------------------
2563		|	1232
21		|	12


Explanation :-

  a = Instr(:col2,',') returns the offset of the first comma (fourth position)
  b = a +1 gives the beginning of the second field (fifth position)
  c = instr(:col2,',',1,2) provides the offset of the second comma
  d = c-a-1 is the length of the second field
  e = instr(:col2,',',-1,1) is the offset of the last comma (the first starting
      from the end in the reverse order)
  f = e+1 is the beginning of the fourth field

RELATED DOCUMENTS
-----------------
Oracle Utilities Guide