Sequential file different number of fields every row

gomez · Post by **gomez** » Mon Jan 22, 2007 11:04 am

Hi All:
I have to extract a sequential file which has the following structure:
Header
Detail1
Detail2
Trailer
This structure repeats itself in the file. The first 6 characters in a line indicates the type - Header/Detail/Trailer.
However Detail line can be 1 or many between a header and trailer.
I want to extract them all, transform the detail row and load them all into a single file obviously in the exact order. Any clue on how this can be done?

I referred to the post

Code: Select all

http://www.dsxchange.com/viewtopic.php?t=103989&postdays=0&p

in the forum which talks about a similar scenario. The difference is in the repeating patterns in the same file
Appreciate any help on this

Thanks

ArndW · Post by **ArndW** » Mon Jan 22, 2007 11:10 am

If you do not need the Header/Trailer rows then just ignore them (drop them in a transform stage). If you need some data from a Header line, put it into a stage variable in the same transform stage where you drop these rows.

gomez · Post by **gomez** » Mon Jan 22, 2007 11:13 am

Thanks ArndW. I would need the header/trailer data as well. I would have to bring them out in the same order as
header
detail1
detail2
trailer
header
detail1
detail2
detail3
trailer
And a header/detail/ trailer consists of different number of fields
Do I bring them as a single field into the transformer. Then using staging variables route them to three different links from the transformer into row splitter stages. Now how do I collect them in the same order as I read into the final target
Thanks

ArndW · Post by **ArndW** » Mon Jan 22, 2007 11:23 am

You can use the method you suggested and use the @INROWNUM variable as a key to rejoin them after processing, or just declare additional columns in the input file that aren't used, just mark them as "incomplete column - retain" and don't use them for the record formats that don't use them.

gomez · Post by **gomez** » Mon Jan 22, 2007 1:48 pm

Thanks ARNDW. Yes I am actually stuck in the next step. I have now generated 3 files - Header, Detail, Trailer using Transformer/Row splitter. However I wouldnt be able to use a Merge Stage to merge the Header file, Detail file and Trailer file since they have different columns. I wouldnt be able to use a Link Collector either since there is no single column configuration that I can define in the stage.
Thanks

gomez · Post by **gomez** » Mon Jan 22, 2007 4:12 pm

My job design is like this

Code: Select all

Source --> Transformer(to split 3 data streams) -->Row Splitter (three of them one  for each stream)---> Transformer(to transform data - three of them for each stream)--->  Row Merge (three of them for each stream)---> Transform(to create a key column-three of them for each stream) ---> Link Collector --->Output

My input files to the Link Collector are
Header file,
Detail file,
Trailer file
I use Sort/Merge Algorithm in Link Collector.
However, the output from Link Collector is like
Trailer1
Detail1
Detail1
Header1
Header2
Detail2
Detail2
Trailer2
Trailer3
Detail3
Detail3
Header3

Any clue on why this happens?

Thanks

vmcburney · Post by **vmcburney** » Mon Jan 22, 2007 8:24 pm

Different parts of your job are processing at different speeds which mixes up the order of your rows. You need to add a sequence number to the start of all header, detail and tail records using an @inrownum and send it down all three paths, in your collector sort by that sequence number.

gomez · Post by **gomez** » Thu Jan 25, 2007 3:12 pm

Thanks vmcburney. I tried this and it works. I like the way you also explained to why this happened. Appreciate that. Thanks