topic Read MT940 File in Talend Studio

Read MT940 File

gtaware — Sat, 16 Nov 2024 04:06:46 GMT

Hallo All Experts ,

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

:61:1812281228D2500,00NTRFRCMS
FPX One-off Impl'tion Fee-Nov18

:61:1812281228D355,12NTRFCITI1819387740638659
CITI514486527783

Out of which immediate next line is Transaction Description

:61:1812281228D2500,00NTRFRCMS
FPX One-off Impl'tion Fee-Nov18

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 .

Re: Read MT940 File

Anonymous — Fri, 15 Nov 2019 08:51:04 GMT

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.

Regards
Shong

Re: Read MT940 File

gtaware — Mon, 18 Nov 2019 08:34:00 GMT

Thanks Shong .

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 .