Re: skipping fields with SQL *loader

Hi

Skipping columns with SQLLDR
This is a frequently asked question. How can you take a file full of delimited data and load just some of the columns, skipping over fields you don't want. You cannot use POSTION(x:y) because it is stream data, there are no positional fields -- the next field begins after some delimiter, not in column X.
Prior to Oracle8i, release 8.1, SQLLDR cannot do this without some help. In Oracle8i, release 8.1 the keyword FILLER was added to SQLLDR so that:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE T
FIELDS TERMINATED BY ','
(
field1,
field2 FILLER,
field3
)
BEGINDATA
a,b,c
would be a valid control file that would load the values a and c, skipping over b. So, in Oracle8i, release 8.1 -- this is easy. Prior to that we have a couple of options.

Option 1; Use some Unix utilities
SQLLDR can load data from a pipe. If you are using UNIX, using "cut" on the file and piping the results of this into a named pipe (and having sqlldr ead that pipe) is pretty efficient. For example:

$ mknod sqlldr.dat p
$ cut -f3- -d, < test.dat > sqlldr.dat & [1] 4946
$ sqlldr userid=tkyte/tkyte control=sqlldr.ctl data=sqlldr.dat

would just let sqlldr have at column 3 on in the file test.dat (separated by commas)... The Unix cut command is pretty flexible in what it can do, please see the man page for more info on using it.

The advantage of this method over the next method is that you can use the direct path loader with the above. The next option will only work with the conventional path loader.

Option 2; Use some PL/SQL
We can use the fact that we can call PL/SQL from SQL and that SQLLDR can perform functions on input data to skip columns as well. For example, if you create a control file such as:

LOAD DATA
INFILE *
REPLACE
INTO TABLE DELIMITED_TEST
(
  FIELD1 position(1:4096) "delimited.word (:field1,1,chr(34),chr(44))",
  FIELD2 position(1:1) "delimited.word (:field1,4,chr(34),chr(44))"
)
BEGINDATA
John Jones,"Elm St",Junk,123 Main Street Jeff Boehlert,abcdef," 5555","po box, 1848, abc"

and install the delimited package, you can load whatever columns you want. The above control file would load columns 1 and 4 of the input data, skipping columns 2 and 3. The way this works is that FIELD1 is mapped to the entire INPUT record (postion 1-4096 or whatever your max record may be). We send field1 down to the delimited.word subroutine for EVERY column. The delimited.word routine compares the string it was called with against the last string it parsed and if they differ -- delimited.word parses the string and caches the results (making subsequent calls against the same string of the i'th column instantaneous).

The inputs to delimited.word are:

p_str - the string to get the i'th word from p_n - the word to get from the string -- an index into the string
p_enclosed_by - what the words might be wrapped in. In the above example, chr(34) is a double quote
p_terminated_by - what separates the words. In the above example, chr(44) is a comma.

In article <39520421$0$78798_at_news2.zeelandnet.nl>,   <z> wrote:
> I'm trying to import a table from a comma
 terminated text file using SQL
> *loader.
> In this text file there are 18 fields from wich
 I only need 6. The first
> field I need is
> the 7th one, so I need to skip the first 6
 fields, but these fields have
> variable sizes,
> so the position keyword doesn't seem to work. I
 can't find anything about
> that in
> my documentation. Can anybody help me?
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jul 17 2000 - 00:00:00 CEST