Need Help with complex SQL Loader problem

From: Triger <trewetuyt_at_mister.com>
Date: 11 Apr 2003 10:39:07 -0500
Message-ID: <3e96e150$0$86527$45beb828@newscene.com>

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 Received on Fri Apr 11 2003 - 10:39:07 CDT