sqlldr: selected field insert from data file
Date: Mon, 14 Jan 2008 16:45:03 -0600
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)
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.
TRUNCATE INTO TABLE HARRIS.CUST
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 220.127.116.11 on Solaris 10 server.
Any help would be greatly appreciated.