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.
Hey @carmstrong,
Would you be able to give this as a text table or link the dataset rather then an image?
Thanks,
Ira
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 |
@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?
The original file appears to have a double space between them.
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.