Let's explore how to load a json file in the Excel program# Loading a json File in Excel
Before we start, there is something you should know. There might be slight differences depending on the version of Excel you are using. That is, depending on whether you're using a new version of Office or versions 2013, 2010, 2007, etc., some menu options might differ or be absent, so please keep that in mind. Let's explore below.! Why Should We Load a json File in Excel?
Assume there is a need to transfer data from a relational database. In such a case, a non-developer or a user unfamiliar with json syntax might need the data and might prefer it in a format such as xlsx or csv rather than a json file. For such purposes, converting json to Excel is utilized.
"Now, let's find out how to load json in Excel."
Fortunately, Excel already provides a convenient feature for loading json data. Of course, not only json, but you can also load various data formats such as xml, sql, etc. The method is also simple. First of all, click on the Data tab in the menu.Screenshot) First, click the Data menu
1. Click on the Data tab in the top menu
First, you need to click on the Data menu. After clicking, press the first option, "Get Data," from the menu that appears.Screenshot) Select 'Load from JSON' in File
2. Select Load from JSON
Inside 'Get Data', you'll be prompted to choose from various data formats. Here, select File > Load from JSON. Note that in previous versions, there might not be a Load from JSON option in the File menu.
3. Click 'Transform to Table'
Now, if you look at the first option in the top menu, there is a menu option called 'Transform to Table.' Select this menu option.
4. Click the bidirectional arrow icon in the column
The table is displayed, and each row is entered as 'Record.' You must expand this Record to see each column data inside. Click the bidirectional arrow icon in the top column to expand it, then click the Confirm button.
All the internal columns are now added and appear as a table. This data is precisely the Excel data you wanted. If you look at the top menu, you can find the 'Close & Load' button. Clicking this button will load the data into a new Sheet.Screenshot) Appearance of Converted Table in Excel Format
Now, all the steps are completed. As a note, if there are any unnecessary columns during the conversion process, it's recommended to uncheck them to remove them before finally closing.! When Record, List, etc. Types Exist Under Table Column
Aren't Record, List, etc. values also printed inside the table column? There is a solution for that. Earlier in step 4, we expanded the table by clicking the bidirectional arrow icon, right? You also have to expand the other Record, List, etc., types present within the column by clicking the bidirectional arrow icon. That is, you must expand the insides of the internal columns. Perform full expansion in step 4. After that, when you click the 'Close & Load' button at the end, all will be expanded and displayed as a table.# In Conclusion
So far, we have looked at how to load a json file in Excel. If you search, you will find many tools for converting json format to Excel. However, you may have to download a separate app or convert it online, so using Excel in the installed Office suite is arguably the most convenient and safe method.