How to Parse Custom JSON Data using Excel

in excel •  3 months ago


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.
READ THE REST OF THIS ARTICLE ON MY BLOG



Posted from my blog with SteemPress : http://theexcelclub.com/how-to-parse-custom-json-data-using-excel/

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

This is quite advanced stuff paula, I got your email thanks. Its a little advanced for me, but I have shared this post with my boss as I think he would be rather intersted

·

thanks for the share @dernan, and yep it is advanced, but you should try it, its not that difficult. And next week I will have the video solution to the activity

Not that anyone should quote me on this one, but it seems that jsons are pretty much standard when it comes to API development.

·

Standard way data ca be moved around the web via API calls but all different in the data they contain

Posted using Partiko Android

This method certainly beats using SQL for simplicity!

Nice promotion of @actifit from your Wordpress blog too :)

·

glad you find it usefull @abh12345. I put @actifit in so people can see there are other ways to also earn steem, get people familiar with some of the uses before I do the hard launch.

This is simply awsome: I'll try it, thank you!

Resteemed and (sorry, very little) upvoted!

·

There is so much people can do with Excel, now with the power tool, working with data of different types is so much easier that it was before.

Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You made more than 600 upvotes. Your next target is to reach 700 upvotes.

Click here to view your Board
If you no longer want to receive notifications, reply to this comment with the word STOP

Support SteemitBoard's project! Vote for its witness and get one more award!