Scenario 3: How to load multi-format (Delimited) column Flat File source data into two different targets?

Scenario 3: How to load multi-format (Delimited)  column Flat File source data into two different targets?

Ex:
cust_id,cname,phone,state_id
101,AMITH,9897045679,201
102,JAGAN,9908907869,203
103,MAHESH,890899089,201
state_id||Sname||Region
201||Karntaka||South
202||Delhi||North
203||Maharashtra||West

Theory/Analysis:
Read complete file as fixed width file. Remove first line through Start Importing row at option while Importing Source File metadata to informatica. First use REPLACESTR() function to replace '||' to ',' then use SUBSTR() till first Comma position. Check whether it's is number or not through ISNUMBER().
Assign and id for each and every record; Use Router to Route data according to ID less than/greater than Mapping Variable. Use SUBSTR, INSTR to split each record for columns.

Practical:

Step 1: Create a Mapping Variable $$SPLIT (Integer) Count

Step 2: Read File as Fixed Width (Though it's comma delimited)

Step 3:
Expression
 Create Total Eight Port
            RECORDS(Variable Port) = REPLACESTR(0,RECORDS_IN,'||', ',')
            COLUMN1 (Variable Port) = SUBSTR(RECORDS,1,INSTR(RECORDS, ',',1, 1)-1)
            Rec_ID (Variable Port) = OLD_ID + 1
            OLD_ID (Variable Port) = Rec_ID
            SPLIT_REC (Variable Port) = IS_NUMBER(COLUMN1)
            Var_Assign (Variable Port) = IIF(SPLIT_REC= 0, SETVARIABLE($$SPLIT, Rec_ID))
            Out_Rec_ID (Output Port) = Rec_ID
            Records_OUT(Output Port) = RECORDS

Note: RECORDS_IN is Input Port and coming from Source Qualifier.

Step 4:
Router:
 Create two Group
            Cust     Rec_ID < $$SPLIT
            State     Rec_ID > $$SPLIT

Step 5:
Expression:
Create Four Port
            CUST_ID (Output Port) = SUBSTR(RECORDS1,1,INSTR(RECORDS1, ',',1, 1)-1)
            CNAME (Output Port) = SUBSTR(RECORDS1, (INSTR(RECORDS1, ',',1, 1)+1), ((INSTR(RECORDS1, ',',1, 2)- INSTR(RECORDS1, ',',1, 1)-1)))
            PHONE (Output Port) = SUBSTR(RECORDS1, (INSTR(RECORDS1, ',',1, 2)+1),  ((INSTR(RECORDS1, ',',1, 3)- INSTR(RECORDS1, ',',1, 2)-1)))
            STATE_ID (Output Port) = SUBSTR(RECORDS1, (INSTR(RECORDS1, ',',1, 3)+1))

Step 6:
Expression: 
Create Three Port
            STATE_ID (Output Port) = SUBSTR(RECORDS3,1,INSTR(RECORDS3, ',',1, 1)-1)
            STATE (Output Port) = SUBSTR(RECORDS3, (INSTR(RECORDS3, ',',1, 1)+1),  ((INSTR(RECORDS3, ',',1, 2)- INSTR(RECORDS3, ',',1, 1)-1)))
            REGION (Output Port) = SUBSTR(RECORDS3, (INSTR(RECORDS3, ',',1, 2)+1))

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com