How to Parse Custom JSON Data using Excel

How to Parse Custom JSON Data using Excel

Excel is a powerful tool that allows you to connect to JSON data and read it.  However sometimes this data might require a little manipulation to be fully understood and analysed in Excel. 

In this article you will learn

  • What is JSON Data
  • How to Connect to JSON data from Excel
  • How to Parse simple JSON Data using Excels Power Query
  • How to Parse complex JSON Data using Excels Power Query

This article contains data tables to download so you can practice along and master the art of parsing custom JSON data using Excel.  We are also powered with STEEM so you can earn while you learn 😊

What is JSON Data?

JSON data is a way of representing objects or arrays.  It is easy to read, and it is easy to parse, even with Excel.  Many API calls will return JSON format and many web apps use JSON to easily move information around the internet.

The syntax for JSON comes from JavaScript and can be summarized as follows:

  • Data is shown in name/value pairs separated by : For example “name” : ”paulag”
  • Data is separated by commas. For Example “name” : ”paulag” , “Sex” : “Female”
  • Curly brackets represent an object. An object being a name/value pair separated by a comma. For Example {“name” : ”paulag” , “Sex” : “Female”}
  • Square brackets hold arrays and contains a list of values separated by a comma.

For a simple example we are going to look at http://api.population.io/1.0/population/World/today-and-tomorrow/?format=json

If entered to your browser you will get something similar to below.

How to Parse Custom JSON Data using Excel{“total_population”: [{“date”: “2019-01-02”, “population”: 7638335801}, {“date”: “2019-01-03”, “population”: 7638557789}]}

{“total_population”: shows the first object, which is a name/value pair.  The name of the object is total_population

[{“date”: “2019-01-02”, “population”: 7638335801}, {“date”: “2019-01-03”, “population”: 7638557789}]}  This is the value for the total population. The [ represents an array.  This array contains two objects. The objects are defined within the curly brackets and separated with a comma. Each object contains 2 lots of data (name/value pair) separated with a comma.  The data, shown in the name/value pairs, is date and population.

Connecting to JSON data from Excel

In Excels Data ribbon, under GET and Transform Data, we have the option of connecting to data of multiple sources and multiple types. 

If we select Get data from file, we have the option to get data from a JSON file. 

If we have a URL API, like our example, we can select from the Data Ribbon,  Get data from Web.  This will open a dialogue box in which you place the URL.  Power query will then open.  Power query is a magic excel tool that will allow you transform data that you connect to into a usable format.

gif_1_Analysing Custom JSON Data in Excel

JSON data will appear as a list or a record in Power Query.  For excel to read this, we must convert a list to a table.  Select into table from the option.  Power query will now create a table and you will see this step appear on the right of the power query window under applied steps.

This new table contains a Name and a Values column.  The values column is comprised of a list.  We must expand the values in this list to access them.  Right click on the arrows in the column header and select Expand to New Rows.

This will reveal there are two records. Right click on the arrows in the column header and this will reveal the columns in the records.  Tick the boxes of the columns you want.

We now have 3 columns of data and two rows.  Each row being a separate record in the dataset, or the object total_population.

gif_2_Analysing Custom JSON Data in Excel

When working with Power Query, its important to make sure you have the correct data types set.  In this example we can see the data type for Value.date column is set to any.  From the transform ribbon, select Date from the data type dropdown.  We can also change the data type for Value.population to whole number.

To work with this data we must now move to from Power Query to Excel.  If we select File, and then select Close and load, this will load the data as a table in Excel.  If we select or Close and Load to, the data will be loaded into a Power Pivot Model.  

Parse JSON Data in Excel

Very often when you access JSON data with Excel it appears in 1 column.  This can happen for many reasons and is often the design of a database.

Look at the image below.  We can see the json_metadata field is still in its JSON syntax

How to Parse Custom JSON Data using Excel

When we encounter data like this, we can easily phrase the column into its components.  From the image below we see we have 4 components. An Array, an Object, the data, and one of the data fields contains an array.

How to Parse Custom JSON Data using Excel

STEP by STEP

Copy and paste the following table into a worksheet in Excel

tx_id

tid

json_metadata

timestamp

647524676

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”jarvie”,”what”:[“blog”]}]

43466.89097

647524682

follow

[“follow”,{“follower”:”steliosfan”,”following”:”michealb”,”what”:[“blog”]}]

