If you have data in the form of a JSON file that you wish to transform into an Excel file and then import it into Excel by using Power Query.
JSON is short in short for JavaScript Object Notation, is an open standard format for files (text-based) that is utilized to store and transfer information. It is typically utilized to transfer data from the server to web pages (e.g. sending information from the server back to the user and allowing it to be displayed on a web page and reverse).
JSON is an information exchange format that is an unformatted text file (like XML). If you have important information stored in the format of a JSON file you’d like to transfer to an Excel spreadsheet it is possible to accomplish this using Excel’s ‘Get and Transform’ feature. And you don’t require any VBA software to transfer data either from your local disk or an API on the web. Let’s look at how to import or convert the contents of a JSON file into an Excel spreadsheet.
Table of Contents
How do I import JSON file into Excel
JSON is represented as an easy-to-read, logical data structure. It is comprised of just two types of data either arrays or objects, or a mix of both. Objects are key-value pairs that have an asterisk between them. Arrays are simply collections of objects. arrays are groups of objects that are separated by the use of a comma.
It is easy to convert JSON files into Excel documents (.xlsx) by using Excel’s “Get & Transform’ tool (Power Query). At first, it was named ‘Data Explorer’ and later it was changed to Power Query. Power Query is only available in the 2010 and 2013 version of Excel. The Power Query feature was added to Microsoft Excel 2016, 2019, as well as 365, the feature was again changed to the ‘Get and Transform’ option under the Data tab.
This is what an JSON file should look:
We now have an example JSON file titled Employees, as seen above. Let’s look at how we can transform this JSON file into the format of an Excel file.
JSON Data
JSON information is written in names/values. A name(key)/value pair is comprised of the field’s named (in double quotation marks), then a colon, followed by a value
"First Name": "Dulce"
JSON Objects
JSON objects may contain several name/value pairs (just as in JavaScript) and they are written in curly braces, as illustrated below.
"First Name": "Dulce", "Last Name": "Abril", "Gender": "Female", "Country": "United States", "Age": "32", "Date": "15/10/2017", "Id": "1562"
JSON Arrays
JSON arrays can be written in square brackets ( [] ) and is an array of objects.
Importing JSON File into Excel
In the above example in the above example, the JSON array is made up of many objects. Each object represents the employee (with First Name Gender, Last Name Age, Country and Date as well as an ID). In this video we will be using Excel 2016′ to show the import of data.
Then, open Microsoft Excel and switch to the tab ‘Data’ and then click the “Get Data button within the “Get as well as Transform Data’ group at the left-hand edge on the ribbon. From the drop-down, expand “From File option and then choose the option ‘From JSON’.
If you click “From JSON option, you will be presented with a window for file browsing. Locate your JSON document on the local disk , and select ‘Import’.
If you are looking to import data using web API (Web API Programming Interface) You may wish to directly import the data via the web. To accomplish this, instead of selecting the “From JSON’ option, click the Data tab > Find Data > From Other sources > From Web, and enter the URL of the website.
If you click the ‘Import’ button, as in the screenshot above the program will take you to Excel Power Query Editor. Excel Power Query Editor. It will show all the entries in that list are broken down into rows. However, we aren’t able to view the actual information. For the conversion of this data into tables, select the “To Table” option.
The To Table dialog box will show up. In the dialogbox, you can keep the default settings and click “OK”.
Your data is now in table format, however, you aren’t able to see the details of the record. To expand the columns, simply click the Expand Column’ option (icon that has two arrows that point away from each other).
The columns will be displayed which are included in the table. Choose the columns you would want to include in the table , and select ‘OK’. Select the columns you would like to exclude.
The data will be divided into columns, as shown below.
You can also move columns as you feel appropriate. For this to be done, click right on the column’s header, choose “Move” and then select the location you want to move it.
When you are happy with the layout After you are satisfied with the layout, click the ‘Close and Load’ option under the tab ‘Home’ to load the data into Excel as a Table.
The data will be transferred to a new spreadsheet in Excel.
Convert JSON File into Excel File Online
If you’re looking to quickly convert JSON files into Excel documents (.xslx) make use of one of the numerous websites that are third-party and accessible online. These websites can transform your data in just a few minutes, but they’re not always trustworthy. Just type ‘convert JSON to Excel in the search bar and you’ll find a myriad of sites to use.
One of the sites that you can utilize for converting JSON into XSLX can be found at json-csv.com. Visit the site and click on the “Upload JSON file’ for uploading JSON files. JSON directly from the local drive. Locate your JSON document on the drive and click “Open”.
After you’ve uploaded your file to the server, you’ll see an overview of your table below. After that, click on the ‘Excel File (XLSX) button to download the converted Excel file.
That’s it! This is how to import the .json File into Excel.