I have below file format . This is one of standared format of MT940 file
{1:F01XXXXXXXXXXXX0000000000}{2:I940XXXXXXXXXXXXN}{4: :20:3067310 :25:514486527783 :28C:1/1 :60F:C181128MYR12658,54 :61:1812281228C125,12NCOL851711644078226 851711644078226 NG KIM LAI :61:1812281228D25,12NCOL851711644078226 851711644078228 NG KIM LAI :61:1812281228D2500,00NTRFRCMS FPX One-off Impl'tion Fee-Nov18 :61:1812281228D355,12NTRFCITI1819387740638659 CITI514486527783 :62F:C181128MYR17271,48 :64:C181128MYR17271,48 -}
From This file I have below requirement
First and Last Line to be removed
Read only Line with TAG = :61:
Now here is a challange , When I say read only with TAG -61 means we have to read next line of 61 also . In above example , I have to read below lines
:61:1812281228C125,12NCOL851711644078226 851711644078226 NG KIM LAI
:61:1812281228D25,12NCOL851711644078226 851711644078228 NG KIM LAI
In final output file I am expecting below Information to be populated
181228|D|2500,00|FPX One-off Impl'tion Fee-Nov18
181228|C|125,12|851711644078226
181228|D|25,12|851711644078228
Now here lets consider one of example of 61 TAG :
:61:1812281228C125,12NCOL851711644078226 851711644078226 NG KIM LAI
Here '1812281228C125,12NCOL851711644078226' from this string :
First 6 character are DATE ( YYMMDD) --->181228
Next 2 character are FundDATE (MMDD) -->1228
Next 1 character is indicator ( credit /debit ) --C
Next 5 chracter is Ammount --125,12
Rest information is not required
and from string '851711644078226 NG KIM LAI'
if First 15 character are Numeric and does not contain valus as ' CITI' then read only 15 character
else
if First 15 character are Non Numeric and does not contain valus as ' CITI' then read all the character
else First 15 character are Non Numeric and does contain valus as ' CITI' then then ignore that TAG
Hence I am expecting final output as :
Date | Indicator | Ammount | Reference
181228|D|2500,00|FPX One-off Impl'tion Fee-Nov18
181228|C|125,12|851711644078226
181228|D|25,12|851711644078228
I really need to develope a generic approach to read such type of data . Because In future I may have another TAG with same kind of details so that I can utlize the same .
Also withing that I need to add looging details such as - How many records read , How many as C and D like that . But it is a secondary requirement .
Hi
You can try to use tFileInputFullRow to read the file line by line, set the Header and Footer as 1 to remove the first line and last line, then link it to tFilterRow to filter the rows which starts with ':61:', and then write some java code to extract the field value from the line on a tJavaRow, as you explained, the length of each field is fixed, such as First 6 character are DATE ( YYMMDD) --->181228.
But I am new to Talend tool and to be honest not much aware of how to use tJavaRow . But I can use Tmap .
Now the solution that you gave provided is to filter our using ':61'
This is good but if you observer in my data there is one more line after 61 Tag . Which is also a part of that Tag . I have to use that tag as well . So need to come up with generic approach here .
Because lets say in my case if I do have :86: Tag and next line is part of that Tag .
I have to form a data by using those consucative Lines . Hwo to do that ?
One High Level Solution I am thinking is :
1. Read a File using tFileInputFullRow . Remove Header and Lats Line
2. Add tmap and add extra column name as 'TAG'
3. Write some logic which will keep the Tag name like below
TAG|Data | |=--+----------------------------------------=| |20 |3067310 | |25 |514486527783 | |28C|1/1 | |60F|C181128MYR12658,54 | |61 |1812281228C125,12NCOL851711644078226 | |61 |851711644078226 NG KIM LAI | |61 |1812281228D25,12NCOL851711644078226 | |61 |851711644078228 NG KIM LAI | |61 |1812281228D2500,00NTRFRCMS | |61 |FPX One-off Impl'tion Fee-Nov18 | |61 |1812281228D355,12NTRFCITI1819387740638659| |61 |CITI514486527783 | |62F|C181128MYR17271,48
4. And then Filter and Aggregate data ( Aggregate only consucative Lines )
|Seq3|Tag|data | |=---+---+-----------------------------------------------------------------=| |1 |61 |1812281228C125,12NCOL851711644078226,851711644078226 NG KIM LAI| |2 |61 |1812281228D25,12NCOL851711644078226,851711644078228 NG KIM LAI | |3 |61 |1812281228D2500,00NTRFRCMS,FPX One-off Impl'tion Fee-Nov18 | |4 |61 |1812281228D355,12NTRFCITI1819387740638659,CITI514486527783 | '----+---+------------------------------------------------------------------'
5. And the Create a Below Data
date | Payload | RefID
181228|D|2500,00|FPX One-off Impl'tion Fee-Nov18
181228|C|125,12|851711644078226
181228|D|25,12|851711644078228
I really need to delope a generic approach for the same .