43466.89097

647524833

follow

[“follow”,{“follower”:”eugenezh”,”following”:”zentricbau”,”what”:[“blog”]}]

43466.89097

647524855

follow

[“follow”,{“follower”:”bitcoinportugal”,”following”:”manuellevi”,”what”:[]}]

43466.89097

647525074

follow

[“follow”,{“follower”:”eugenezh”,”following”:”adriellute”,”what”:[“blog”]}]

43466.89167

647525089

follow

[“follow”,{“follower”:”bigbigtoe”,”following”:”hoxly”,”what”:[“blog”]}]

43466.89167

647525121

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”loveon”,”what”:[“blog”]}]

43466.89167

647525159

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”pechichemena”,”what”:[“blog”]}]

43466.89167

647525233

follow

[“follow”,{“follower”:”imealien”,”following”:”pataty69″,”what”:[“blog”]}]

43466.89167

647525652

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”kamile”,”what”:[“blog”]}]

43466.89236

647525818

follow

[“follow”,{“follower”:”bitcoinportugal”,”following”:”drmake”,”what”:[“blog”]}]

43466.89236

647525886

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”bradfordtennyson”,”what”:[“blog”]}]

43466.89236

647525980

follow

[“follow”,{“follower”:”a0i”,”following”:”shoemanchu”,”what”:[“blog”]}]

43466.89236

647526007

follow

[“follow”,{“follower”:”voteme”,”following”:”kostyantin”,”what”:[“blog”]}]

43466.89236

648215552

follow

[“follow”,{“follower”:”ansie”,”following”:”hoxly”,”what”:[“blog”]}]

43467.75833

648215582

follow

[“follow”,{“follower”:”ashokcan143″,”following”:”abcor”,”what”:[]}]

43467.75833

648215691

follow

[“follow”,{“follower”:”ashokcan143″,”following”:”abasinkanga”,”what”:[]}]

43467.75903

648215820

follow

[“follow”,{“follower”:”nongvo.piy”,”following”:”acidyo”,”what”:[]}]

43467.75903

648215859

follow

[“follow”,{“follower”:”grid9games”,”following”:”yeninsfer”,”what”:[“blog”]}]

43467.75903

648215945

follow

[“follow”,{“follower”:”nongvo.piy”,”following”:”acidyo”,”what”:[“blog”]}]

43467.75903

Click on any of the cells that contain the data and from the Data Ribbon select FROM Table/Range

How to Parse Custom JSON Data using Excel

If your data is not in table format, Excel will then prompt you to create a table.  Select the cells the contain the data and tick the box to say that your table has headers.

How to Parse Custom JSON Data using Excel

Power Query editor window will open. On the canvas you can see your data and, on the right, you can see any transformation steps that have taken place.

How to Parse Custom JSON Data using Excel

To parse the json column, first select the column, then on the Transform Ribbon select Parse and select JSON

How to Parse Custom JSON Data using Excel

