Parsing (RegEx) and Transformation Help for a Text File

[carmstrong]

Hello,

I have a messy text file that I am trying to parse and transform. I have tried to use RegEx but did not get very far. I am attempting to turn the format in the What I Have Table to the End Goal Table. The data is separated in groups by Null rows and some groups do not contain the 6-digit sequence before the Sender’s Name. I am looking to have the data parsed and transformed into one row for each group/chunk between null rows. I have replaced all the real data for confidentiality purposes.

[IraWatt]

Hey @carmstrong,

Would you be able to give this as a text table or link the dataset rather then an image?

Thanks,

Ira

[carmstrong]

Field_1

Null

1234.00 Sender’s Name   Receiver’s Name 12345678  ABC  DE  12  123456  100.00  AB

SEC: ABC R/T: 1234-56789 Trace #: 12345678 (Random Letters and Numbers Following This)

DESC: Receiver’s Name 123456789

Null

1234.00 Sender’s Name   Receiver’s Name 12345678  ABC DE  12  123456  100.00  AB

SEC: ABC R/T: 1234-56789 Trace #: 12345678 (Random Letters and Numbers Following This)

DESC: Receiver’s Name 123456789

Null

Sender’s Name   Receiver’s Name 12345678  ABC  DE  12  123456  100.00  AB

SEC: ABC R/T: 1234-56789 Trace #: 12345678 (Random Letters and Numbers Following This)

DESC: Receiver’s Name 123456789

Null

Sender’s Name	Receiver’s Name	Code	Account Number	Amount	R/T	Trace #	Description
Sender’s Name	Receiver’s Name	12	123456	100.00	1234-56789	12345678	Receiver’s Name 123456789
Sender’s Name	Receiver’s Name	12	123456	100.00	1234-56789	12345678	Receiver’s Name 123456789
Sender’s Name	Receiver’s Name	12	123456	100.00	1234-56789	12345678	Receiver’s Name 123456789

[IraWatt]

@carmstrong just looking at your data (I assume it is dummy data), what text rule could be used to differentiate the sender name from the receiver’s name? Can we guarantee for instance that it there name will only be one word for instance? Or could there be a double space or a | to make it obvious which section is which?

[carmstrong]

The original file appears to have a double space between them.

[flying008]

Hi, @carmstrong 

1- Try the RegEx parse:

^(.+)(?=\s{3})\s{3}([^0-9]+)(?=\s\d).+(\d{2})\s{2}(\d{6})\s{2}([0-9|.]+).+R\/T:\s([0-9|-]+).+\:\s(\d{8}).+DESC:\s(.+)$

2- With the flow:

3- Get this result.

*****

If it help you , maybe you can mark this as a solution for share.