Converting JSON file to Excel

JSON (JavaScript Object Notation) is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. It is a text-based format that uses a simple syntax to represent data structures and objects consisting of key-value pairs. JSON is often used for storing and exchanging data between a client and a server, or between different systems. It is used widely by all Cloud providers.

A JSON file is typically saved with a .json file extension. The file is a plain text file and can be opened and edited with any text editor. JSON files can also be opened and parsed by a variety of programming languages, such as JavaScript, Python, and Java, making them a versatile format for data interchange and storage.

Many times we need to open these .JSON files in excel format to analyze the data. Below is one example of a JSON file taken from Oracle Cloud.

Open Excel file. Use below navigation and select “From JSON”:

Click “Into Table”

Click on Name Down Arrow

Uncheck the “Summary” as our data is in “results” and not in “summary”

Click OK

Click on “Expand Column” button and select to “Expand to New Rows”

UNCHECK “Use Original Column Name as prefix”

Click OK.

AGAIN Click on “Expand Column” button and select to “Expand to New Rows”

Click Load More and click OK.

AGAIN Click on “Expand Column” button and select to “Expand to New Rows”

Click Load More and click OK.

AGAIN Click on “Expand Column” button and select to “Expand to New Rows”

Click Load More and click OK.

NOTE: LOOK FOR ANY MORE COLUMNS WHICH HAS “EXPAND COLUMN” BUTTON.

Example below “oracle” column has “EXPAND COLUMN” BUTTON.

AGAIN Click on “Expand Column” button and select to “Expand to New Rows”

Click Load More and click OK.

Once all columns are expanded then click “Close & Load”

Data will get converted to Excel.

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
• Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
• Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023