Microsoft Excel has a huge number of cool features. Many would say there is a free equivalent in the form of Google Sheets. However, can you open a JSON format file in Google Sheets as easily as you can in Excel?
Microsoft Excel has a built-in option to help you import JSON without third-party parsing tools. You can view the JSON data, select the columns you want to contribute to the Excel file, and load the data into the table.
So here’s how it works.
How to convert a JSON file to Microsoft Excel
If you want to import a JSON file into an XLSX file, you can follow these steps:
- First, open Microsoft Excel on your PC and create a new spreadsheet.
- Then go to the Data tab and click Get Data.
- After that, select From File and From JSON.
- You will see the standard Import window. Here, open the folder where your JSON file is located and open it in Excel.
- Excel will open a Power Query Editor window. This is where you decide how the JSON data will be loaded into your spreadsheet.
- At the top of the window, click on the Into Table option. This will turn your data into a spreadsheet.
- To select which columns to leave in the spreadsheet, click on the arrow icon next to Name and Value.
- Now, select the columns you want to save and click OK.
- Your JSON file data should now be displayed in Excel-style columns and rows. If you want to delete or change the position of columns, right-click the column and select the appropriate option.
- Finally, add your JSON data to an Excel spreadsheet by clicking Close & Load in the upper left corner of the window.
This is what you roughly should have in the result.
Your Excel spreadsheet now has data in JSON format. You can use this data however you want. For example, you can remove table formatting or even convert the table to a range. Once you’re done, you can save your document using the File tab.
What is JSON?
JSON (JavaScript Object Notation) is a text-based data exchange format based on a multi-paradigm programming language. Its primary purpose is to store and transmit a structured stream of information.
Using simple rules for forming symbolic constructions in JavaScript, a person can provide a simple and reliable way to store any information, whether a simple number, an integer string, or a vast number of different objects expressed in plain text.
In addition, the JSON format combines objects and data structures into a set of components, thus forming software blocks that allow complex records of several variables of different types to be stored and processed.
Once a file is created, its strings can be easily redirected to another location on the Internet via any data path. This is because the string is plain text.
How to create a JSON file
There are several ways to create a file with the JSON extension.
First, it can be done with a plain text editor. Fortunately, Windows has a built-in Notepad. To do this, you need to open it, insert the appropriate code and save the document in JSON extension. In addition, you can install an external code editor, which will have a more versatile functionality than Notepad.
The second way involves using an online editor. One of the most popular and convenient is JSON Editor Online. The service interface is presented as two working areas. The first area is where you work directly with forming data, and the second contains tools for this purpose. Finally, you can save your file on your PC or in the cloud in the format you need.
Frankly, I find using the online service much more convenient than Notepad. This is because the service automatically detects syntax errors and highlights them. Thus, the user can notice the omissions and correct them immediately.
