.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

[gawa]

hi @BRRLL99 Thank you for you private message you sent to me yesterday. According to your request, I surely looked into your case.

It is pars-able by using a lot of tools like this. I recommend to parse separately for LEFT part and RIGHT part, and JOIN them later.

Hope this snapshot will inspire you so you can achieve your demand by yourself. 

At the end, I regret to say that private message should not be used for asking somebody for providing/expediting the solution. This is public community, and we help each other on a voluntary basis....in my humble opinion😅 

[BRRLL99]

Noted!
this my first time working with .txt file, please share the workflow . It would be immensely helpful to understand the process you're referring to

[gawa · Accepted solution]

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