Set a specific cell value to a column (CTL)

Question

I have created a CTL file that will read the data inside a CSV file. It loads the data from the CSV to the table in the database. I just want to ask if it is possible to set a table column to a specific cell on CSV file when loading data using sqlloader?

For example (CTL script):

INTO TABLE "DB"."TABLENAME"
TRUNCATE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
**COL1 (:A1),** - is it possible to set this column to a specific cell?
COL2,
COL3,
COL4,
LOAD_DATE SYSDATE
)

Thanks in advance. :)

@nop77svk: it is the specific cell on an csv file. Like for example cell A1. — user3462803, Jul 02 '14 at 01:08
Aaaha, OK ... By "to set this column to a specific cell" you mean: For each row being imported/loaded the column value will be set to the value of the "cell" "A1"? — peter.hrasko.sk, Jul 02 '14 at 05:40
Yes, it is, if you want a specific cell value from the first row. No, it is not, if you want a specific cell value from one of the rows beyond the first one. Is the first row OK for you? If so, I have a solution to your problem. — peter.hrasko.sk, Jul 02 '14 at 12:17
@nop77svk I have to skip its row. But I have to keep it on a specific column. It is located in different rows. — user3462803, Jul 03 '14 at 01:17
Bad luck. :-( SQL*Loader can't do this. You still can, though, load your data with a sequence column and then use SQL with analytic functions to select whichever cell value you need. — peter.hrasko.sk, Jul 03 '14 at 06:11
Thank you so much @nop77svk.. Do you know how to do that?If so, can you please share it with me?. :) — user3462803, Jul 03 '14 at 07:01
Sorry just read your answer. Thank you so much @nop77svk.. I'll give it a try. :D — user3462803, Jul 04 '14 at 06:38
And if it works, I kindly ask for marking your question as answered. That's how SO works. Thank you. — peter.hrasko.sk, Jul 04 '14 at 07:20

score 0 · Answer 1 · answered Jul 03 '14 at 07:27

Extend your table TABLENAME with an integer column, let's name it ROW#. Then modify your control file as follows ...

LOAD
INTO TABLE "DB"."TABLENAME"
TRUNCATE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
    COL1,
    COL2,
    COL3,
    COL4,
    LOAD_DATE SYSDATE,
    ROW# SEQUENCE
)

Then create a primary key on TABLENAME (ROW#).

Then create a view over your TABLENAME which contains a new column with the specific cell value from the specific row you need. This is an "SQL for beginners" and, moreover, outside the scope of the sql-loader tag, so from here onwards you are on your own.

Set a specific cell value to a column (CTL)

1 Answers1