Hi,
I sure hope theres no posting limit in this forum..
I have a job which will read a sequential file into an oracle table -sound familiar?- on a nightly basis.
The file has at least 3 rows of header data at the top.
The header data contains things like order#,customer# etc..
During the load, I must obtain the customer#.
Is this possible?
Thanks,
Mark
Reading header information in a flat file
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Well, I've gone past 350 posts!
Two approaches. The first may generate metadata mismatch warnings if input columns are not Char or VarChar.
Create a stage variable called CustomerNumber (or whatever). Its initial value is irrelevant, its derivation is something like:
If @INROWNUM = 1 Then InputLink.CustNo Else CustomerNumber
where the Else clause refers to the stage variable itself.
The output link from your Transformer stage must be constrained so that the header row is not processed:
@INROWNUM > 1
The Format for your sequential file is "first row is NOT header line".
The second approach is to load the customer number into a one record, two column hashed file. Its key value can be anything, such as "X", and you load the customer number into it. Could be a separate job, could be within the same job, could be BASIC code (which would have the advantage of not having to process the entire input file).
Other variations exist on this theme, but these ideas should get you under way.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Two approaches. The first may generate metadata mismatch warnings if input columns are not Char or VarChar.
Create a stage variable called CustomerNumber (or whatever). Its initial value is irrelevant, its derivation is something like:
If @INROWNUM = 1 Then InputLink.CustNo Else CustomerNumber
where the Else clause refers to the stage variable itself.
The output link from your Transformer stage must be constrained so that the header row is not processed:
@INROWNUM > 1
The Format for your sequential file is "first row is NOT header line".
The second approach is to load the customer number into a one record, two column hashed file. Its key value can be anything, such as "X", and you load the customer number into it. Could be a separate job, could be within the same job, could be BASIC code (which would have the advantage of not having to process the entire input file).
Other variations exist on this theme, but these ideas should get you under way.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518