tab delimited control file
The script for loaidng tab delimited can be written as follows: It works good for me
-- Loads data even though few fileds are null values.
-- make sure the tempraray table columns data types length match with the raw data file
-- NULLIF is used to let the field be null if a particular field has a null value
-- x'09' is used for tab delimiting
-- below are few sample coloumns
-- a temprary table should be created in oracle with appropriate data types and length matching the tab delimted text field.
LOAD DATA
INFILE './xyz.txt'
BADFILE './xyz.BAD'
DISCARDFILE './xyz.DSC' DISCARDMAX 50
INSERT INTO TABLE TEMP
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
( Column1 NULLIF (Column1="NULL"),
Column2 NULLIF (Column2="NULL"),
Column3 NULLIF(Column3="NULL"),
Column4 NULLIF (Column4="NULL"),
Column5 "MM/DD/YY" NULLIF(Column5="NULL"),
Column6 NULLIF(Column6="NULL"),
)
-- SQLLOAD COMMAND : sqlldr control=TEMPCONTROLFILE.ctl