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