| Mappingv columns using Sql Loader [message #252154] |
Wed, 18 July 2007 00:42  |
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
|
Member |
|
|
Hi Gurus,
i am working wotking with SQL loader to load data got stucked with problem in mapping data
Ex:
i have table like ( col1, col2, col3)
file like ( col3,col2,col4,col5, col1)
i want write control file to load the above table using the given file format
can any one help me out, Thanks in advance...
|
|
|
|
|
|
| Re: Mappingv columns using Sql Loader [message #252190 is a reply to message #252154] |
Wed, 18 July 2007 02:00  |
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
|
Member |
|
|
Thanks for the quick reply,
As you said we can eliminate the columns in loading but my problem with structure i want to load all the columns i don't want to miss any one but the order of the columns are not same as database and i can't ask people to change the source file format right now since i am directly getting files from production and the table has been used by so many people there is no other way i found so far i could just doing manually planning to write one control file which can care of.
File Type: Comma delimited
Data base: Oracle 8i
Hope you can understand the problem....
|
|
|
|
|
|
| Re: Mappingv columns using Sql Loader [message #252202 is a reply to message #252154] |
Wed, 18 July 2007 02:31 |
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
|
Member |
|
|
Hi Michale,
Let me give more detail probably it might give bit clear picture
Following is the table strure
CREATE TABLE TEST_ORDER
(
ONE NUMBER,
TWO DATE,
THREE VARCHAR2(20 BYTE),
FOUR VARCHAR2(20 BYTE)
)
i have file which has column order like
Column names: THREE,ONE,FOUR,TWO ( As i mentioned comma seperated)
My control control file as follows ....
LOAD DATA
INFILE test_order.csv
INTO TABLE wwops.test_order
TRUNCATE
FIELDS TERMINATED BY "," optionally enclosed by '"'
TRAILING NULLCOLS
(
ONE,
TWO DATE 'DD/MM/YY',
THREE,
FOUR
)
When i tried to load data using this way it is returning error...
|
|
|
|
|
|
| Re: Mappingv columns using Sql Loader [message #252214 is a reply to message #252154] |
Wed, 18 July 2007 02:53 |
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
|
Member |
|
|
To match the file i have to suffle the column order in the file..
if u r dealing with comman seperated values sql loader will deal the values based on seperated values only correct me if i am wrong. in that case even if i mention the control file in given order it takes data like
here is sample file data
Three ONE FOUR TWO
----- --- ------------- ---------
XX 1234 UNITED STATES 1/31/2007
The above data should load to the databae table in the following way
ONE TWO THREE FOUR
---- ----------- ------ --------------
1234 1/31/2007 XX UNITED STATES
for this i need to do mapping like given second column should go to the first column in the database..
Hope this helps in understanding.. I am really thankfull to you for the quick responces..
|
|
|
|
| Re: Mappingv columns using Sql Loader [message #252225 is a reply to message #252214] |
Wed, 18 July 2007 03:33 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Maybe the following exemple will help you to understand what I mean.
I create a table with 2 columns col1 and col2, a file with data for col2 and col1 (note the reverse order) and load it:
SQL> create table t (col1 varchar2(20), col2 varchar2(20));
Table created.
SQL> host type t.dat
forCol2,forCol1
SQL> host type t.ctl
LOAD DATA
INFILE t.dat
INTO TABLE t
TRUNCATE
FIELDS TERMINATED BY "," optionally enclosed by '"'
TRAILING NULLCOLS
(COL2, COL1)
SQL> host sqlldr userid=michel/michel control=t.ctl data=t.dat
SQL*Loader: Release 10.2.0.3.0 - Production on Mer. Juil. 18 10:31:48 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
SQL> select * from t;
COL1 COL2
-------------------- --------------------
forCol1 forCol2
1 row selected.
See this is in the correct fields in the end.
Regards
Michel
[edit: typos]
[Updated on: Wed, 18 July 2007 04:13] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
