JSON (JavaScript Object Notion) is based on a subset of the JavaScript programming language and is often used to exchange data between a client and a server and store configuration files.
Similarly, Excel is one of the most widely used formats to store and exchange data, especially as it organizes data in rows and columns, which makes it easier for data analysis.
While JSON is a popular standard due to its human-readable structure, many times you might need to present and analyze large datasets in a more familiar format, easier to organize and manipulate.
So, in this guide, we will show you how to convert and import a JSON file to Excel on Windows or Mac. Let’s get into it.
How to Import and Convert JSON to Excel
Excel has an integrated feature that enables you to import data without the need for external processing programs. So, importing or converting files to any format becomes extremely easy for users.
Follow the steps to import and convert a JSON file in Excel:
1. Open MS Excel and create a new worksheet.
2. Click on Data from the menu and then on Get Data > From File > From JSON.
3. From the pop-up window, select your JSON file and click on Import.
4. Click on Record in the Power Query Editor that appears after you import the file and then click on user.
5. Click on To Table Convert in the top left corner of the screen and then on OK to confirm your action.
6. Now, click on the double-arrow icon next to Column 1, choose the columns you want to keep, deselect Use original column name as prefix, and finally click on OK.
Your JSON file’s data will be converted to Excel JS and you will be able to see them arranged in columns and rows.
7. If required, swap the rows and columns as preferred, and then click on Close & Load in the top left corner of the screen.
Make sure to save the file on your PC.
Also Read: How to Unprotect Excel Workbook Without Password
How to Import JSON data to Excel using VBA
Excel has integrated VBA (Visual Basic Applications) programming language that allows users to automate tasks and create custom functions.
You can also import JSON data using VBA in Excel. Here’s how:
1. Open MS Excel, right-click on any tool from the menu, and then click on Customize the Ribbon…
2. In the Excel Options pop-up window, click on Developer, and then on OK.
3. Open your table, click on the Developer option from the menu, and then on Visual Basic at the top-left corner.
4. Now, right-click on your sheet and select Import File… from the context menu.
5. Select the JSON file from your desktop and click on Open to import it to Excel.
How to Import and Open JSON to Excel Online
Excel also gives you the feature to import JSON files directly from the web. Follow the steps:
1. Create a new sheet in Excel, and click Data from the top menu, followed by From Web.
2. Enter the URL in the pop-up window and click OK.
3. Click on Connect, then right-click on List and select To Table from the context menu.
4. Click on OK to confirm your action.
5. In the pop-up window, click on the double arrow icon beside Column 1 and select OK.
6. Finally, click on Close & Load at the top left corner of the screen to import the JSON file.
Also Read: How Two Way Synchronization Between SharePoint List and Excel Table Works?
Now that you have understood how to import JSON to Excel, go ahead and make use of Excel to its full capabilities.
If you have any queries or suggestions, do not hesitate to reach out to us in the comment section. Keep visiting TechCult for more tech-related know-how.