RegEx to parse column using more than one character as delimiter

[brian]

Hello, 

I have a complex case study regarding RegEx

I'm working with Json files where all the data is flagged under following pattern:

Data

ClientInfo.Address.1.AddressLine1.1.LINE1.2.AddressType

ClientInfo.1.AddressLine2.test.1.LINE2.testing.2.AddressType.test.bis.2.Reference

I want to parse this into: 

Level1                        Index1   Level2                      Index2      Level3          Index3    Level4                        Index4    Level5

ClientInfo.Address     1            AddressLine1           1               LINE1          2             AddressType

ClientInfo                   1            AddressLine2.test    1               LINE2.test    2            AddressType.test.bis   2            Reference

The delimiter here is .<numeric value 0-9>.  

The issue is I cant predefine how many level / index (as in example, the 1st data row has only 4 level and 3 index while the 2nd data has 5 level and 4 index). So the parse function of Parse tool isn't the ideal solution 

Ree

[atcodedog05 · Accepted solution]

Hi @brian 

Here is how you can do it. Test it with your data.

Workflow:

Hope this helps : )

[brian]

Thank you so much!

It works 🙂

[atcodedog05]

Happy to help : ) @brian 

Cheers and have a nice day!