How to Import JSON to Excel Using VBA

16 Aug How to Import JSON to Excel Using VBA

Howdee! It’s becoming increasingly more common for data to be generated in a JSON format as opposed to XML. XML was widely used for years, but recently JSON has started to become the preferred method of data exchange. Many REST APIs have the option to return both but the majority that I interact with default to returning JSON formatted data. Therefore, as excel ninjas, it’s important to understand how to import JSON to Excel for analysis. Before we begin on that route, let’s take a quick moment to talk about what JSON is for those that aren’t familiar with it.

Curious how to do this in VSTO? Click Here!

What is JSON?

JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. In layman’s terms, it is a string of text that represents a universal data structure. It is easy for humans to read (when formatted properly) and, because of the universal structure, it is very easy and fast for machines to parse and generate. It’s made simple because JSON, at it’s most basic, is only two data types. It is made up of objects, or arrays, or a combination of both. Objects are made up of key/value pairs (often called a dictionary) and arrays are simply collections of values or objects separated by a comma.

It’s important to note that object/dictionary and array are the terminology applicable to the .NET language. Other languages might have other terminology such as hash table for object, or vector for an array. This site gives a great high level, cross-language overview of JSON.

JSON Object

A JSON object always begins with { and ends with }. The key is separated from its value with a colon (“:”). The key/value pairs will then be separated by commas all within the curly brackets. In the image below, the first key “color” corresponds to its value “red”, while the second key “value” corresponds to the red hex code “#f00”. Hopefully you can see why this might be called a dictionary as you look up the key (word to define) to get its value (definition of the word).

JSON Array

A JSON array, in its simplest form, is a list of values separated by a comma that begins with [ and ends with ]. In the below image, the JSON array is a list of string data points. We can see that they are colors but there is no key in this example explicitly telling that this string represents a color. Therefore, JSON is most commonly seen as a combination of arrays and objects.

JSON Objects & Arrays Together

Most of the time, JSON is returned as an array of objects. If you recall, an object always begins and ends with curly brackets. Therefore, the array of objects would have several objects enclosed in curly brackets that are separated by commas and all objects are enclosed in normal brackets (the array). That looks like this image:

In this example, each object represents a color and its corresponding hex code. In Excel, this would likely be two columns of data. One column for color, and the other for its hex code value. Now that you’re more familiar with JSON data structure, let’s learn how to import JSON to Excel!

Setup JSON to Excel

There are several libraries out there to help you import JSON to Excel. The most popular of these is VBA-JSON which is available here on GitHub. This library will do most of the hard work of parsing the JSON string to a dictionary for you and then you can write the values to an excel worksheet. When you download the latest release, you’ll need to import the “JsonConverter.bas” file to your VBA project. This GIF shows how that is accomplished. You will also need to add a  reference to the “Microsoft Scripting Runtime” by clicking tools from the ribbon, selecting references and checking the box for Microsoft Scripting Runtime as shown in the screen grab.

Once you have completed both steps, insert a new module into your project and title it whatever you like. To get started, we need to dimension some variables to use later.

Dim ws As Worksheet
Dim jsonText As String
Dim jsonObject As Object

The ws variable will represent a worksheet in excel. In this example, we will use this worksheet to both read in our JSON string from and write the parsed JSON to Excel. To assign a worksheet to this variable, set it like this:

Set ws = Worksheets("JSON to Excel Example")

The jsonText variable will represent our string of valid JSON data. For this example, I’ve pasted that string of colors and hex codes we looked at earlier into cell A1 on the JSON to Excel Example tab. To assign that string to a variable, type this code:

jsonText = ws.Cells(1, 1)

This step is not necessary for the code to work for us. We could simply reference the cell in the worksheet that contains the JSON. However, most of the time you’ll be returning your JSON string from another data source, most likely a HTTP GET Web Call. If you’re not familiar with HTTP Requests in VBA, click here to learn more.

Lastly, to put the JSON string into the jsonObject, we will use one of the methods contained in the JsonConverter file you imported to begin this example. Since the builder of this library made all of the subroutines public, it is callable from the module this code is in. That call looks like this:

Set jsonObject = JsonConverter.ParseJson(jsonText)

This call instructs the JsonConverter module to parse the JSON string you’ve passed it using the jsonText variable to a collection of dictionaries that we can now access. Now that we’ve finished our setup, we can start learning to import JSON to Excel in the next section.

Import JSON to Excel

