Issue with reading a file

Four Stars

Issue with reading a file

HI,

I have a use case where I have read below file and extract certain tag values and create a new output file

Example: Basically the file beings with a header then data and ends with footer. The columns tags are separated by a ":". My requirement is to extract the TAG 86 (:86) from each record and extract only values for the keyword "ACC_NUM". Each record is separated by "-"

HDR20180502_9041.dat

Smiley TongueMT:HGT

Smiley TongueCI:CASH

Smiley TongueND:

:20:01876529

:61:30986,28NF5678//21812367     1

:86:/BROKER:MERILY FEN/PO/1112/BNT/789647/ACC_NUM/89543209865/USNML/9762

:61:15678,50NMS89//21830877      1

:86:/PT/PY/BROKER: 6805 MERILY

IDD_CLEARING/PO/1112/ACC_NUM/018765432975678/USNML/9388

115278

-

Smiley TongueMT:HJJ

Smiley TongueCI:CASH

Smiley TongueND:

:20:3098789

:61:30988,28MNG5678//218128937     1

:86:/PT/FT/METLIFE /BNT/53346789647

:61:145698,578JHS89//21839847      1

:86:/PT/PY/ACH CREDIT RECEIVED /CO CALAMOS

BTN/53478912956945678/ACC_NUM/018765432975674/USNML/9380

115275

-

TLRUA 38800000001

 

My expected output:

89543209865

018765432975678

018765432975674

 

note: ACC_NUM is a non mandatory field

 

Thank you,

Brat


Accepted Solutions
Nine Stars

Re: Issue with reading a file

Hi Brat,

 

Read full row as one record and check using index function if you have any acc_num in row. using tMap you can filter remaining records and using substring function you can get desired output.

 

Regards,

 

Veeru Boppudi

All Replies
Nine Stars

Re: Issue with reading a file

Hi,

Please provide more details regarding requirement.

as you mentioned, columns are separated by ":", the data you provided is like below or  each tag you will get in different line?

 

:MT:HGT:CI:CASH:ND::20:01876529:61:30986,28NF5678//21812367     1:86:/BROKER:MERILYEN/PO/1112/BNT/789647/ACC_NUM/89543209865/USNML/9762:61:15678,50NMS89//21830877      1:86:/PT/PY/BROKER: 6805 MERILYIDD_CLEARING/PO/1112/ACC_NUM/018765432975678/USNML/9388115278

Regards,

Veeru Boppudi
Four Stars

Re: Issue with reading a file

Hi Veeru,

Each tag will start in a different line.

 

Thanks,

Brat

Nine Stars

Re: Issue with reading a file

Hi Brat,

 

Are you looking for following output?

 

Acc_num1.PNGAcc_num2.PNG

 

Here i assumed that for every account number there will be "USNML" entry.

 

Regards,

Veeru Boppudi
Four Stars

Re: Issue with reading a file

Thanks Veeru..yes that is the expected result I was looking for.

What is the file input delimiter and record separator you used to build the below program?

Nine Stars

Re: Issue with reading a file

Hi Brat,

 

Read full row as one record and check using index function if you have any acc_num in row. using tMap you can filter remaining records and using substring function you can get desired output.

 

Regards,

 

Veeru Boppudi
Four Stars

Re: Issue with reading a file

Got it. Thank you - Veeru.

 

Regards,

Brat

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.