SQL Loader Exclude lines from datafile

From: Bernhard Graeuler <fuerspam_at_gmx.de>
Date: Mon, 27 Jan 2003 11:56:04 +0100
Message-ID: <b13381$urg1s$1_at_ID-49408.news.dfncis.de>



Hello,

I have to import a datafile, that has the following structure:

The first line contains the filename and some numbers. Then some lines with the filed names are following. The data rows have fix positions, but they are interrupted by repetitions of the field names.

Example:
  C:\xxxxxx\xxxxxxxx.xxx xx/xx 00.00.0000/xx

* Motor-Typ  ! Nenn-! Bemerk ! Wirtsch !
*            ! dreh-!  Land  !Ber UNTEN!
*            ! zahl ! (Code) !von  bis !
*------------!------!--------!---------!

,XX543 , 2800 , ,1400 1600,
,FD564A , 2800 , ,1400 1600,
,EG872 , 2800 , ,1400 1600,
,HF352A , 2800 , ,1400 1600,
,HE535 , 2200 , ,1000 1200,
,WF355A , 2200 , ,1000 1200,
,JZ540 , 2500 , ,1100 1350,
,EG360A , 2500 , ,1100 1350,
*------------!------!--------!---------! * Motor-Typ ! Nenn-! Bemerk ! Wirtsch ! * ! dreh-! Land !Ber UNTEN! * ! zahl ! (Code) !von bis ! *------------!------!--------!---------!
,WFE01 , 2500 , ,1100 1350,
,FE401LA , 2100 ,Euro1 , 900 1100,
,HT654LA , 1900 ,Euro1 , 900 1100,
,ER401LA , 1900 ,Euro1 ga, 900 1100,
,HT453 , 2500 , ,1100 1350,

[Quoted] Now I need a control file for the SQL*Loader that filters out the repetitions of the fieldnames. Theoretically that would be easy, because they start with a '*', but I don't now how to do it. I thought about a "WHEN [Quoted] (1) != '*'" Statement, but as far as I know the (1) means the first field of [Quoted] the table, what is wrong...
This is what I have so far:

LOAD DATA
INFILE 'xxxxxx\xxxxxxxx.xxx'
TRUNCATE
INTO TABLE xxxxxxxx
TRAILING NULLCOLS
SKIP 5 (

  • FIELDS FROM THE ASCII FILE MOTORCODE position(2:13), NENNDREHZAHL position(16:19), BEMERKUNG position(22:29), WIRTBERUNTEN_VON position(31:34), WIRTBERUNTEN_BIS position(36:39),
  • AUTOMATIC GENERATED FIELD GGUID "HEXTORAW(SYS_OP_GUID())", UPDATETIMESTAMP SYSDATE, INSERTTIMESTAMP SYSDATE, IMPORTTIMESTAMP SYSDATE, UPDATEUSER CONSTANT "SQLload", INSERTUSER CONSTANT "SQLload"

)

Regards,

    Bernhard Received on Mon Jan 27 2003 - 11:56:04 CET

Original text of this message