Parsing data from multiple .txt files without delimiter with multiple data formats

[BethHembree]

Hello!

I am working with data that comes in on 20+ .txt files. I am struggling with parsing the data since there is no delimiter. Each line needed begins with "02" (which is filtered). The workflow then splits the data up based on the source of the data (XGR, XTM, X12 and RECORD). My current workflow is using Text to Columns for anything that has two spaces. This does not work on the entire string as there are areas where the data does not have breaks in space. I believe RegEx may be the solution, however each time I try to use the tool, I receive NULL" in the columns created. 

Here is a sample of each source type mentioned: 

0201-18-202402:32:45XGR       478994  9781    9898814           57    81547741NAME TRA 56954749COOL/JOEJR 05JAN24   2 MNOP     162.00      255.00     13.34 USD          A             13.34                                                                                                                                        DUD1134                       Z5629Z9989          XGR        6297731407            7819      -000000000000000000000001394.190USDZ5629ABC002652025-JAN-24 1  2024212125    2172      48790   51              55656770113

0201-04-202401:54:47X12       477950  1176    9898801           29    69194220 69194220      93762952 MO/MIC/MR  21DEC23    2    122.00     5.90V AED 100313203000003                   1.96                                                                                                                                        123DUD1                       Z9989C2950          X12        55656745096           33        -000000000000000000000000179.810USDZ5629ABC005845511-JAN-24 1  2024212123    2172      48790   51              2950015728

0201-18-202402:37:43XTM       478993  1078    9898810           10    86389962MOUSEMIN 99654150 90399492DONNDUCK/ 09JAN24   1      127.00       0.64      0.75USD                        0.75                                                                                                                                        3126Z13                       Z5629Z9989          XTM        6297731407            706       -000000000000000000000000007.260USDZ5629ABC002652025-JAN-24 1  2024212124    2172      48790   51              55656770363

02                  RECORD    479058  3       9898811           1     EGWAT521-12/22/2023-SRC                                                                                                                                                                                                                                                                    Z9989Z7179          MIMOU123   55656769298           3226      0000000000000000000000000135.850USDZ5629ABC002651925-JAN-24 1  2024212123    2172      48790   51              7179524571

I have attached an example illustration of where data should be separated.

[A1exW]

You can add data cleansing before text to column tool and config as below

[BethHembree]

Thank you. I would still need to solve for items that do not have spacing and there will also be items that have spacing that should stay together. 

[BethHembree]

To simplify, I am trying to break out this line: 0000000000000000000000000135.850USDZ5629ABC002651925-JAN-24, what I need it to do is create columns for 135.85    USD    Z5629   ABC0026519    25-JAN-24

[nagakavyasri · Accepted solution]

@BethHembree Try this: