Parsing (RegEx) and Transformation Help for a Text File

Question

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.

flying008 · Answer

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.

carmstrong · Answer

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

IraWatt · Answer

@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?