Home » RDBMS Server » Server Utilities » Mappingv columns using Sql Loader
Mappingv columns using Sql Loader [message #252154] Wed, 18 July 2007 00:42 Go to next message
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 #252180 is a reply to message #252154] Wed, 18 July 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Utilities
Part II SQL*Loader
Chapter 9 Field List Reference
Section Specifying Columns and Fields
Subsection Specifying Filler Fields

Regards
Michel
Re: Mappingv columns using Sql Loader [message #252190 is a reply to message #252154] Wed, 18 July 2007 02:00 Go to previous messageGo to next message
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 #252194 is a reply to message #252190] Wed, 18 July 2007 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you name the column in the control as it is in the data file. The order of columns in the table does not matter.
And as there is more columns in the data file than in the table I don't know how you can prevent from losing some.

Regards
Michel
Re: Mappingv columns using Sql Loader [message #252202 is a reply to message #252154] Wed, 18 July 2007 02:31 Go to previous messageGo to next message
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 #252205 is a reply to message #252202] Wed, 18 July 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your control file must fit the column order in THE FILE not the table:
LOAD DATA
INFILE test_order.csv
INTO TABLE wwops.test_order
TRUNCATE
FIELDS TERMINATED BY "," optionally enclosed by '"'
TRAILING NULLCOLS 
(
THREE,
ONE,
FOUR , 
TWO DATE 'DD/MM/YY'
)

Regards
Michel
Re: Mappingv columns using Sql Loader [message #252214 is a reply to message #252154] Wed, 18 July 2007 02:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Mappingv columns using Sql Loader [message #252229 is a reply to message #252154] Wed, 18 July 2007 04:03 Go to previous message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Thank you very much Michel ,,,,


Its working fine.. u r genious... thanks man .. thanks for your promply help..
Previous Topic: reg:sql loader
Next Topic: import problem
Goto Forum:
  


Current Time: Sun Dec 11 08:29:54 CST 2016

Total time taken to generate the page: 0.09359 seconds