Import a text file with random format

[Will_Cheng]

Hi Everyone, 

I am new to Alteryx and i am trying to build a new workflow, current face an data cleaning issue and hopefully i can get some advice from here. 

My input data source is a txt format file. And i would like to clean it up before i perform any manipulation. Ideally the below is the example of expected outcome. 

1		USA COMPANY
REF-000-01		DAILY ACTIVITY SUMMARY
SEP. 20, 2020		INVESTMENT Account SYUMIA		PAGE 1
	Opening Balance			Closing Balance
	12,345,678.90			12,345,678.90
		Credits	DEBITS
Customer Activity		11,222.13	11,222.33
WITHHOLDING TAX			.10

I tried few options, the 1st is fixed the width text file, but it would cause a problem because im not sure the data file width is fixed. 

In 2nd option, i tried is using RegEX + Text to Columns. But the most difficult part is that at "WITHOLDING TAX" Row, ".10" will move to credits side because there is no amount on credit column.

Grateful for help...

[Blake]

Hi @Will_Cheng 

Check out this post - it should help you.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/4gb-txt-file-without-delimiter-to-open...

You might have to do something to concatenate your header info but it should handle your data nicely. 

Good luck!