CODEWITHSUNDEEP
oracle-databaseoracle12csql-loader
Swoop
Swoop

Reputation: 49

SQL*Loader control file mapping one data field to two columns

I am using a shell script with the sqlldr command, to load data from a CSV file into a table.

For example the CSV file with this data:

dog;cat;bird;fish;

and this control file:

OPTIONS (SKIP=1) LOAD DATA 
 INFILE *
 APPEND
 INTO TABLE animals
 FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' 
 (
animal1, animal2, animal3, animal4)

loads into table as expected:

ANIMAL1    ANIMAL2    ANIMAL3    ANIMAL4   
---------- ---------- ---------- ----------
dog        cat        bird       fish

But now I would like to load the Data into same table like

ANIMAL1    ANIMAL2    ANIMAL3    ANIMAL4   
---------- ---------- ---------- ----------
dog        fish       bird       fish

So instead of 'cat' I would like to have 'fish' in both the second and fourth columns.

How can I modify my control file to achieve that?

Upvotes: 1

Views: 3228

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can refer to other fields as part of an SQL expression, so in this case you can just replace the field value from the file with another field, with bind-variable-type syntax:

...
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(
  animal1,
  animal2 ":animal4",
  animal3,
  animal4
)

After loading your sample CSV with your original control file and one modified as above the table contains:

select * from animals;

ANIMAL1    ANIMAL2    ANIMAL3    ANIMAL4   
---------- ---------- ---------- ----------
dog        cat        bird       fish      
dog        fish       bird       fish

Upvotes: 3

Related Questions