Power query will recognise the first [ and create a list format.  We need to expand this list to new rows.  To do this click on the arrows on the top of the column and select Expand to New Rows.

How to Parse Custom JSON Data using Excel

What is returned is two lines for each tx-id.  The json column now has a row for the name of the array, which is follow, and a record.  The record will contain the data.

How to Parse Custom JSON Data using Excel

As we do not need the name of the array, we can use the filter to remove all the follow rows

How to Parse Custom JSON Data using Excel

We are now left with the records. We can expand this record, by pressing the arrow on the column.  From here we see we have the names of 3 data fields, Follower, Following and What

How to Parse Custom JSON Data using Excel

When we select OK, we get a new column in our data table for each data field.

However, the field named what contains an array, which is again shown as a list (or array) which needs to be expanded

How to Parse Custom JSON Data using Excel

Once we expand this, we are at the end of the JSON data and have extracted the relevant columns.  You can now use this data for further analysis in Excel or Power Pivot.  To load it back to excel, select File and Close & Load.

How to Parse Custom JSON Data using Excel

More complex JSON data extractions in Excel

So far, we have looked at getting JSON data into Power Query using an Excel table and directly from a URL.  There are other ways you can connect to JSON data including connecting directly to a JSON file.  Connecting is the easy part.  Things get more complicated when you have JSON columns where the strings are different in each row.  One might start with an array and so return a list, but some might start with an object and return a record.

In Power Query lists are expanded to new rows in the table and records are expanded to new columns.

Look at this small table of data. You can copy and paste it into Excel to carry out this activity.

tx_id

tid

json_metadata

timestamp

647524705

vote

{“account”:”chireerocks”,”author”:”chesatochi”,”permlink”:”up73bndy”,”app”:”partiko”}

43466.89097

648085961

vote

{“account”:”mad-runner”,”author”:”drago18121996″,”permlink”:”re-mad-runner-actifit-mad-runner-20190102t141207878z”,”app”:”partiko”}

43467.62222

647524243

sm_find_match

{“match_type”:”Ranked”,”app”:”steemmonsters/0.4.4.2″}

43466.89028

647524248

sm_submit_team

{“trx_id”:”4c7a40566525deca69c89bd02074a88712b4bf8a”,”team_hash”:”7957d96e5aa457865a2ed92fae7604b9″,”summoner”:”C1-16-3HSZ4ALDN4″,”monsters”:[“C1-22-WD5TFWW4G0″,”C1-20-3GB1NQ9H0W”,”C1-17-U1ITIF1M4W”,”C1-15-QTM0Z7GDJ4″,”C1-14-KJFI0UKCSW”],”secret”:”kAc7j9Jvms”,”app”:”steemmonsters/0.4.4.2″}

43466.89028

647524255

sm_submit_team

{“trx_id”:”5067d8aa92591b80c1d2a5af821d77dc7f183731″,”team_hash”:”61604b5d6aedbf24e9298eb92b4a8431″}

43466.89028

647524259

sm_submit_team

{“trx_id”:”d6ef774b9625d64e7a5960b50e281661c92615c3″,”team_hash”:”6ab2dc7d52b9f0d20bc6336457626523″,”summoner”:”C1-49-SB2U0N4Z9S”,”monsters”:[“C1-50-R0ZC1YYMJK”,”C3-86-Q77IFPN1K0″,”C1-51-P2EM43BKVK”,”C1-45-PPLZZWAAIO”,”C1-52-N7NMZZ8YPC”,”C1-64-DQS3VZG774″],”secret”:”89eTbIrIbZ”,”app”:”steemmonsters/0.4.4.2″}

43466.89028

647524260

sm_find_match

{“match_type”:”Ranked”,”app”:”steemmonsters/0.4.4.2″}

43466.89028

647524263

sm_submit_team

{“trx_id”:”875e0c9e14529006e17d862eed9b1c75794d5bf8″,”team_hash”:”178bcca13992d8aea95c63d67935fd1e”,”summoner”:”C3-88-EHDPTK1ZZK”,”monsters”:[“C1-40-C200PYYG0G”,”C1-37-7CACIJO0W0″,”C1-35-ELNFBZ4LTC”,”C1-64-6W2076JT0G”,”C1-36-458942IM1S”],”secret”:”oxQG0VM7q3″,”app”:”steemmonsters/0.4.4.2″}

43466.89028

647524676

follow

[“follow”,{“follower”:”mervin-gil”,”following”:”jarvie”,”what”:[“blog”]}]

43466.89097

647524682

follow

[“follow”,{“follower”:”steliosfan”,”following”:”michealb”,”what”:[“blog”]}]

43466.89097

647524833

follow

[“follow”,{“follower”:”eugenezh”,”following”:”zentricbau”,”what”:[“blog”]}]

43466.89097

The JSON column has varied lengths and objects and strings.  How would you go about parsing this data so that it is all available in one table?

Give it a try.  If you have a STEEM account you can earn while you learn. See below for more details.

It does require logical thinking and little more Power Query knowledge than what we have covered here but I know you can get it.  Also, there is more than one way to come up with the solution.

Post your solutions in the comments section below.  If you get stuck, post a comment letting me know where you are stuck and what problems you encounter.

Next week I will post a video with the solution.

The Excel Club blog has now been powered with STEEM.  We are a tokenized website where you can earn while you learn.  To earn STEEM tokens you must partake in the practice activity and post your answers or questions or feedback in the comments section below.
There are currently 2 options for getting a STEEM account

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

IF YOU CARE- YOU WILL SHARE – YOU WILL FIND THE SHARE BUTTONS BELOW THE COMMENTS SECTION