Home » RDBMS Server » Server Utilities » SQL*Loader - how to load a file like this?
SQL*Loader - how to load a file like this? [message #169556] |
Thu, 27 April 2006 10:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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!
|
|
|
|
Re: SQL*Loader - how to load a file like this? [message #169569 is a reply to message #169556] |
Thu, 27 April 2006 10:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 1730 times)
|
|
|
|
|
|
Re: SQL*Loader - how to load a file like this? [message #169673 is a reply to message #169556] |
Fri, 28 April 2006 02:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?!
|
|
|
|
Re: SQL*Loader - how to load a file like this? [message #169702 is a reply to message #169556] |
Fri, 28 April 2006 05:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?!
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 29 21:57:38 CDT 2024
|