In this tutorial we’ll learn how to parse or import the JSON file in Microsoft Excel by following these simple steps.
Sometimes you receive the data from webpages as JSON (Java Script Object Notation) files. JSON files are lightweight, simple and text based, easily readable by anyone, and can be edited using any text editor as simple as a notepad.
Step 1 – Go to the Main Menu Tab
– To start the process of parsing a JSON file to Microsoft Excel, click the Data tab from the main menu tabs in the spreadsheet window.
Step 2 – Go to file from Get Data —> From File —> From JSON
– On the Data tab, from the Get & Transform Data section, select Get Data —> From File —> From JSON.
– This will open up a new dialog box and will ask you to choose the JSON file.
Step 3 – Choose the JSON file
– On this new dialog box, browse your way to the json file that you wish to parse to Excel.
Step 4 – Transform JSON Data To Table in Excel’s Power Query Editor
– When you click on the import button the JSON file will be loaded into Excel and Excel will open up the power query editor automatically.
– Here you will choose how the data should look like when it will be loaded to excel spreadsheet. For this purpose choose To Table option in the Power Query Editor and then using the default options click OK on the next dialog box.
Step 5 – Choose appropriate data from JSON file
– Clicking on the OK button will load the JSON data as a table in Power Query Editor. Now click on the double arrow button to open up the options to choose the required columns from the complete data.
– In the example file, we’ll choose only a few columns that are necessary for us. So after choosing the required data press the OK button that will populate the data in Power Query Editor.
Step 6 – Click Load & close to load data from JSON file in Excel
– Now press the close and load button and the selected fields will be loaded into Excel as separate columns of the Excel Table.