CODEWITHSUNDEEP
oracle-database
João Silva
João Silva

Reputation: 581

Oracle loader skip lines

In a CSV, using oracle loader, how can I skip lines that aren't the header? For exemple:

Names;Initials
SomethingForSkip
Name1;Inital1
Name2;Inital2
SomethingForSkip
Name3;Inital3
Name4;Inital4

At the moment I have this code:

CREATE TABLE t_ext_course(
UC  CHAR(100),
SCIENTIFIC_FIELD    CHAR(10),
DEPARTAMENT CHAR(100)

)
ORGANIZATION EXTERNAL
(
    TYPE oracle_loader
    DEFAULT DIRECTORY src_files
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY newline
        BADFILE 'course.bad'
        DISCARDFILE 'course.dis'
        LOGFILE 'course.log'
        SKIP 3
        FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
        (
            UC              CHAR(100),
            SCIENTIFIC_FIELD    CHAR(10),
            DEPARTAMENT     CHAR(100)
        )
    )
    LOCATION ('course.csv')
)
REJECT LIMIT UNLIMITED;

Thank you in advance for your help.

Upvotes: 1

Views: 1339

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17934

You can use the LOAD WHEN parameter. You would put it in right after your SKIP 3 parameter.

With LOAD WHEN, you can specify conditions so that only the source file rows meeting that condition will be loaded.

SQL*Loader and, by extension, external tables of type oracle_loader offer a BLANKS keyword that you can use to check for empty fields. It is useful for delimited data where you do not know the field length.

You can put it all together like this:

LOAD WHEN (SCIENTIFIC_FIELD != BLANKS)

Upvotes: 3

Related Questions