Как конвертировать json в excel
JSON to Excel Converter
Online Converter: Convert JSON file into Excel format
JSON to Excel File Converter
JSON to Excel Converter transforms the structured content of a JSON file into a table representation and saves it as an Excel file.
See examples below.
Why convert JSON to Excel?
JSON format used in a wide range of software applications and although it is a text format, it is difficult for a human to read and understand it visually.
JSON File Format
JSON Format
JSON means JavaScript Object Notation.
JSON file is a text file and it can be edited in any Text Editor.
JSON Format Data
The JSON file contains different types of data: objects, arrays, and attributes.
Where the object is a data structure with a set of key-value attributes on the same level.
The Attribute is a key-value, where the key is the name of the attribute. Attributes can only be used inside objects.
And Array is the set of objects.
Where JSON format is used?
The JSON file format is used for storing data or configuration, and for data interchange between different applications.
File Format Information
How JSON to Excel Converter works?
Based on the predefined rules we transform JSON Objects into a flat table representation, and Arrays act as a set of rows in the table.
Source JSON file:
Result Excel file:
How to Import JSON to Excel Using VBA
16 Aug How to Import JSON to Excel Using VBA
Howdee! It’s becoming increasingly more common for data to be generated in a JSON format as opposed to XML. XML was widely used for years, but recently JSON has started to become the preferred method of data exchange. Many REST APIs have the option to return both but the majority that I interact with default to returning JSON formatted data. Therefore, as excel ninjas, it’s important to understand how to import JSON to Excel for analysis. Before we begin on that route, let’s take a quick moment to talk about what JSON is for those that aren’t familiar with it.
Curious how to do this in VSTO? Click Here!
What is JSON?
JSON stands for JavaScript Object Notation and is a lightweight data-interchange format. In layman’s terms, it is a string of text that represents a universal data structure. It is easy for humans to read (when formatted properly) and, because of the universal structure, it is very easy and fast for machines to parse and generate. It’s made simple because JSON, at it’s most basic, is only two data types. It is made up of objects, or arrays, or a combination of both. Objects are made up of key/value pairs (often called a dictionary) and arrays are simply collections of values or objects separated by a comma.
JSON Object
A JSON object always begins with < and ends with >. The key is separated from its value with a colon (“:”). The key/value pairs will then be separated by commas all within the curly brackets. In the image below, the first key “color” corresponds to its value “red”, while the second key “value” corresponds to the red hex code “#f00”. Hopefully you can see why this might be called a dictionary as you look up the key (word to define) to get its value (definition of the word).
JSON Array
A JSON array, in its simplest form, is a list of values separated by a comma that begins with [ and ends with ]. In the below image, the JSON array is a list of string data points. We can see that they are colors but there is no key in this example explicitly telling that this string represents a color. Therefore, JSON is most commonly seen as a combination of arrays and objects.
JSON Objects & Arrays Together
Most of the time, JSON is returned as an array of objects. If you recall, an object always begins and ends with curly brackets. Therefore, the array of objects would have several objects enclosed in curly brackets that are separated by commas and all objects are enclosed in normal brackets (the array). That looks like this image:
In this example, each object represents a color and its corresponding hex code. In Excel, this would likely be two columns of data. One column for color, and the other for its hex code value. Now that you’re more familiar with JSON data structure, let’s learn how to import JSON to Excel!
Setup JSON to Excel
There are several libraries out there to help you import JSON to Excel. The most popular of these is VBA-JSON which is available here on GitHub. This library will do most of the hard work of parsing the JSON string to a dictionary for you and then you can write the values to an excel worksheet. When you download the latest release, you’ll need to import the “JsonConverter.bas” file to your VBA project. This GIF shows how that is accomplished. You will also need to add a reference to the “Microsoft Scripting Runtime” by clicking tools from the ribbon, selecting references and checking the box for Microsoft Scripting Runtime as shown in the screen grab.
Once you have completed both steps, insert a new module into your project and title it whatever you like. To get started, we need to dimension some variables to use later.
The ws variable will represent a worksheet in excel. In this example, we will use this worksheet to both read in our JSON string from and write the parsed JSON to Excel. To assign a worksheet to this variable, set it like this:
The jsonText variable will represent our string of valid JSON data. For this example, I’ve pasted that string of colors and hex codes we looked at earlier into cell A1 on the JSON to Excel Example tab. To assign that string to a variable, type this code:
This step is not necessary for the code to work for us. We could simply reference the cell in the worksheet that contains the JSON. However, most of the time you’ll be returning your JSON string from another data source, most likely a HTTP GET Web Call. If you’re not familiar with HTTP Requests in VBA, click here to learn more.
Lastly, to put the JSON string into the jsonObject, we will use one of the methods contained in the JsonConverter file you imported to begin this example. Since the builder of this library made all of the subroutines public, it is callable from the module this code is in. That call looks like this:
This call instructs the JsonConverter module to parse the JSON string you’ve passed it using the jsonText variable to a collection of dictionaries that we can now access. Now that we’ve finished our setup, we can start learning to import JSON to Excel in the next section.
Import JSON to Excel
The code to import JSON to Excel might sound daunting, but it is much easier than it sounds. At a high level, all you have to do is loop through the jsonObject you’ve created, and write each dictionary value to the sheet. The wonderful thing about dictionaries, is this is easy to do with a for each loop. I’ve added a couple of variables and a for each loop to our code and the result is this:
I set a counter variable, i, that I can use to tell the loop which row to write the data to. Next, create my column headers for the data. Then, for each “item” in the jsonObject write that dictionaries values to the cell row I indicate and increment my counter variable. That’s it! The results of our import JSON to Excel code looks like this:
As you can see, we’ve turned that odd-looking string of text in cell A1 to an easy to understand table of data with just a few lines of code!
Understanding JSON to Excel (Advanced)
Now, I do want to point out that understanding the structure of the JSON you’ll be parsing is very important. Within the for each loop, the code can access the value of each object by referencing its key. This goes back to understanding the key/value pair relationship of JSON and the dictionary structure to which it is parsed. The reason I stress this so much is because, when you’re pulling back more complex looking JSON, you may need to have multiple levels of dictionary references to access the value you want. There may also be times when you have to nest for loops when you have an array of objects with arrays of objects nested inside each object.
For example, let’s look at an example where the JSON is structured as a single object with key “data” and the value is an array of data about employee’s and their organizations:
As you can see, we have an object with an array nested inside. Some objects in the array contains other nested objects as well. This multi-tiered data structure is very common when pulling data from a REST service and was very confusing to me when I first began trying to import JSON to Excel. Let’s examine what the code looks like for this scenario before we dive any deeper.
The code overall is the exact same, just referencing a different tab and added more fields to pull out. The change I want to point out is to the for loop itself. The beginning of the for loop now starts by referencing the overall object that contains the array. This is referred to as the “root” of the JSON and you’ll see it if you’re querying a REST API for JSON data. This moves the for each loop inside the “data” level of the JSON and will now loop through all objects in the highest-level array. Had I left this out, the loop would only occur once as the JSON is all contained within a single object at its top level.
The second piece I want to point out is the lines for address and company. These are both objects contained within the parent object. So, in order for me to reach to value I want, I have to reference both keys to get the specific value I need. Lastly, I want to call out that you do not need to write every value from the returned JSON to the sheet. I have skipped over several fields to get the end result I desired and it does not affect my code results, which coincidently look like this:
I hope this brief explanation of how to import JSON to Excel was useful for you! Please let me know if it was helpful or if you have any questions in the comments below. Also, if you want to download the file with the example JSON and VBA code, you can do so here if you’re a free subscriber to the site. It’s free, easy, and only takes a moment to sign up.
How to Convert JSON to Excel in Python with Pandas
In this Pandas tutorial, we will learn how to export JSON to Excel in Python. This guide will cover 4 simple steps making use of Python’s json module, and the Python packages requests and Pandas.
Basic Code Example to import JSON to Excel with Python
Here is the easiest way to convert JSON data to an Excel file using Python and Pandas:
Conversion
In this section, the problem will be briefly formulated. Now, although we can work with data using only Python and its packages, we might collaborate with people that want to work in Excel. It is, of course, true that Microsoft Excel is packed with features to keep and organize tabular data. Furthermore, it might also be easier to get an overview of the data in Excel. Now, a lot of open data resources are storing data in a variety of different file formats. But sometimes, we might find out that the data is stored in the JSON format only. In some cases, we might want to obtain data from JSON and save it to Excel and send it to our collaborators. Now, in other cases, we just want to explore data ourselves, as mentioned before, using Excel.
Despite it is possible to import JSON to Excel with Microsoft Excel itself, the optimal way is automating the conversion by importing data from JSON to Excel worksheets programmatically. Especially, if we already know Python.
Prerequisites
In this section, you will learn what you need to have installed to convert JSON data to Excel files.
Now, there is one very easy way to install both Python and Pandas: installing a Python scientific distribution such as Anaconda, ActivePython, or Canopy (see here for a list of Python distributions). For example, if you install Anaconda you will get a library with useful Python packages, including Pandas.
How to Install Pandas and openpyxl
If you already have Python installed, you can use pip to install Python packages. To install Pandas and openpyxl using pip open up Windows Command Prompt or the Terminal you prefer and type the following code:
Sometimes, pip will warn us that there’s a newer version. If this is the case now, read the post about upgrading pip for more information.
Note, that it is generally a good idea to create a virtual environment and install your new packages in this environment. Now, that you have all that you need we will continue to the next section of this tutorial. If you use a tool such as pipx you can automatically install Python packages in virtual environments. Here’s a YouTube video on how to install Pandas and Anaconda:
As a final note, before converting JSON to Excel with Python: if you need, you can use pip to install specific version of Python packages.
4 Steps to Convert JSON to Excel in Python
In this section we will go through, step-by-step, how to import JSON data and save it as an Excel file using Python. Here’s a summary of what this chapter will cover: 1) importing pandas and json, 2) reading the JSON data from a directory, 3) converting the data to a Pandas dataframe, and 4) using Pandas to_excel method to export the data to an Excel file.
1. Importing the Pandas and json Packages
First, we start by importing Pandas and json:
2. Reading the JSON file
Now, we are ready to import the data from a file using the load method:
As you can see in the code chunk above, we are first opening a file with Python (i.e., as json_file) with the with-statement. Moreover, in the with-statement we are using the load method. This is when we are actually reading the JSON file.
3. Creating a Pandas Dataframe
Now, before we can save the data, we have imported we need to create a dataframe:
In the code chunk above, we used Pandas DataFrame class to create a dataframe from the JSON data we have, in the previous section, loaded into Python. Basically, what we do is similar to converting a Python dictionary to a Pandas dataframe. Note, adding new columns to the dataframe, before saving it, is of course also possible.
Finally, we can use the to_excel method that is available in the dataframe object:
Now, if we want to convert JSON data to other formats such as CSV, SPSS, or STATA to name a few we can use other methods available in the dataframe object. For example, if we want to save the JSON data to a CSV file we can use the to_csv method. If you are interested in learning more about reading and writing data files with Python and Pandas check out the following blog posts:
Now, it is worth mentioning here that we actually can skip step 2 and use Pandas read_json method to import the JSON data to a dataframe. Here’s a full working example on how to save JSON data with only the Pandas package:
In the next section, we will have a look at how to read JSON data from a URL and save it as an Excel file.
JSON to Excel: Reading data from a URL
In the previous section, we learned, step-by-step, how to automatically convert JSON data to an Excel file in Python. Here, we are going to use the same steps, but we are going to read the data from a URL. First, we are going to look at a simple example where we can use the same code as above. However, we are going to change the string so that it is pointing at the URL location instead of a directory.
Now, as you can see, we managed to read this JSON file with Python and save it as an Excel file using Pandas. In the next section, we will look at a bit more complex JSON file where the data is nested.
Nested JSON data to Excel
In this section, we will look at a bit more complex example. Now, there are times when the JSON data is nested and if we use the method above the Excel file will be messy. Luckily, we can fix this by using json_normalize from Pandas and the requests module:
In the code chunk above, we first imported requests and then json_normalize. Second, we created a string with the URL to the JSON data we want to save as an Excel file. Now, the next thing we do to use the get method which sends a GET request to our URL. Briefly, this is pretty much reading in the webpage that we want, and we can, then, use the json method to get the json data. Finally, we used the json_normalize to create a dataframe that we save as an Excel file. Again, saving the data we read from the JSON file is done with the to_excel method.
Import JSON to Excel and Specifying the Sheet Name
In this section, we will specify the sheet name using the sheet_name argument. Now, it might be obvious, but the string we input will be the name of the Worksheet in the Excel file:
In the example code above, we named the sheet ‘Session1’. However, we still can see the index column in the Excel file. In the next section, we will have a look on how to remove this index.
JSON to Excel with Pandas Removing the Index Column
To remove the index column when converting JSON to Excel using Pandas we just use the index argument:
Now, this argument takes a bool (True/False) and the default is True. Thus, in the code example above we set it to False and we get this Excel file:
Of course, there are plenty of more arguments that we could use when converting JSON to Excel with Pandas. For instance, we can set the encoding of the excel file. Another thing we can do is to set the engine: openpyxl or xlsxwriter. Note, to use the to_excel method to save the JSON file to an Excel file you need to have one of them installed on your computer (see the previous section). See the documentation for more information on how to use the arguments of the to_excel method.
Other Options: the JSON to excel converter Package
Now, there are of course other methods and/or Python packages that we can use to convert JSON to Excel in Python. For example, we could work with the json module and choosing one of xlsxwriter or openpyxl packages. It is worth noting, however, that the resulting Python script will be a bit more complex. Furthermore, there is a Python package created just for the purpose of converting JSON to Excel: JSON to excel converter.
JSON to Excel with the JSON to excel converter package
This package is very easy to use. In the example code above, we converted the airlines.json data to an Excel file. Remember, this data is nested and one very neat thing with the JSON to excel converter package is that it can handles this very nicely (see the image below for the resulting Excel file).
Conclusion
In this post, we have covered a lot of things related to opening JSON files and saving them as Excel files using Python. Certainly, knowing how to convert JSON data to Excel files might be useful in many situations. For instance, if we are getting the data and collaborating with someone who prefers using Excel. To summarize, in this tutorial we have used the Python packages Pandas, json, requests, and JSON to excel converter to read JSON files and save them as Excel files. First, we had a quick look at the syntax, then we learned 4 steps to converting JSON to Excel. After that, we have also learned how to read the JSON data from a URL, and how to work a bit with some of the arguments of the to_excel method.
Hopefully, you learned something from this Python tutorial and if you did find it useful: please do share it on your social media accounts. Finally, if you have anything you would like to learn please leave a comment below and I might put that together in a new tutorial.
Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query
If you have data stored in JSON format that you would like to import into Excel, it’s now very easy and doesn’t require any VBA to import data locally from the disk or from a web API. Watch the video or read the steps below!
Step 1: Open The Data in the Query Editor
When clicking “From JSON”, you will be presented with a file browser. Find the file on your disk and click Import. This should bring you into the query editor. If you are using a web API, you may choose instead to import the data directly from the web. To do this, instead of clicking “From JSON”, click “From Web” and enter the URL. This should fetch the file and bring you into the query editor.
Step 2: Craft the Query
Start by finding the list in the data. Click through any single records until you find a list. Click it to open the list of records.
Click through the record to see what’s inside 
Once you find the list, go ahead and click it to open it.
One you have found the list you are looking for, the list needs to be converted to a table for Excel to use. Click “To Table” to convert it.
Click “To Table” to convert the list to a table.
Once you have the list as a table, you need to specify which columns you want excel to make use of as well as have the opportunity to specify how any inner lists (array fields) may be dealt with. Click the icon with two arrows pointing away from one another to expand the table. You may want to rename the table before doing this. To do so, double click the name and type a new one.
Click the arrow icon to expand the table. Select the columns you want Excel to make use of and then click OK.
Step 3: Bring the Table Back Into Excel
Click Close and Load to bring the data back into Excel as a Table. A new sheet may be created.
Click Close & Load. The query editor will close and the data will appear as a table in Excel.