.txt file data cleaning

BRRLL99

Hi Team,

I have following data in .txt file(attached), my expected output also been attached

since data is in .txt for input i used \0( all data coming in one column) , \s(data coming in 16 columns)

REPORT ID: BSS-120 data needs to be ignored(for sample i have shown only BSS-120, it can BSS-130, 990 etc)

as shown in the sample input, for REPORTING FOR: 100157 ABC RQSTZ there is no total values, when there is no total values it should be blank.

I'm looking to get the result as shown in expected output( different variety is also acceptable), I need to join reporting for column with other dataset.

Input file name : SAMPLE

EXPECTED OUTPUT (Alteryx):

REPORT DATE	REPORT ID	SETTLEMENT CURRENCY	REPORTING FOR	ROLLUP TO	FUNDS ARQ ENTIT	VALUE	COUNT	CREDIT AMOUNT	DEBIT AMOUNT	TOTAL AMOUNT
24Dec23	BSS-110	MYR	100157 ABC RQSTZ	200167 RTCQ SUMQ	200289 RQC BARR M'SIA
24Dec23	BSS-110	MYR	199157 ABC RQSTZ	280167 RTCQ SUMQ	271289 RQC BARR M'SIA	INTERCHANGE VALUE
						TOTAL ACR	178	144,473.61	580	234,918CR
						TOTAL ISSR	0	0
						TOAL OTHER	0	0	0	0
						TOTAL INTERCHANGE VALUE	178	144,473.61	580	234,918CR
						RECHARGE VALUE
						TOTAL ACR	188	104,479.61	589	234,918CR
						TOTAL ISSR	0	0	0	0
						TOAL OTHER	0	0	0	0
						TOTAL RECHARGE VALUE	188	104,479.61	589	234,918CR
						CARD CHARGES
						TOTAL ACR	143	124,479.61	89	134,918CR
						TOTAL ISSR	0
						TOAL OTHER	0
						TOTAL CARD CHARGES	143	124,479.61	89	134,918CR
24Dec23	BSS-110	JPY	399157 ABC RQSTZ	480167 RTCQ SUMQ	570289 RQC BARR M'SIA	INTERCHANGE VALUE
						TOTAL ACR	108	104,473.61	480	934,918CR
						TOTAL ISSR	0	0	0	0
						TOAL OTHER	0	0	0	0
						TOTAL INTERCHANGE VALUE	108	104,473.61	480	934,918CR
						RECHARGE VALUE
						TOTAL ACR	188	104,079.61	509	834,918CR
						TOTAL ISSR	0	0	0	0
						TOAL OTHER	0	0	0	0
						TOTAL RECHARGE VALUE	188	104,079.61	509	834,918CR
						CARD CHARGES
						TOTAL ACR	148	124,479.61	89	134,918CR
						TOTAL ISSR	0	0	0	0
						TOAL OTHER	0	0	0	0
						TOTAL CARD CHARGES	148	124,479.61	89	134,918CR

SAMPLE.txt

gawa

@BRRLL99 Here you go.You will see how to read .txt file without Input tool, too.

1243382_gawa.yxzp