Re: SQL Loader and delimited fields
Date: Wed, 10 Jun 1998 10:38:15 GMT
Message-ID: <357e580f.10475955_at_www.sigov.si>
On Tue, 9 Jun 1998 17:54:40 -0500, "Geoff" <gschemme_at_qni.com> wrote:
>Does anyone know how to skip a field when using SQL*Loader and a delimited
>flat file? I have a file with 5 fields per record. The fields vary in
>length between records, so I must use a delimited file format. My table has
>4 columns, and I need to load the first three fields into into the first
>three columns, but the fifth field must go into the fourth column. How can
>I skip the fourth field?
>
>Thanks in advance!
The sollution provided by Thomas Kyte is the universal one. But there exists another, somewhat easier sollution, which has its advantages and disadvantages compared to the one involving PL/SQL. The main advantage of the following method is it can be used with SQL*Loader in direct path mode, while tkyte's sollution can't. So here it goes:
Suppose your table T1 has 4 columns: C1, C2, C3, and C4. Create the following view:
CREATE VIEW V1 AS SELECT c1, c2, c3, c4, c4 AS dummy FROM t1;
Now make the loader to insert the data into the table through this view. The control file should be something like:
LOAD DATA INFILE ......
INTO TABLE v1 FIELDS TERMINATED BY ','
(c1, c2, c3, dummy, c4)
This way you'll skip the fourth field.
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Jun 10 1998 - 12:38:15 CEST