The code to import JSON to Excel might sound daunting, but it is much easier than it sounds. At a high level, all you have to do is loop through the jsonObject you’ve created, and write each dictionary value to the sheet. The wonderful thing about dictionaries, is this is easy to do with a for each loop. I’ve added a couple of variables and a for each loop to our code and the result is this:

Sub JsonToExcelExample()
Dim jsonText As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("JSON to Excel Example")

jsonText = ws.Cells(1, 1)

Set jsonObject = JsonConverter.ParseJson(jsonText)

i = 3

ws.Cells(2, 1) = "Color"
ws.Cells(2, 2) = "Hex Code"

For Each item In jsonObject
    ws.Cells(i, 1) = item("color")
    ws.Cells(i, 2) = item("value")
    i = i + 1
Next

End Sub

I set a counter variable, i, that I can use to tell the loop which row to write the data to. Next, create my column headers for the data. Then, for each “item” in the jsonObject write that dictionaries values to the cell row I indicate and increment my counter variable. That’s it! The results of our import JSON to Excel code looks like this:

As you can see, we’ve turned that odd-looking string of text in cell A1 to an easy to understand table of data with just a few lines of code!

Understanding JSON to Excel (Advanced)

Now, I do want to point out that understanding the structure of the JSON you’ll be parsing is very important. Within the for each loop, the code can access the value of each object by referencing its key. This goes back to understanding the key/value pair relationship of JSON and the dictionary structure to which it is parsed. The reason I stress this so much is because, when you’re pulling back more complex looking JSON, you may need to have multiple levels of dictionary references to access the value you want. There may also be times when you have to nest for loops when you have an array of objects with arrays of objects nested inside each object.

For example, let’s look at an example where the JSON is structured as a single object with key “data” and the value is an array of data about employee’s and their organizations:

As you can see, we have an object with an array nested inside. Some objects in the array contains other nested objects as well. This multi-tiered data structure is very common when pulling data from a REST service and was very confusing to me when I first began trying to import JSON to Excel. Let’s examine what the code looks like for this scenario before we dive any deeper.

Sub JsonToExcelAdvancedExample()
Dim jsonText As String
Dim jsonObject As Object, item As Object
Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("JSON to Excel Advanced Example")

jsonText = ws.Cells(1, 1)

Set jsonObject = JsonConverter.ParseJson(jsonText)

i = 3

ws.Cells(2, 1) = "id"
ws.Cells(2, 2) = "Name"
ws.Cells(2, 3) = "Username"
ws.Cells(2, 4) = "Email"
ws.Cells(2, 5) = "Street Address"
ws.Cells(2, 6) = "Suite"
ws.Cells(2, 7) = "City"
ws.Cells(2, 8) = "Zipcode"
ws.Cells(2, 9) = "Phone"
ws.Cells(2, 10) = "Website"
ws.Cells(2, 11) = "Company"

For Each item In jsonObject("data")
    ws.Cells(i, 1) = item("id")
    ws.Cells(i, 2) = item("name")
    ws.Cells(i, 3) = item("username")
    ws.Cells(i, 4) = item("email")
    ws.Cells(i, 5) = item("address")("street")
    ws.Cells(i, 6) = item("address")("suite")
    ws.Cells(i, 7) = item("address")("city")
    ws.Cells(i, 8) = item("address")("zipcode")
    ws.Cells(i, 9) = item("phone")
    ws.Cells(i, 10) = item("website")
    ws.Cells(i, 11) = item("company")("name")
    i = i + 1
Next

End Sub

The code overall is the exact same, just referencing a different tab and added more fields to pull out. The change I want to point out is to the for loop itself. The beginning of the for loop now starts by referencing the overall object that contains the array. This is referred to as the “root” of the JSON and you’ll see it if you’re querying a REST API for JSON data. This moves the for each loop inside the “data” level of the JSON and will now loop through all objects in the highest-level array. Had I left this out, the loop would only occur once as the JSON is all contained within a single object at its top level.

The second piece I want to point out is the lines for address and company. These are both objects contained within the parent object. So, in order for me to reach to value I want, I have to reference both keys to get the specific value I need. Lastly, I want to call out that you do not need to write every value from the returned JSON to the sheet. I have skipped over several fields to get the end result I desired and it does not affect my code results, which coincidently look like this:

I hope this brief explanation of how to import JSON to Excel was useful for you! Please let me know if it was helpful or if you have any questions in the comments below. Also, if you want to download the file with the example JSON and VBA code, you can do so here if you’re a free subscriber to the site. It’s free, easy, and only takes a moment to sign up.

Cheers!

R