JSON is a text-based data exchange format based on a multi-paradigm programming language. Its main purpose is to store and transmit a structured stream of information.
In addition, the JSON format is used to combine objects and data structures together as a set of components, thus forming program units that allow you to store and process complex records consisting of several variables of different types.
Once a file has been created, the strings it contains can quite easily be redirected to another location on the Web via any data path. This is because the string is plain text.
So, here’s how to convert a JSON file to Microsoft Excel.
How to import a JSON file into an XLSX file
If you want to import a JSON file into an XLSX file, you have to follow these steps:
- First of all, open Microsoft Excel on your computer and create a new spreadsheet.
- Then, click on the “Data” tab in Excel’s ribbom at the top.
- In the “Get & Transform Data” section, click on the “Get Data” and select “From File”.
- After that, click on the “From JSON” option.
- You will see the standard “Import” window of your computer. Here, open the folder where your JSON file is located. Double-click the file to attach it to 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 “To Table” option. This will turn your data into a spreadsheet.
- Now, click “OK”.
- To select which columns to leave in the spreadsheet, click the double arrow icon next to “Column1”.
- Next, select the columns you want to save. Then uncheck the “Use original column name as prefix” option 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.
- You can add your JSON data to an Excel spreadsheet by clicking “Close and Load” in the upper left corner of the window.
- 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.
- Finally, to save this JSON data in Excel format, click “File” in the upper left corner of Excel and click “Save”.
Once you have completed these steps, the Excel version of your JSON file with the .xlsx extension is now available in the folder you specify.
How to create a JSON file
The JSON format is usually used to handle (store and use) proprietary information. This is usually a staffing schedule that neither the developer nor the audience of the web resource should see.
There are several ways to create a file with an appropriate extension. First of all, it can be done by means of the usual text editor, which is part of the Microsoft Windows operating system. To do this, open Notepad, insert the appropriate code, and save the document in the usual and only available extension. After that, you need to change it to the desired variant.
The second way involves the use of third-party services. The most popular is JSON Editor Online. It’s much more convenient than Notepad. The interface of the service is presented as two working areas.
In the first zone, the work on the formation of the data directly takes place, in the second zone the tools for this are placed. After the creation process is finished, you must click on the button “Save” and choose how to save the result: to disk or online.
As already mentioned, using the online service is much more convenient than Notepad. This is due to the fact that the service automatically detects syntax errors in the process and highlights them so that the user can notice the omissions and correct them immediately.