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.

Excel Import Json How To Import JSON

Therefore, as excel ninjas, its important to understand how to import JSON to Excel for analysis.

Before we begin on that route, lets take a quick moment to talk about what JSON is for those that arent familiar with it.

In laymans 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.

Its made simple because JSON, at its 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 keyvalue pairs (often called a dictionary) and arrays are simply collections of values or objects separated by a comma.

This site gives a great high level, cross-language overview of JSON.

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).

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.

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).

Now that youre more familiar with JSON data structure, lets learn how to 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.

Excel Import Json Download The Latest

When you download the latest release, youll need to import the JsonConverter.bas file to your VBA project.

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.

To get started, we need to dimension some variables to use later.

Excel For Mac Import Json