How to Convert JSON To Excel Offline – Step By Step

If you have content in a JSON format that you would like to export to an Excel spreadsheet. You may use Power Query to transfer it into Excel. Today, this article will describe how to convert JSON to Excel.

What is JSON?

The acronym JSON stands for JavaScript Object Notation. It is a text-based open standard file format for storing and transmitting data. You can send information from the server to a website with the help of this. For instance, you might send data from the server to the browser so that everyone can view the information on a web page.

JSON is a plain-text data transmission format.  If you want to input data from a JSON file into an Excel spreadsheet. Then you’ll need to use the JSON to Excel converter. So, using Excel’s Get and Transform function, you can simply achieve that. Importing content from a local hard disk or a web API does not necessitate writing any VBA code.

Let’s have a look at how to import/convert a JSON file to an Excel workbook.

How to Insert a JSON File into an Excel Spreadsheet

JSON is a global data structure that is logical and easily understood. It consists of just two forms of data: objects and arrays, or a mix of the two. Objects are key-value combinations separated by dots. Arrays, on the other hand, are just collections of items split by a colon.

When working with JSON data, Excel’s “Get & Transform” function can quickly turn JSON data into Excel data. Data Explorer was the original name before it got its new and updated name ‘Power Query.’

Excel 2010 and 2013 are the only variants that include Power Query. The ‘Get & Transform’ function on the Data tab was updated in Microsoft Excel 2016, 2019, and 365.

Let’s explore how to make an Excel sheet from a JSON file.

JSON Data

This allows to express JSON data using Name/value pairs.. Field names (in double quotes), followed by a semicolon and a value form a name(key)/value pair.

JSON Objects

A JSON document can contain numerous name/value combinations. Around the term they are, they use curly brackets to emphasise their point.

JSON Arrays

A JSON array is a set of objects enclosed in braces ([]).

Accessing Data from a JSON File in Excel

The JSON array has a large number of items. In addition, every item is documentation of a worker like first and last name, date of birth, address, etc.  This lesson will be implementing ‘Excel 2016’ to illustrate data migration.

  • To begin, launch Microsoft Excel and navigate to the ‘Data’ menu.
  • Then, in the ‘Get and Transform Data’ category in the lower-left corner of the menu, press the ‘Get Data’ button.
  • Click the ‘From File’ drop-down and pick the ‘From JSON’ choice.
  • Once you select ‘From JSON’, a file explorer will open. Select ‘Import’ to add a JSON file from your computer.
  • If you wish to integrate data from a web API, you may choose to import the data from the internet straight.
  • Rather than selecting ‘From JSON’, go to Data > Get Data > From Other Source > ‘From Site’ and input the web address.
  • To transfer a file, simply click the “Import” tab on the toolbar.
  • You’ll see that the records in that list have been divided into rows. However, we are unable to view the real data. To make a table out of this list, select ‘To Table’ from the list’s drop-down menu.
  • You’ll see a new dialogue box called To Table. Keep the default settings in the window and click ‘OK’ to proceed.
  • The content is now in a tabular format, but the record details are still hidden. Choose the ‘Expand Column’ option to make more room for your data.
  • The columns that are mentioned in the documents will be visible to you. Click ‘OK’ after selecting the columns you want to include in the table. Pick the columns you want to omit and uncheck them.
  • After then, the information will be separated into several columns.
  • You may also re-arrange the columns to suit your needs. To change a column header, just right-click on it, pick ‘Move’, and then tap the new location.
  • Once you’re happy with the design, move on to the next phase. To transfer data into Excel as a Spreadsheet, select the ‘Home’ tab and then the ‘Close and Load’ option.

Convert JSON to Excel Offline by python

Yes, you can use Python to convert JSON to Excel offline. One popular library for working with JSON and Excel in Python is pandas. Here is an example of how you can use pandas to convert a JSON file to an Excel file:

import pandas as pd

# read json file
df = pd.read_json(“yourfile.json”)

# write dataframe to excel file
df.to_excel(“yourfile.xlsx”)

In this example, “yourfile.json” is the name of the JSON file you want to convert and “yourfile.xlsx” is the name of the Excel file you want to create. The pd.read_json() function is used to read the JSON file and store it as a pandas DataFrame. The to_excel() function is then used to write the DataFrame to an Excel file.

 

Online Conversion of JSON to Excel

If you need to convert JSON data to Excel files fast (.xslx). You might utilize one of the numerous third-party websites that are available on the internet. They have the capacity to switch your data in a couple of moments. But they are sometimes not accurate.

Try searching for “convert JSON to Excel” in a search query and you’ll find a plethora of websites to choose from.

json-csv.com is among the services that can help you export JSON to XSLX. To submit the JSON from your local drive, go to the website and hit the ‘Upload JSON file’ icon. Select ‘Open’ after finding the JSON file on your hard drive.

You’ll see a sample of your spreadsheet when you’ve uploaded the data. Next, to get your transformed Excel file, press the ‘Excel File (XLSX)’ option.

Using Feature Manipulation Engine

Websites and online apps provide a great deal of information that may be analyzed using spreadsheet software such as Microsoft Excel. APIs that produce JSON data are common in web applications.

FME can simply read and convert this JSON data for usage in Excel. FME (also known as the Feature Manipulation Engine) has compatibility for dozens of file types and programs, as well as transformation functions.

FME’s goal is to help you be more productive, so you can focus your energy using your data instead of battling with it. Once the straightforward graphical user interface has been set up, it’s easy to operate.

JSON to Excel processes may be automated and performed in the background with FME Server. The FME GUI is user-friendly and straightforward to operate. There’s no need to learn any programming languages.