Best tool for extracting text

[JamesEOS]

Hi,

I have data strings like the below

{'gender': 'Female', 'nationality': 'IRL', 'document_type': 'passport', 'date_of_expiry': '2024-05-14', 'issuing_country': 'IRL'}

And I am looking to organise the data into columns that will show the parameter and result. However there are sometimes missing or extra fields like the below

{'document_type': 'driving_licence', 'issuing_country': 'CHE'}

What would be the best tool to scrape this data?

Thanks

[BrandonB · Accepted solution]

I think that a couple of text to columns tools should make things easy. One to split to rows on a comma delimiter, and a second to parse on the colon delimiter. Workflow is attached for reference. 

 

[JamesEOS · Accepted solution]

Thanks, hadn't thought of doubling up text to columns!

[jdunkerley79]

One other option given the structure of your data is to replace the ' with " and then you can use JSON Parse:

Obviously has a problem if there are extra " or ' in the data.

But does give a nice parsed output: