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

Author

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

sunv

2005-04-21, 8:23 pm

Hi there,

Could anyone please let me know how to load few columns from a .csv
file into a oracle table using SQL*Loader.

I know how to create a .dat and .ctl file and run the sql loader.

Suppose I have a .csv file with

col1, col2, col3, col4

and I only need to load col1 and col3 into col_a and col_b respectively
in table_a?

structure of table_ a
col_a,
col_b,
col_3

Please advice

sunv

Arun Mathur

2005-04-21, 8:23 pm

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

SQL> conn amathur@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@dbd
ev
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@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

pero

2005-08-23, 4:17 pm

i want a detailed step by step command line for using my oracle9i sql loader to import and export data from oracle database9i to any other database platform
i will really appreciate it if a mail on oracle backup and recovery steps both for RMAN and user-managed environments
is sent to my email address
thanks
peter

Sponsored Links