| sql loader mapping only specific columns from data file [message #131258] |
Fri, 05 August 2005 00:51  |
find_meghana
Messages: 1
Registered: August 2005
|
Junior Member |
|
|
Hi,
I am using sql loader to load data into a table
table1(col1,col2,col3)
My data file consists of something like...
LOAD DATA INFILE * APPEND INTO TABLE table1 FIELDS
TERMINATED BY '\t' (col1,col2,col3)
BEGINDATA
d1 T1 aaa a 12
d2 T2 bbb b 13
d3 T3 ccc c 14
Here I want to load data from first,second and fourth columns
from the data file into the three columns of the table.
How can I specify this mapping.
|
|
|
|
| Re: sql loader mapping only specific columns from data file [message #131263 is a reply to message #131258] |
Fri, 05 August 2005 01:12  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Hi ,
Go to this folder on the machine hosting Oracle.
%ORACLE_HOME%\rdbms\demo
and u wil find few scripts and files starting with
ulcase1, ulcase2 ... ulcasen.
Examples of both control files and datafiles r thr .
One file looks like this
LOAD DATA
INFILE 'ulcase2.dat'
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL)
I think now u can map through positions. u can check more examples at the above location.
regards,
tarun
|
|
|
|
|
|
|
|
| Re: sql loader mapping only specific columns from data file [message #131745 is a reply to message #131741] |
Tue, 09 August 2005 04:57 |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
Hi shila,
Sqlldr is a command prompt utility provided by Oracle to load data in text format.
Pls read the documentation before performing the task.
lets take it step by step :
1. --> Excel ( save the file in text format using save as option of excel)
--> Access ( save the tables in text format using export feature of access)
now u got the text files containing data to load.
2. create necessary table in oracle where u want to load data.
3. create a control file(not the control file of database) for sqlldr. U can see the examples of control file in ur database server at this location. --- %ORACLE_HOME%\rdbms\demo. This location contains examples of control files as well as examples of data files .And file names would be something like
ulcase1, ulcase2 ... ulcasen
4. Now when everything ready u can fire the sqlldr command from the dos prompt .
Suppose we have ,
datafile -- datald1.dat
controlfile-- dataload.ctl
userid -- scott/tiger
then the command could look like this --
C:\> sqlldr USERId=scott/tiger CONTROL=c:\dataload.ctl DATA=datald1.dat LOG=C:\test1.log
and the data wil b loaded if everything happens fine.
But do read the sqlldr documentation first before doing all this.
regards,
tarun
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
