Re: SQL*Loader - How to load only a few columns from a .csv file to ora table

From: Arun Mathur <themathurs_at_gmail.com>
Date: 21 Apr 2005 13:38:50 -0700
Message-ID: <1114115930.566475.8460@o13g2000cwo.googlegroups.com>

You can use the filler keyword, providing your release is 8i or higher.  I'll do an example with just numbers:

SQL> conn amathur_at_dbdev
Enter password:
Connected.

SQL> drop table t;

Table dropped.

SQL> create table t(col_1 number(11),col_2 number(11),col_3 number(11),col_4 number(11));

Table created.

Now for the controlfile with some sample data.

load data
infile *
insert into table t
fields terminated by "," optionally enclosed by '"' trailing nullcols
(col_1,

col_2 filler,
col_3 filler,
col_4)

begindata

1,2,3,4
5,6,7,8
9,10,11,12

My intention is to only load columns 1 and 4 from the data into col_1 and col_4.

D:\temp>sqlldr control=load_t.ctl
Username:amathur_at_dbdev
Password:

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Apr 21 16:34:00 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 3

D:\temp>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 21 16:36:03 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: amathur_at_dbdev
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> column col_1 format 999
SQL> column col_2 format 999
SQL> column col_3 format 999
SQL> column col_4 format 999
SQL> select * from t;

COL_1 COL_2 COL_3 COL_4
----- ----- ----- -----

    1                 4
    5                 8
    9                12

Is this what you're looking for?

Regards,
Arun Received on Thu Apr 21 2005 - 15:38:50 CDT