Parsing complicated JSON

[francoza]

Hi there,

I'm trying to parse a heavily nested JSON file to get the attached end result (basically a matrix of the data). I found several posts here which have been helpful, but unfortunately I've reached a point where the RegEx parse returns Null for some line items, which prevents me from joining the data after I apply a filter.

Any help would be appreciated

Thanks!

Franco

[francoza · Accepted solution]

I've tried building this from the bottom-up and I seem to be getting there. Attached is the solution I'm using (happy to hear from anyone if they have a better solution).

[jwalder · Accepted solution]

@francoza I have found using text-to-columns is way more useful in dealing with JSON structures. The attached is just a quick sketch of one way to use it to deal with data like you have. The data you are pulling is in fact hierarchical in nature, so creating the flat set you gave an example of in Excel isn't necessarily the best answer. I used the calculated [Depth] a lot in how I played it out so that it wasn't completely dependant on this JSON structure, but you could simplify a lot of it by looking for specific [name#] values if your JSON is relatively consistent.

Cheers!

[francoza]

This is great, thank you !