I get a data dump that has a column called Metatdata with the following values
[{"templateId":"InternalMetadata","fields":{"Operator":"JSmith","Group ID":"8","Document Num":"3452","Doc Edit Date":"1/1/2023 12:18:36 PM","Description":"SecondAmendment_of_the_Agreement","SubType":"na","Keywords":"ACQ FIN","Doc Create Date":"1/1/2022 2:00:00 PM","individual ID":"43","Department":"ACQ","Author":"JSmith","Final":"True","DocType":"FIN"}}]
and a second row
[{"templateId":"InternalMetadata","fields":{"Operator":"AJackson","Group ID":"7","Document Num":"2345","Doc Edit Date":"5/1/2023 12:18:36 PM","Description":"Proof_of_Concept_New_Model","SubType":"na","Keywords":"Analytics Excel","Doc Create Date":"4/1/2023 12:00:00 PM","individual ID":"78","Department":"BI","Author":"AJackson","Final":"True","DocType":"Excel"}}]
What I'm trying to do is split this data into columns as follows
| TemplateID | Fields | Operator | Group ID | Document Num | Doc Edit Date | Description | SubType | Key Words | Doc Create Date |
| InternalMetadata | { | JSmith | 8 | 3452 | 1/1/2023 | SecondAm... | na | ACQ FIN | 1/1/2022 |
| InternalMetadata | { | AJackson | 7 | 2345 | 5/1/2023 | Proof_Of_C... | na | Analytics Excel | 4/1/2023 |
Essentially I need to find "*": and make the * into column headers, Then find :"*" and make the * the value for those columns. I looked at some similar examples where regex was used but my knowledge of Perl is zero and I wasn't able to figure out the necessary regular expressions. The Fields description doesn't follow this pattern but the values always start with the same expression [{"templateId":"InternalMetadata","fields":{ and I'd be fine if that was just gone. I would like all columns from operator to doc type.
