How to Open json File in Excel

What is JSON?

JSON (JavaScript Object Notation) is a file format that is used for storing and exchanging data in the network. It is used to send data from server to client and client to server.  JSON is easy for machines to analyze and generate. The extension of JSON  is .json. 

JSON records are, in reality, straightforward to peruse on the off chance that you know the construction of a Javascript document. You can utilize a proofreader with explicit help for JSON to get the disentangling. For instance, if there is an extremely pleasant JavaScript addon for Notepad++ called JSTool, which incorporates a JSON watcher, it shows the decoded esteem strings so you can duplicate the string to another content record. 

Check Out upGrad’s Java Bootcamp

A commonplace JSON record is a word reference where every section has a string key, and each worth is a string, a number, or exacting like valid, bogus or invalid. The qualities are strings that are encoded by the shows of Javascript, which is practically equivalent to Java or C, implying that every typical character is composed with no guarantees and all extraordinary characters are encoded with an oblique punctuation line.

This is either followed by the scorch that is implied or by a hex number that addresses the ASCII or Unicode estimation of the singe. For example, on the off chance, you have French or Spanish scorches or dialects that are totally outside Latin letters in order like Russian or Chinese. 

JSON is JavaScript object documentation or a progression of JavaScript objects with recorded credits. These credits are what is recorded within each set of wavy sections and give data about the object. JSON records may contain numerous objects in each document. They might be the consequence of an information dump from a program that utilizes JSON documentation or methods by which a client can enter data into a data set. MongoDB, for instance, locally utilizes JSON to store information. 

A JSON document is a record that stores straightforward information constructions and objects in JavaScript Object Notation (JSON) design, which is a standard information trade design. It is principally utilized for sending information between a web application and a server. JSON documents are lightweight, text-based, comprehensible, and can be altered by using a content manager. 

Also, check out our free courses to get an edge over the competition.

Open the record in Notepad, save it as a .TXT document, and then import it into Excel.

The Structure of JSON is 

Filename = [{key: value}]


