Skipping Delimited Fields in SQL*Loader

From: Brad McFarlane <bradm_at_newbridge.com>
Date: Mon, 30 Aug 1999 09:01:36 -0400
Message-ID: <37CA80AF.6D090ECA_at_newbridge.com>

I'm new to using SQL*Loader, but there's something I'd like to do that doesn't seem to be in the Oracle on-line docs for SQL*Loader.

In Case Study 3: Loading a Delimited, Free-Format File, they use:

LOAD DATA

	INFILE *
	APPEND
INTO TABLE emp
	FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

(empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':', projno, loadseq SEQUENCE(MAX,1))
BEGINDATA
7782,"Clark","Manager",7839,09-June-1981,7572.50,,10:101

to load up the table. What if, however, the data looked like this:

BEGINDATA
7782,"Clark","field-to-ignore","Manager",7839,09-June-1981,7572.50,,10:101

(e.g., if it came from some program that output a fixed set of fields)
but the "emp" table was the same. What you need to do is tell SQL*Loader that "empno" is the first field, "ename" the second, "job" the fourth field, etc. In other words, SQL*Loader should skip the third (delimited) field on the line. With fixed-width data, it seems pretty easy to skip columns using POSITION to say which data columns go with which table column names, but I can't figure out how to say which data fields go with which table column names.

Is this possible, and if so, how? If it isn't, I'll have to resort to stripping out just the columns I want before I feed the data into SQL*Loader.

Any help would be appreciated.

Thanks,
Brad McFarlane

-- 
Brad McFarlane
Newbridge Networks Corporation
Kanata, Ont.
CANADA

Received on Mon Aug 30 1999 - 15:01:36 CEST