Nested JSON to flat data tables

Have you ever had to convert a file of JSON text to Excel tables? Not simple JSON, but one with a collection of dictionaries inside a dictionary inside a collection of dictionary records inside another collection of records inside a dictionary? And you want all this as old-fashioned normalised database tables? And in VBA? Here’s how I did it:

Download: Flatten JSON 20200915.xlsm

I chose as an example a Surveymonkey survey_details JSON file. I use VBA Collections, ADO.Recordsets, Scripting.Dictionary, a free class to parse JSON in VBA and some code to handle the inevitable exceptions. It’s a two pass process; one to identify all the fields in all the dictionaries, create the linking primary and foreign keys, and the second to populate recordsets with the data for those fields and write them out to Excel sheets. Or of course you could write them to a database.

If you like YouTube tutorials, here’s a recording where I walk through it:

What we start with:

What we end with:

(Update 23-Nov-2020: fix misspelled download link)

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s