JSON (Standard Object Notation) is a popular format for storing and exchanging structured data. You’d hear much instances of a json files when it comes to the APIs and web services and in the development of apps. While (obviously) upper case’s great for computers to decipher, it’s not always the easiest one for humans to read. Excel on the other hand provides the ability to view the data easily, filter and analyze it.
So what if you receive a file in the form of CSV file but however need to work with the file of Excel? The good news – It is quite quick and straightforward to convert the information from a JSON file to an Excel. Whether you are a student, data analyst or just a victim of someone exporting data to you, hopefully this guide will help you out in rather simple ways you can have your JSON transformed into an organized spreadsheet without fail.
Table of Contents
Method 1: Importing the Directly into Excel
Modern versions of Microsoft Excel (.xlsx version 2016 and above) have the ability to load in data from a .JSON file as an inbuilt function.
Steps:
- Open Excel.
- Go to Data on the top menu.
- Select Get data – From File – From JSON.
- Choose the file below (this format is required – regulation is in the form of a .json):
- Excel will read in the data using the Power Query Editor.
- Click on Convert to Table then Close & Load.
Your json data will now be nicely displayed in a worksheet in a nicely formatted data.
This approach is best used if you want a nice, tidy looking table and your JSON is easily possible to have a standard formatting.
Method 2: Using an Online Calculator
If you don’t want to open excel, first, there are converters of data from one format to another online in which case they can accomplish the task very fast in the format of a(json)->excel converter.
How it works:
- Visit a website on how to convert it to be in the form of a .JSON file.
- Upload your .json file.
- Choose output format to be used (xlsx/csv);
- Then download and open it in Excel after being converted.
This is handy if you are using a smartphone, or are using a chromebook, or don’t have excel installed on your computer.
Method 3: Converting to CSV from the JSON (Then Open in Excel)
As CSV files are easy to read in excel, you can now convert the JSON to CSV file and open it in excel.
Basic idea:
- Take an internet converter from a json to a csv converter
- Or do a very quick conversion using any kind of script tool (Python, Node.Js etc.).
- Download or save the CSV file.
- Double click on the CSV file to open it up excel.
This technique can be used particularly well if your JSON contains array styled information, or it is huge in size of data.
Things to be Careful of While Converting
If you have a nested (data inside data) in your json excel might have some values in expandable columns. Expanding records in Power Query may be of interest to you.
- Large size of a json file may take for longer time to load. Being patient is key.
- Make sure that your json is valid (no formatting errors) – or excel might not be able to import your data
Why Convert JSON to Excel?
If you want to: –
- Convert from the format of a json to exce
- View data in a more understandable and organized manner in rows and columns
- Generate business reports
- Generate the analysis of statistics or trends
- Filter, sort and format huge sets of data easily
- Share readable data to team members who might not understand what is in the form of Isonian
It’s taking complex structure, and making it understandable to the end user and useable.
Conclusion
Although both of them are used for different purposes, surprisingly it is quite easy to transfer data from one to the other. Whether using the inbuilt import tool available in Excel, online converter or an intermediate file (usually in CSV), converting a document from a JSON to a neat raised/spreadsheet will only take a few minutes.
Once it’s been converted, the data is easier to analyze, visualize and work with – and it will make your workflow much easier and more efficient.
If you are doing data work very frequently then learning this simple conversion is an acquisition of a simple trick and will save you time over and over again.
Also Read: How To Use Subtraction Formula in Excel?