OraFAQ Forum: Server Utilities » SQL*Loader - how to load a file like this?

Home » RDBMS Server » Server Utilities » SQL*Loader - how to load a file like this?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader - how to load a file like this? [message #169556]

Thu, 27 April 2006 10:10

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Hi,

i'm struggling with a task to load a file via SQL*Loader. The Format i have to load is below:

                                                                                                                                                       
                                                                                                                                                                                                                                                                        
    ID            :   1     
        
    DATE         NUMBER      
 ----------   --------------- 
 01/03/2004      63.352,830              
 02/03/2004      63.352,830               
 03/03/2004      63.352,830             
 04/03/2004      63.352,830               


                                                                                                           
   ID            :     2       
                                                                                                                                                       
    DATE      NUMBER      
 ---------- --------------- 
 01/03/2005      60.000,000              
 02/03/2005      60.000,000             
 03/03/2005      60.000,000             
 04/03/2005      60.000,000               
 05/03/2005      60.000,000      

                                                                                                             
    ID        :     3                                                           
                                                                                                                                                       
                                                                                                                                                       
    DATE      NUMBER      
 ---------- --------------- 
 01/03/2006      50.999,830              
 02/03/2006      50.999,830               
 03/03/2006      50.999,830

The Problem is, i have to load the columns "DATE" and "NUMBER" and in the target-table will be a column "ID" where i have to put the "ID" in.

The "Dates" will be different, so there could be 10 lines below "ID" or 50.

I hope i explained the problem properly. If you have any question, abaut the task, please let me know.

Any help is really appreciated, even if you're saying: It's not possible...

thanks!

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169561 is a reply to message #169556]

Thu, 27 April 2006 10:19

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Is this the actual datafile you are about to load?
Else,just post a sample datafile.
Post your table DDL and your business rules.

[Updated on: Thu, 27 April 2006 10:19]

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169569 is a reply to message #169556]

Thu, 27 April 2006 10:47

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

it's sample data.

I attached the real file.

There are no further business rules, only the id has to be in a table column, so the values can get assigned to the id.

The table DDL would look similar to this:

create table id_test
(
ID_column           NUMBER,
FECHA               DATE,
NUM_PARTIC          NUMBER,
VALOR_LIQ           NUMBER,
IMP_MONEDA_FONDO    NUMBER,
FIXING              NUMBER,
IMP_MONEDA_PAG      NUMBER,
DIVISA              VARCHAR2(10),
COM                 NUMBER,
COM_GESTION         NUMBER,
COM_RETROC          NUMBER
)

Attachment: orafaq.txt
(Size: 26.51KB, Downloaded 1749 times)

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169570 is a reply to message #169569]

Thu, 27 April 2006 10:51

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

The file format is horrible.
Is that a tab delimited?
Are you grabbing the ID From your input datafile?
like this
ID : 654415 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX "A"

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169578 is a reply to message #169570]

Thu, 27 April 2006 11:25

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You may want to look into PERL or some scripting to parse through the file. SQLLDR has limited options.
Or
Load the file as-is using external tables and use SQL methods ( even that may be little tricky).
I will try to work on a script and keep you posted.
Regards.

[Updated on: Thu, 27 April 2006 11:25]

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169627 is a reply to message #169556]

Thu, 27 April 2006 16:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I second the use of Perl.
I had to use Perl on some old, old IIS logfiles which only had the date in a single line to the top of the file.
After extracting the date, I prepended it onto every line.
Alternatively you could load the data into a "work"table which would have auto generated sequence numbers as pseudo line numbers.
Then write PL/SQL to load actual table.

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169673 is a reply to message #169556]

Fri, 28 April 2006 02:28

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Hi,

external tables aren't possible, because it's Oracle8i. I could try to use PHP, because i have no Perl experience.

Quote:

Are you grabbing the ID From your input datafile?
like this
ID : 654415 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX "A"

@Mahesh: Yes i have to grab the id from the file.
So i would need to put the 654415 as an id in the first column.

Thanks anyway, maybe you could let me know your ideas, so i would have a better starting point?!

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169684 is a reply to message #169627]

Fri, 28 April 2006 03:09

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

@anacedent: Do you have an idea how the Control-file for the whole file would look like? I would like to try your idea with a working table.

I could also save the file as *.csv, if this would be more easier...

[Updated on: Fri, 28 April 2006 03:11]

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169702 is a reply to message #169556]

Fri, 28 April 2006 05:21

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Hi,

i used UTL_FILE to delete everything that is not necessary from the file. So, the task would be to load a file (csv-format) in this format:

65456								
01/07/2005	63.352,830	106,000,000	6.715.399,98     1,00000000	6.715.399,98	USD	1,500	275,98	137,99
02/07/2005	63.352,830	106,000,000	6.715.399,98     1,00000000	6.715.399,98	USD	1,500	275,98	137,99
03/07/2005	63.352,830	106,000,000	6.715.399,98     1,00000000	6.715.399,98	USD	1,500	275,98	137,99
04/07/2005	63.352,830	106,000,000	6.715.399,98     1,00000000	6.715.399,98	USD	1,500	275,98	137,99
05/07/2005	63.352,830	105,280,000	6.669.785,94     1,00000000	6.669.785,94	USD	1,500	274,10	137,05
06/07/2005	63.352,830	104,830,000	6.641.277,17     1,00000000	6.641.277,17	USD	1,500	272,93	136,47
65498								
08/08/2005	69.199,440	115,970,000	8.025.059,06     1,00000000	8.025.059,06	USD	1,500	329,80	164,90
09/08/2005	69.199,440	117,120,000	8.104.638,41     1,00000000	8.104.638,41	USD	1,500	333,07	166,54
10/08/2005	69.199,440	118,650,000	8.210.513,56     1,00000000	8.210.513,56	USD	1,500	337,42	168,71
11/08/2005	69.199,440	117,080,000	8.101.870,44     1,00000000	8.101.870,44	USD	1,500	332,95	166,48
12/08/2005	69.199,440	117,740,000	8.147.542,07     1,00000000	8.147.542,07	USD	1,500	334,83	167,42
13/08/2005	69.199,440	117,740,000	8.147.542,07     1,00000000	8.147.542,07	USD	1,500	334,83	167,42
56458								
15/09/2005	67.878,980	124,460,000	8.448.217,85     1,00000000	8.448.217,85	USD	1,500	347,19	173,60
16/09/2005	67.878,980	126,270,000	8.571.078,80     1,00000000	8.571.078,80	USD	1,500	352,24	176,12
17/09/2005	67.878,980	126,270,000	8.571.078,80     1,00000000	8.571.078,80	USD	1,500	352,24	176,12
18/09/2005	67.878,980	126,270,000	8.571.078,80     1,00000000	8.571.078,80	USD	1,500	352,24	176,12
19/09/2005	67.878,980	127,260,000	8.638.278,99     1,00000000	8.638.278,99	USD	1,500	355,00	177,50
20/09/2005	67.878,980	127,300,000	8.640.994,15     1,00000000	8.640.994,15	USD	1,500	355,11	177,56

"65456","65498" and "56458" are the ID's that i have to write in the first column from the table.

Maybe someone got an idea now?!

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #169823 is a reply to message #169702]

Fri, 28 April 2006 21:29

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch10.htm#1007588

Report message to a moderator

Re: SQL*Loader - how to load a file like this? [message #172624 is a reply to message #169556]

Wed, 17 May 2006 07:13

buck444
Messages: 80
Registered: January 2006
Location: Luxembourg

Member

Barbara, thanks so much for the link.

i solved the above problem with UTL_FILE, but the case studies are very interesting!

Report message to a moderator

Previous Topic:	Urgent: ORA-29280
Next Topic:	EXPORT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 08:19:11 CST 2024