JSONExample ={ “namelist” : [ {name:”mark”, age:18, location:” Mars”},

{name:Steve Rogers, age:118, location:”Moon”},

{name:”Tony”, age:48, location:”DC”},

{name:”Peter Parker”, age:18, location:”Queens”},

{name:”Thor”, age:18, location:”Ascord”} ],



Applications of JSON

  • JSON files are primarily used while writing programming code in Javascript-based programs like node, react, etc.
  • The JSON file is used for transmitting data in a structured way while transferring via a network.
  • Most of the time JSON is used to transfer data between server and client applications.
  • In the modern era, JSON overtakes XML in the maximum amount.
  • It can adapt to all programming languages.

upGrad’s Exclusive Software and Tech Webinar for you –

Check Out upGrad’s Advanced Certification in Cyber Security

SAAS Business – What is So Different?


What is Excel?

Excel is the name of a software program created by Microsoft and is known as Microsoft Excel. It uses a spreadsheet to store the data and numbers with the functions and formulas. The extension of the excel file is .xlsx. It is widely used in the business sector.

There are important things about excel that you can learn at upGrad. Excel is perhaps the most remarkable measurement software. It permits you to store the information in even arrangement, i.e., in lines and segment design. It additionally allows you to collaborate with your data differently. 

You can sort and channel the information utilizing probably the most powerful equations. The turntables one is the best component of Excel. You can use turntables to make another understanding by controlling the information. Excel has different highlights that can assist you with insights. There are different approaches to bringing in and sending out the information. You can likewise incorporate the data into the work process. 

Like no different insight software, Excel permits you to make the custom capacity utilizing its programming capacities. The basic role of Excel is to make records of information and to control the information according to the users’ requests. As referenced before, Excel permits you to utilize the outside information base to dissect and make reports, among other things. 

Currently, Excel offers the best graphical UI alongside the utilization of design instruments and representation strategies.



Applications of Excel

1. Easy to Utilize Interface

MS Excel has an easy-to-use interface that empowers the client to utilize the software effectively without battling an excessive amount to keep up any records. 

2. Graphical Representation

MS Excel empowers the client to graphically address the information entered. This component of excel helps break down the numbers easily when we manage an enormous amount of data. When handling a vast amount of data (extending to 1000 lines), graphical representation helps us assess the information quickly. 

3. Highly Supportive in Breaking Down Deals

Excel helps organizations break down deals and buys and appropriately assists them with examining their purchasing/selling choices. 

Our learners also read: Excel online course free!

4. Financial Modeling 

Quite possibly, the main benefit of Excel is that it simplifies the financial analysis of the organization. It helps with maintaining the financial records of the organization in an easily accessible format. Excel is often used to calculate or determine EBITDA, WACC, net sales, and net profits, among other things.

5. Accessibility of a Broad Scope of Functions

Excel has enormous scope for doing equations which assists us with computing the entirety, item, difference, and any arrangements of numbers. The user can call upon various functions to simplify the vast data at hand to offer an accurate deduction.

6. Future Development of Any Business 

Excel is a valuable instrument to break down different sorts of information. Organizations use Excel to get an accurate picture of their business development and get a realistic forecast of the coming years’ achievable growth. Excel helps organizations accomplishing a particular development rate by examining the progress of the business.

How to Open JSON file in EXCEL?

The methods of opening JSON files in Excel vary in different versions of Microsoft Excel. You can apply the following methods to open a JSON file depending on the versions of Excel you have:

Office 2007

In the 2007 version of Office, excel doesn’t have the option to open a JSON file in EXCEL.

Office 2010

  • After opening Excel, on the Power Query tab, click From Other Sources > Blank Query.
  • In the Query Editor, click Advanced Editor.
  • Input your query string as follows, replacing “C:\Users\Name\Desktop\example.json” with the path to your JSON file.


    Source = Json.Document(File.Contents(“C:\Users\Name\Desktop\example.json”)),

    #”Converted to Table” = Record.ToTable(Source)


    # “Converted to Table”

Office 2013

In the 2013 version of Office, excel doesn’t have the option to open a JSON file in EXCEL.

  • After opening Excel, on the Power Query tab, click From Other Sources > Blank Query.
  • In the Query Editor, click Advanced Editor.
  • Enter your query string as follows, replacing “C:\Users\example.json” with the path to your JSON file.


    Source = Json.Document(File.Contents(“C:\Users\example.json”)),

    #”Converted to Table” = Record.ToTable(Source)


    # “Converted to Table”

In Newer Version of Excel

  • Select Data > Get Data > From File > From JSON. The Import Data dialog box appears.
  • Search the JSON file, and then select Open.

Opening JSON From the Internet in Excel

The above methods are used for opening files stored in local storage. If you want to import JSON data from the internet or from an API call, you can do the following:

  • Go to Data Tab -> Transform data -> Click From Web. It will open a window or modal.


  • Then, open the power query.



  • Next, create a table for the JSON.


  • By following the steps, you can open the JSON file in EXCEL.

Learn Software Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.


JSON (JavaScript Object Notation) is a file format that is used in storing data and exchanging data in the network. EXCEL uses a spreadsheet to store the data and numbers with functions and formulas. The steps for opening a JSON file in Excel differ in several versions of EXCEL. In the newer version, you can open a JSON file by selecting “Data” > Get Data > From File > From JSON. The Import Data dialogue box appears. Search the JSON file, and then select “Open.” You can check out Free online Excel courses offered by upGrad on Excel

If you’re interested to learn more about full-stack development, check out upGrad & IIIT-B’s Executive PG Program in Full-stack Software Development which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.

What is a json format?

JSON is a format of storing and exchanging data. It stands for JavaScript Object Notation. JSON is a lightweight data-interchange format. It is used primarily to transmit data between a server and web application, as an alternative to XML. It is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. This data interchange format is simple, small, and language independent JSON was derived from the JavaScript scripting language for representing simple data structures and associative arrays, called objects. It has become quite popular for transporting data between the server and web application. It is considered as a lightweight or language independent alternative to XML and is used in numerous AJAX applications.

How to view json files?

JSON file is actually a text file, you can view it line by line with simple text editor like notepad, notepad++ or emacs and so on. If you want to view it in python, it'll be better to install jq, in that case, you can view it in python like: jq '.result'. There are many available JSON libraries for almost every language. If you are interested in Java, you may want to check out GSON. If you want to use for Python, you can also use simplejson.

What are the applications of JSON files?

JSON files are files that store data in JavaScript Object Notation. This is a lightweight format for storing data which is easy for humans to read and write. One of the best features of JSON files is that they are readable by both machines and humans (unlike XML) and more importantly, they work well with JavaScript. JSON files are commonly used for data-driven web applications. In fact, you can even use them to make a web page dynamic. JavaScript has the ability to parse these files and that’s why JSON is one of the best ways to return data to a web page. You can store data in a well-organized and easily-readable format.

Want to share this article?

Land on Your Dream Job

Leave a comment

Your email address will not be published. Required fields are marked *

Our Popular Software Engineering Courses

Get Free Consultation

Leave a comment

Your email address will not be published. Required fields are marked *

Get Free career counselling from upGrad experts!
Book a session with an industry professional today!
No Thanks
Let's do it
Get Free career counselling from upGrad experts!
Book a Session with an industry professional today!
Let's do it
No Thanks