Re: sqlldr: selected field insert from data file

Hi,

That's where the filler keyword comes into action.

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T
FIELDS TERMINATED BY ','
(
field1,
field2 *FILLER*,
field3
)
BEGINDATA
a,b,c

google for more examples

Brgds

Jack

On 15/01/2008, Godwin vincent <godwin.ror_at_gmail.com> wrote:
>
> Hi all,
> I have a question with Oracle SQLLDR. I have a table, which has the
> following columns
> SQL> desc HARRIS.CUST;
> Name Null? Type
> ---------------- ------------------------- --------
> NAME VARCHAR2(50 CHAR)
> APP_NAME VARCHAR2(50 CHAR)
> MASTER_SVR VARCHAR2(10 CHAR)
>
> I have a DATA FILE, which has more than 5 columns of data in it and all i
> want is just the above 3 columns to be loaded in to the table STB_TYPE from
> the DATA FILE. Following is the format of DATA FILE (data.txt) that i have
> (the first row is just the heading as a reference to the columns below)
>
> NAME|LOCATION|SERIAL_NUMBER|APP_NAME|MASTER_SVR|ADDRESS|ORGANIZATION
> hr|chicago|1234|ps|secaucus|nj|comm
> billing|chicago|234|paystubs|secaucus|nj|paychex
> recruit|sacramento|ps|newark|nj|resource
> holidays|sacramento|ps|washington|va|hr
>
>
> As you can there, there are 7 fields in the data file, which are delimited
> by "|". All i would need from this data file are columns, NAME,
> APP_NAME,MASTER_SVR, which are at position 1,4,5 fields respectively. My
> question here is how can i represent this in the control file? so that i can
> only get the corresponding columns get loaded in to the table.
>
> LOAD DATA
> INFILE '/export/home/oracle/data.txt'
> TRUNCATE INTO TABLE HARRIS.CUST
> (NAME .......,
> APP_NAME ......,
> MASTER_SVR .......)
>
> What should i put in the control file so that only the 1,4,5 fields of the
> data file gets loaded in to the table?
>
> The POSITION(x,y) does not work in this case as the column width is not
> fixed.
>
> I know the opposite could be done, where we use the FILLER keyword to
> filter out. Is there anyway to do this or manipulating the datafile (using
> AWK to extract 1,4,5 columns) the only way?
>
> I am working with Oracle 9.2.0.8 on Solaris 10 server.
>
> Any help would be greatly appreciated.
>
> Thanks,
> Godwin.
>

-- 
J.A. van Zanen

--
http://www.freelists.org/webpage/oracle-l