Re: Need Help with complex SQL Loader problem

Hi there,

I don't know if you can get SQL*Loader to read your file directly (it supports a wide range of input formats, the really hard part is to decode the documentation). But as you're running under Unix, I would try to convert the input file to a more easily readable format with the appropriate utilities, e.g. awk to generate 2 files, one with the tags (one per line) and one with the values, paste the files together via 'paste' and then feed the result into SQL*Loader.

Regards
Michael

trewetuyt_at_mister.com (Triger) wrote in message news:<3e96e150$0$86527$45beb828_at_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 Mon Apr 14 2003 - 11:31:09 CDT