Re: Possible with SQLLoader?

From: Ralf Butter <ralf_butter_at_drewag.de>
Date: 18 Mar 2003 22:24:35 -0800
Message-ID: <822f10df.0303182224.4068ea32_at_posting.google.com>

[Quoted] Hello, Bernhard,

this should solve your problem:

(I found it some time ago on asktom.oracle.com  look for "How to load fixed position file into multiple table")  

Load data
infile ...
replace
INTO TABLE xxx
(

value   Position(1:5),
text    Position(7:19),
code    Position(20:26)

)
INTO TABLE xxx
(

value   Position(1:5),
text    Position(7:19),
code    Position(27:33)

)
INTO TABLE xxx
(

value   Position(1:5),
text    Position(7:19),
code    Position(34:40)

)
and so on

hth

Ralf

"Bernhard Graeuler" <fuerspam_at_gmx.de> wrote in message news:<b57gq5$2642be$1_at_ID-49408.news.dfncis.de>...
> Hello!
>
> We have an ASCII file which we want to load into a table. The file looks
> like the following example:
>
> value text codelist
> 0 text 1 Codexx1Codexx3Codexx2Cd33222 123456
> 3 longer text 2 11111112222222333333344444445555555
> 22 another text aaaaa ssaaff ffee sffeewwwfee eew
>
>
> As you can see,
> value is saved in position 1..5,
> text is saved in position 7..19, and
> codelist is saved in position 21..55.
> Every code in the codelist has a length of seven characters.
>
> Now we need a control file for the SQLLoader which imports every code in the
> codelist as a single record, keeping the other values of the current row of
> the ASCII file. So the resulting table has to have the following data:
>
> value text code
> 0 text 1 Codexx1
> 0 text 1 Codexx3
> 0 text 1 Codexx2
> 0 text 1 Cd33222
> 0 text 1 123456
> 3 longer text 2 1111111
> 3 longer text 2 2222222
> 3 longer text 2 3333333
> 3 longer text 2 4444444
> 3 longer text 2 5555555
> 22 another text aaaaa
> 22 another text ssaaff
> 22 another text ffee s
> 22 another text ffeewww
> 22 another text fee eew
>
> Now to the question(s):
> Is this possible with the SQLLoader?
> And if you have the time to answer this: How is that possible?
>
> Don't know if that matters, the Oracle clients version number is 8.0.5, and
> an an upgrade to 8.1.7 would be no problem.
>
> Thank you!
>
> Best regards
>
> Bernhard
Received on Wed Mar 19 2003 - 07:24:35 CET