<center>http://theexcelclub.com/wp-content/uploads/2019/01/11.png</center> <br/>To Parse Custom JSON data is to split out its name/value pairs into a more readable useable format.
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
<ul>
<li>What is JSON Data</li>
<li>To Connect to JSON data from Excel</li>
<li>How to Parse simple JSON Data using Excels Power Query</li>
<li>To Parse complex JSON Data using Excels Power Query</li>
</ul>
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 <img draggable="false" src="https://s.w.org/images/core/emoji/12.0.0-1/svg/1f60a.svg" alt="😊"><br/>. If you are not familiar with <a href="https://theexcelclub.com/excel-power-tools-the-future-of-excel/">Excel Power Tools you can find out about them here</a>.
<h2>What is JSON Data?</h2>
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 which easily moves information around the internet.
The syntax for JSON comes from JavaScript and can be summarized as follows:
<ul>
<li>Data is shown in name/value pairs separated by : For example “name” : ”paulag”</li>
<li>Data is separated by commas. For Example “name” : ”paulag” , “Sex” : “Female”</li>
<li>Curly brackets represent an object. An object being a name/value pair separated by a comma. For Example {“name” : ”paulag” , “Sex” : “Female”}</li>
<li>Square brackets hold arrays and contains a list of values separated by a comma.</li>
</ul>
Look at the sample JSON in the image below
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/5-2.png" alt="How to Parse Custom JSON Data using Excel" width="1354" height="443"><br/>
{“total_population”: This 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) also separated with a comma. The data, shown in the name/value pairs, in this example is date and population.
Here is one you can look at yourself. <a href="https://api.coinmarketcap.com/v1/ticker/bitcoin/">https://api.coinmarketcap.com/v1/ticker/bitcoin/</a>
If you enter this to your browser, you will get something like the below:
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-1.png" alt="excel JSON" width="439" height="373"><br/>
Let’s take a look at that image in more detail.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-2.png" alt="" width="792" height="462"><br/>
<h2>Connecting to JSON data from Excel</h2>
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 will then have the option to get data from a JSON file.
In this example we have URL API endpoint <a href="https://api.coinmarketcap.com/v1/ticker/bitcoin/">https://api.coinmarketcap.com/v1/ticker/bitcoin/</a>. Therefore, from the Data Ribbon we can select, Get data from Web. This will open a dialogue box in which you place the URL.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-3.png" alt="" width="735" height="378"><br/>
Next, 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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/9.png" alt="" width="744" height="298"><br/>
The JSON data will appear as a list of records Power Query. For excel to read this, we must convert a list to a table. Select ‘to table’ from the available option.
Next, Power query will 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 record. We must expand this record to get the value pairs. As this record only has 1 row, we would expect this to expand across the columns. To do this, right click on the arrows in the column header.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-5.png" alt="" width="733" height="517"><br/>
This will reveal the names of the value pairs. By selecting ok a new column will be set up in the table. The name will be in the header and the value in the row.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-6.png" alt="" width="1534" height="131"><br/>
<h3>Further transformations</h3>
If we wanted this data going down the row, we could Unpivot the columns. By selecting the id column. Then from the Transform ribbon select the dropdown for unpivot columns and select unpivot other columns.
<img src="http://theexcelclub.com/wp-content/uploads/2019/11/Excel-JSON-7.png" alt="" width="723" height="441"><br/>
When working with Power Query, it’s important to make sure you have the correct data types set. 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. Or, if we select or Close and Load to, the data will be loaded into a Power Pivot Model.
<h2>How to Parse JSON Data in Excel</h2>
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
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/6-2-1024x54.png" alt="How to Parse Custom JSON Data using Excel" width="1024" height="54"><br/>
When we encounter data like this, we can easily parse the column into its components. From the image below we see we have 4 components. We have An Array, an Object, the data, and one of the data fields contains an array.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/7-2.png" alt="How to Parse Custom JSON Data using Excel" width="913" height="515"><br/>
<h3>STEP by STEP</h3>
<a href="https://www.dropbox.com/s/itpne6vizrtb8yk/JSON%20example.csv?dl=0">Download this file</a>. It contains a table as shown below. (do not copy and paste the table as the JSON field will not be recognised.)
<table>
<tbody>
<tr>
<td width="64"><strong>tx_id</strong></td>
<td width="48"><strong>tid</strong></td>
<td width="418"><strong>json_metadata</strong></td>
<td width="71"><strong>timestamp</strong></td>
</tr>
<tr>
<td width="64">647524676</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"mervin-gil","following":"jarvie","what":["blog"]}]</td>
<td width="71">43466.89097</td>
</tr>
<tr>
<td width="64">647524682</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"steliosfan","following":"michealb","what":["blog"]}]</td>
<td width="71">43466.89097</td>
</tr>
<tr>
<td width="64">647524833</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"eugenezh","following":"zentricbau","what":["blog"]}]</td>
<td width="71">43466.89097</td>
</tr>
<tr>
<td width="64">647524855</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"bitcoinportugal","following":"manuellevi","what":[]}]</td>
<td width="71">43466.89097</td>
</tr>
<tr>
<td width="64">647525074</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"eugenezh","following":"adriellute","what":["blog"]}]</td>
<td width="71">43466.89167</td>
</tr>
<tr>
<td width="64">647525089</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"bigbigtoe","following":"hoxly","what":["blog"]}]</td>
<td width="71">43466.89167</td>
</tr>
<tr>
<td width="64">647525121</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"mervin-gil","following":"loveon","what":["blog"]}]</td>
<td width="71">43466.89167</td>
</tr>
<tr>
<td width="64">647525159</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"mervin-gil","following":"pechichemena","what":["blog"]}]</td>
<td width="71">43466.89167</td>
</tr>
<tr>
<td width="64">647525233</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"imealien","following":"pataty69","what":["blog"]}]</td>
<td width="71">43466.89167</td>
</tr>
<tr>
<td width="64">647525652</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"mervin-gil","following":"kamile","what":["blog"]}]</td>
<td width="71">43466.89236</td>
</tr>
<tr>
<td width="64">647525818</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"bitcoinportugal","following":"drmake","what":["blog"]}]</td>
<td width="71">43466.89236</td>
</tr>
<tr>
<td width="64">647525886</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"mervin-gil","following":"bradfordtennyson","what":["blog"]}]</td>
<td width="71">43466.89236</td>
</tr>
<tr>
<td width="64">647525980</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"a0i","following":"shoemanchu","what":["blog"]}]</td>
<td width="71">43466.89236</td>
</tr>
<tr>
<td width="64">647526007</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"voteme","following":"kostyantin","what":["blog"]}]</td>
<td width="71">43466.89236</td>
</tr>
<tr>
<td width="64">648215552</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"ansie","following":"hoxly","what":["blog"]}]</td>
<td width="71">43467.75833</td>
</tr>
<tr>
<td width="64">648215582</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"ashokcan143","following":"abcor","what":[]}]</td>
<td width="71">43467.75833</td>
</tr>
<tr>
<td width="64">648215691</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"ashokcan143","following":"abasinkanga","what":[]}]</td>
<td width="71">43467.75903</td>
</tr>
<tr>
<td width="64">648215820</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"nongvo.piy","following":"acidyo","what":[]}]</td>
<td width="71">43467.75903</td>
</tr>
<tr>
<td width="64">648215859</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"grid9games","following":"yeninsfer","what":["blog"]}]</td>
<td width="71">43467.75903</td>
</tr>
<tr>
<td width="64">648215945</td>
<td width="48">follow</td>
<td width="418">["follow",{"follower":"nongvo.piy","following":"acidyo","what":["blog"]}]</td>
<td width="71">43467.75903</td>
</tr>
</tbody>
</table>
Click on any of the cells that contain the data and from the Data Ribbon select FROM Table/Range
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/8-2.png" alt="How to Parse Custom JSON Data using Excel" width="627" height="144"><br/>
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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/9-1.png" alt="How to Parse Custom JSON Data using Excel" width="244" height="158"><br/>
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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/10-1-1024x505.png" alt="How to Parse Custom JSON Data using Excel" width="1024" height="505"><br/>
To parse the json column, first select the column, then on the Transform Ribbon select Parse and select JSON
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/11.png" alt="How to Parse Custom JSON Data using Excel" width="797" height="348"><br/>
Power query will recognise the first [ and create a list format. Next, 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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/12.png" alt="How to Parse Custom JSON Data using Excel" width="766" height="132"><br/>
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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/13.png" alt="How to Parse Custom JSON Data using Excel" width="758" height="216"><br/>
As we do not need the name of the array, we can use the filter to remove all the follow rows
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/14.png" alt="How to Parse Custom JSON Data using Excel" width="799" height="404"><br/>
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
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/15.png" alt="How to Parse Custom JSON Data using Excel" width="789" height="301"><br/>
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
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/16.png" alt="How to Parse Custom JSON Data using Excel" width="755" height="223"><br/>
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.
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/17.png" alt="How to Parse Custom JSON Data using Excel" width="629" height="305"><br/>
<h2>More complex JSON data extractions in Excel</h2>
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. Parsing custom JSON data in Excel can require some thinking.
<h2>Learn and Earn Activity</h2>
<strong>Look at this small table of data. You can <a style="color: #000000;" href="https://www.dropbox.com/s/2rh9ii0ucnnatyh/json_complex.csv?dl=0">download the file with this link</a> to carry out this Learn and Earn Activity. This is more complex custom JSON data that can be parsed in Excel with Power query.</strong>
<img src="http://theexcelclub.com/wp-content/uploads/2019/01/json.png" alt="" width="961" height="745"><br/>
<strong>The JSON column has varied lengths and objects and strings. </strong> <strong>How would you go about parsing this custom JSON data so that it is all available in one table?</strong>
<h5><center><em><strong>Give it a try. If you have a STEEM account you can earn while you learn. See below for more details.</strong></em></center></h5>
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.
The video below shows how you can parse simple JSON data using Excel Power Query and how you can parse custom JSON data as detailed in the activity.
<h3>Become a Power Pivot Hero</h3>
<a href="https://courses.theexcelclub.com/courses/power-query-excel-365-get-and-transform-data" data-elementor-open-lightbox="">
<img width="441" height="513" src="http://theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png" alt="Power Pivot online training course" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320 275w" sizes="(max-width: 441px) 100vw, 441px"><br/></a>
<h3> WE REWARD YOU for Learning EXCEL</h3>
<p><center>Practical Beginner Excel Now Includes Learn and Earn Activities.</center></p>
<a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365" data-elementor-open-lightbox="" target="_blank" rel="noopener noreferrer">
<img width="432" height="501" src="http://theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png" alt="FREE beginner excel training" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png?w=432 432w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png?resize=259%2C300 259w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png?resize=410%2C475 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png?resize=100%2C116 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/beginner-excel.png?resize=275%2C319 275w" sizes="(max-width: 432px) 100vw, 432px"><br/></a>
<h3><center><em><strong>Time to Brush Up on Power Pivot </strong></em></center></h3>
<a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox="">
<img width="441" height="513" src="http://theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png" alt="Power Pivot online training course" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320 275w" sizes="(max-width: 441px) 100vw, 441px"><br/></a>
<figure><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?w=1170" sizes="(max-width: 225px) 100vw, 225px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?w=225&ssl=1 225w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=150%2C150&ssl=1 150w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/05/steem.png?resize=100%2C100&ssl=1 100w" alt="learn and earn steem activity"><br/></figure>
<center><strong>Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.</strong></center></p>
<center><strong>We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.</strong></center></p>
<center><a href="https://theexcelclub.com/learn-and-earn-steem-activities/">Click to</a> <a href="https://theexcelclub.com/learn-and-earn-steem-activities/">find out more now and start earning while you are learning Excel and Power BI</a></center></h3>
<a href="https://actifit.io/signup?referrer=paulag" data-elementor-open-lightbox="">
<img width="1024" height="497" src="http://theexcelclub.com/wp-content/uploads/2019/01/4-3-1024x497.png" alt="actifit" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=1024%2C497 1024w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=300%2C146 300w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=768%2C373 768w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=700%2C340 700w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=410%2C199 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=100%2C49 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?resize=275%2C133 275w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2019/01/4-3.png?w=1377 1377w" sizes="(max-width: 1024px) 100vw, 1024px"><br/></a>
<h4><center><strong>BEST VALUE</strong></center></h4>
<center><strong>ACCESS ALL AREAS - UNLIMITED LEARNING</strong></center></h4>
<a href="https://courses.theexcelclub.com/courses/access-all-areas" data-elementor-open-lightbox="">
<img width="438" height="502" src="http://theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png" alt="" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?w=438 438w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=262%2C300 262w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=410%2C470 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=100%2C115 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/09/access-all-areas.png?resize=275%2C315 275w" sizes="(max-width: 438px) 100vw, 438px"><br/></a>
<center><strong>Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox</strong> <strong>with Earn and Learn activities.</strong></center></h3>
<a href="https://theexcelclub.newzenler.com/f/email-signup" role="button">
SIGN UP NOW
</a>
https://youtu.be/q6sKs2KLnOo
<em>New to Excel? Check out our </em><a href="https://theexcelclub.com/excel-101-getting-started-with-excel-365/"><em>Ultimate beginner Excel Guide here</em></a><em>.</em></h4>
<h4><em>New to Excel Power Tools? </em><a href="https://theexcelclub.com/excel-power-tools-the-future-of-excel/"><em>Check out these amazing Power Pivot, Power Query and Power view demonstrations</em></a></h4>
<h4><em>New to DAX for Power Pivot and Power BI? </em><a href="https://theexcelclub.com/dax-sum-and-sumx-functions/"><em>Let us help you get started</em></a></h4>
<center><strong>Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox</strong> <strong>with Earn and Learn activities.</strong></center></h3>
<a href="https://theexcelclub.newzenler.com/f/email-signup"><strong>SIGN UP NOW</strong></a>
IF YOU CARE- YOU WILL SHARE - YOU WILL FIND THE SHARE BUTTONS BELOW THE COMMENTS SECTION <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : http://theexcelclub.com/how-to-parse-custom-json-data-using-excel/ </em><hr/></center> 
hiveblocks