SQL Loader tab delimited files

[wjh_uk]

I am trying to import selected columns from a file into one table. for example the data looks like this(Tab delimited)

123456 34 23 Today Bank1
126556 Today Bank2
123478 34 23 Today Bank3
193856 34 23 Today Bank4
127265 34 23 Today Bank5
098756 25 23 Today Bank6

and the table is like this

ID number(6);
Day varchar2(5);
Bank_name varchar2(12);

I know I can load them in to a temporary table, then load what I want in to my actual table but is there a way I can do it without using a temporary table. The positions(character) are not allways the same so I can't use the positioning thing. But the positions are always the same with respect to the tabs. Even if the fields are empty the tabs are still there.

Can anyone help?

Thanks

[Shestakov]

LOAD DATA
INFILE *
APPEND (or insert/replace)
INTO TABLE one_table
FIELDS TERMINATED BY WHITESPACE
(fld,fld2,fld3,fld4)
BEGINDATA
123456 34 23 Today Bank1
126556 ?? ?? Today Bank2
123478 34 23 Today Bank3
193856 34 23 Today Bank4
127265 34 23 Today Bank5
098756 25 23 Today Bank6

[culonbu]

use SQLLDR with following control file

OPTIONS(DIRECT=TRUE, ERRORS=999999)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE '/u02/dwanat/datatfr/import/NSBGL'
READBUFFERS 4
TRUNCATE
INTO TABLE dwstg_bgeog_loctn
TRUNCATE
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY '\t'
TRAILING NULLCOLS
( fld1,fld2,fld3,fld4,fld5)




Note: '\t' = tab character

[sahaja]

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

[davey23uk]

6 years old! why bring up a 6 year old thread