0

I'm currently inserting data some columns from a CSV file into a table by using SQL loader and that data is validated and the remaining columns are filled automatically based on the inserted data. But if I have an unnecessary extra data beyond my required columns, that is inserting into the other columns of the table which is supposed to null if the data is ok after validation.

I want to take only certain columns from the CSV file and need to insert into table.. no need of any extra data from other columns in CSV file to be load. What should I do?

I'm wondering if there is any thing I need to include in this!

Options (ERRORS=100000,SKIP=1) Load data Append Into table emp Fields terminated by ',' optionally enclosed by '"' Trailing Nullcols

(Emp_id char, Dept char, Class integer, Subclass integer )

1 Answer 1

1

You don't have to do anything special - just omit all these columns from the control file, here:

(Emp_id char, Dept char, Class integer, Subclass integer )

To illustrate it: sample table has only two columns:

SQL> create table test
  2  (emp_id number,
  3   dept_name varchar2(10));

Table created.

Control file's data contain some more data - talking about this:

1,A,some more data,123
2,B,not important,553
    ------------------
    no columns for this in the table

Control file itself:

LOAD DATA
INFILE *
REPLACE
INTO TABLE test
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
emp_id,
dept_name
)

BEGINDATA
1,A,some more data,123
2,B,not important,553

Loading session:

SQL> $sqlldr scott/tiger@orcl control=test45.ctl log=test45.log

SQL*Loader: Release 18.0.0.0.0 - Production on Čet Lis 27 12:55:34 2022
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test45.log
for more information about the load.

Result:

SQL> select * from test;

    EMP_ID DEPT_NAME
---------- ----------
         1 A
         2 B

SQL>

As you can see, everything is just fine.


If it were vice versa, i.e. you'd want to populate columns that don't exist in the CSV file, you'd use e.g. filler (but - as that's not your issue - forget it. Actually, remember it, maybe you'll need it later).

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.