Home » RDBMS Server » Server Utilities » problem in loading data into table (10gR2 Linux)
| problem in loading data into table [message #519724] |
Tue, 16 August 2011 09:01  |
 |
9390512774
Messages: 103
Registered: January 2011
Location: hyd
|
Senior Member |
|
|
Hi all,
i have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
Please tell me how to write control file to load this data into the above revenue table.
Thanks in Advance.
|
|
|
|
| Re: problem in loading data into table [message #519731 is a reply to message #519724] |
Tue, 16 August 2011 10:18  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It is easy to do it with an external table and the classical column to row transformation.
SQL> create table revenue
2 (
3 person varchar2(23),
4 month varchar2(3),
5 rev_amt number
6 )
7
SQL> /
Table created.
SQL> create table ext (
2 person varchar2(23),
3 Jan number,
4 Feb number,
5 Mar number,
6 Apr number,
7 May number,
8 Jun number,
9 Jul number,
10 Aug number,
11 Sep number,
12 Oct number,
13 Nov number,
14 Dec number)
15 organization external
16 (type ORACLE_LOADER default
17 directory MY_DIR
18 access parameters (fields terminated by ',')
19 location ('t.txt'))
20 /
Table created.
SQL> host type t.txt
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
M³ller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
SQL> insert into revenue
2 select person,
3 decode(line, 1,'Jan', 2,'Feb', 3,'Mar', 4,'Apr', 5,'May', 6,'Jun',
4 7,'Jul', 8,'Aug', 9,'Sep', 10,'Oct', 11,'Nov', 12,'Dec'),
5 decode(line, 1,Jan, 2,Feb, 3,Mar, 4,Apr, 5,May, 6,Jun,
6 7,Jul, 8,Aug, 9,Sep, 10,Oct, 11,Nov, 12,Dec)
7 from ext,
8 (select level line from dual connect by level <= 12)
9 /
120 rows created.
SQL> select * from revenue where rownum <= 13;
PERSON MON REV_AMT
----------------------- --- ----------
Schnyder Jan 345
Weber Jan 234
Keller Jan 596
Meyer Jan 987
Holzer Jan 509
M³ller Jan 456
Binggeli Jan 487
Stoller Jan 596
Marty Jan 94
Studer Jan 784
Schnyder Feb 223
Weber Feb 234
Keller Feb 276
13 rows selected.
Regards
Michel
[Updated on: Tue, 16 August 2011 10:19] Report message to a moderator |
|
|
|
| Re: problem in loading data into table [message #519775 is a reply to message #519731] |
Tue, 16 August 2011 18:20 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses SQL*Loader, does not require a staging table, and will work if the data file is on the server or the client.
SCOTT@orcl_11gR2> create table revenue
2 (
3 person varchar2(23),
4 month varchar2(3),
5 rev_amt number
6 )
7 /
Table created.
SCOTT@orcl_11gR2> host type t.txt
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Mⁿller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
SCOTT@orcl_11gR2> host type test.ctl
load data
infile t.txt
into table revenue
fields terminated by ','
(person, rev_amt, month constant "Jan")
into table revenue
fields terminated by ','
(person position (1),
Jan filler,
rev_amt, month constant "Feb")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler,
rev_amt, month constant "Mar")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler,
rev_amt, month constant "Apr")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler,
rev_amt, month constant "May")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler,
rev_amt, month constant "Jun")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
rev_amt, month constant "Jul")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler,
rev_amt, month constant "Aug")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler,
rev_amt, month constant "Sep")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler,
rev_amt, month constant "Oct")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler, Oct filler,
rev_amt, month constant "Nov")
into table revenue
fields terminated by ','
(person position (1),
Jan filler, Feb filler, Mar filler, Apr filler, May filler, Jun filler,
Jul filler, Aug filler, Sep filler, Oct filler, Nov filler,
rev_amt, month constant "Dec")
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 16 16:16:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 10
SCOTT@orcl_11gR2> select * from revenue
2 /
PERSON MON REV_AMT
----------------------- --- ----------
Schnyder Jan 345
Weber Jan 234
Keller Jan 596
Meyer Jan 987
Holzer Jan 509
Mⁿller Jan 456
Binggeli Jan 487
Stoller Jan 596
Marty Jan 94
Studer Jan 784
Schnyder Feb 223
Weber Feb 234
Keller Feb 276
Meyer Feb 345
Holzer Feb 154
Mⁿller Feb 125
Binggeli Feb 347
Stoller Feb 237
Marty Feb 234
Studer Feb 567
Schnyder Mar 122
Weber Mar 123
Keller Mar 347
Meyer Mar 645
Holzer Mar 876
Mⁿller Mar 678
Binggeli Mar 458
Stoller Mar 976
Marty Mar 235
Studer Mar 235
Schnyder Apr 345
Weber Apr 457
Keller Apr 134
Meyer Apr 567
Holzer Apr 347
Mⁿller Apr 235
Binggeli Apr 347
Stoller Apr 876
Marty Apr 763
Studer Apr 753
Schnyder May 324
Weber May 456
Keller May 743
Meyer May 834
Holzer May 146
Mⁿller May 878
Binggeli May 235
Stoller May 346
Marty May 54
Studer May 124
Schnyder Jun 244
Weber Jun 287
Keller Jun 545
Meyer Jun 567
Holzer Jun 788
Mⁿller Jun 237
Binggeli Jun 864
Stoller Jun 567
Marty Jun 567
Studer Jun 575
Schnyder Jul 123
Weber Jul 234
Keller Jul 216
Meyer Jul 789
Holzer Jul 174
Mⁿller Jul 567
Binggeli Jul 689
Stoller Jul 126
Marty Jul 237
Studer Jul 864
Schnyder Aug 123
Weber Aug 123
Keller Aug 456
Meyer Aug 234
Holzer Aug 986
Mⁿller Aug 237
Binggeli Aug 235
Stoller Aug 879
Marty Aug 457
Studer Aug 235
Schnyder Sep 345
Weber Sep 678
Keller Sep 124
Meyer Sep 678
Holzer Sep 568
Mⁿller Sep 788
Binggeli Sep 764
Stoller Sep 125
Marty Sep 325
Studer Sep 753
Schnyder Oct 121
Weber Oct 656
Keller Oct 753
Meyer Oct 973
Holzer Oct 246
Mⁿller Oct 237
Binggeli Oct 964
Stoller Oct 568
Marty Oct 753
Studer Oct 864
Schnyder Nov 345
Weber Nov 341
Keller Nov 346
Meyer Nov 456
Holzer Nov 324
Mⁿller Nov 324
Binggeli Nov 624
Stoller Nov 124
Marty Nov 577
Studer Nov 634
Schnyder Dec 197
Weber Dec 567
Keller Dec 456
Meyer Dec 125
Holzer Dec 987
Mⁿller Dec 778
Binggeli Dec 347
Stoller Dec 753
Marty Dec 346
Studer Dec 678
120 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 13:36:19 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
