Saturday, February 9, 2013

How to Separate Header and Footer from Flat file


Load the Header records into one target table, say TRGT01, footer records to TRGT02 and detail records to another TRGT03:

Source File

Header”ABC”
@@@@@@@@@@@@

Detail”123”
Detail”456”
Detail”789”
Detail”012”

@@@@@@@@@@@@
Footer”Efg”

1.Create Source definition for the file and connect to an Expression

2.In Expression create two variable ports
  •         V_HEADER= IIF (SUBSTR(COLUMN,1,1) =’Header’,1,0)
  •         V_FOOTER= IIF (SUBSTR(COLUMN,1,1) = ‘Footer’,1,0)

Also create two output ports
  •          O_HEADER=V_HEADER
  •          O_FOOTER=V_FOOTER


3.Create a Router with two user groups
  •         In first group give condition such that IIF(O_HEADER) ='1' then pass to TRGT01
  •         In second group IIF(O_FOOTER) ='1' then pass to TRGT02
  •          And the default group to TRGT3(this will hold the detail records)

  

No comments:

Post a Comment

Thank you :
- kareem