Re: Need Help with complex SQL Loader problem

Triger wrote:
> We are running Oracle 9.1.2 on a Sun Unix server running Sun 2.7
>
> I have a file that we need to load evey hour into an Oracle database the file
> happens to have a wierd format any help in parseing it would be appreciated
>
> Background
> We have a Load system that measures loads on electrical components each hour
> and records those values. each value has a unique tag (key) associated with
> it and is stored as the tag + the value for the hour
>
> a tag looks like the following : 11FISK3P We have send the load system a
> file with 50,000 tags like it and every hour it reads that file looks at the
> tags we requested extracts the data for the tag and sends it to us
>
> The file being sent is an ASCII comma separated value file. Data is coming in
> the form of:
>
> Two lines
> 1st - Date format used, tag, tag, tag, tag, tag,tag,...50,000th tag
> 2nd- date value, tag value,tag value,tag value,tag value,..50,000th tag value
>
> sample: (showing 3 tags and values)
>
> %d-%b-%y %H:%M:%S,11FISK3P,714MEDP,56TYRFT,...
> 3/4/2003 10:15,104, 8567, 5674.2, ..,
>
> we need to load it into a table as such
>
> DateTime Tag hourly value
> 3/4/2003 10:15 11FISK3P 104
> 3/4/2003 10:15 714MEDP 8567
> 3/4/2003 10:15 56TYRFT 5674.2
> .
> .
> .
> 50,000th row
>
>
> we have to keep the association between the two line the 34,567 value in line
> 1 has the tag, the 34,567 value in line 2 has the corresponding tag value, in
> essense after the date field, the values are paired up
>
> Need Help with complex SQL Loader problem

Two options:

ONE: assuming you will only consider SQL LOADER.   then put the data in a staging table and load the destination table via a pl/SQL package to parse and group the data.

TWO: assuming you are more flexible and will consider other tools.   the preprocess the raw file with a PERL program to produce a format that you can easily load with SQL LOADER.

If you'll conside contracting out the work, I can do it either way.

HTH

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost