How To Import JSON To Excel
Whatever the case, you can easily convert JSON into a CSV file and open it in the spreadsheet software of your choice. So, here are the steps to import a JSON file into Excel. Let us dive straight in.
6 Steps: Import JSON In Excel
MS Excel, as you know, is one of the most accessible spreadsheet software options in the world. It is readily available on most modern MS Windows devices and has an estimated 850 million users globally. If you're using Windows and MS Excel 2016 and newer versions, and want to import a JSON file follow these steps below. (If you are using MS Excel 2010 – 2013, skip to the next section.)
1. Open MS Excel
Open the Start Menu using Windows Keys or Clicking the Start Menu icon on your Task Bar. Search and open MS Excel.
2. Create New Workbook
Next, you need to create a blank spreadsheet. Once you have MS Excel’s welcome page in front of you, click on Blank Workbook in the New section. It will create a new spreadsheet for you to work with.
3. Locate Data Tab
Find and click on the Data tab in the Ribbon menu of MS Excel.
4. Import Data
When you click on the Data tab, it brings up several sections, and one of them is Get & Transform Data. In the section, Click on Get Data, select From File option and then select From JSON option.
5. Select JSON File
Clicking on From JSON option will bring up an import window. You have to select the drive or folder where your JSON file is located and select the file. Click on Import, and Excel will open the Power Query Editor window. Here, you have the option to choose the way you want data in JSON to appear in a spreadsheet.
6. Convert Data Into Table
At last, to convert JSON into a table, select the option Into Table. And then click Close & Load option in Home tab of Power Query Editor. That’s it. You have a JSON file in a tabular format open in MS Excel.
Now all you have to do is open the File menu and save your spreadsheet. Optionally you can export the data as a CSV file.
Import JSON To MS Excel 2010 – 2013
The steps mentioned above will work on MS Excel 2016 and newer versions. If you are using MS Excel 2010 – 2013, follow the following steps to import JSON to Excel:
a. Open MS Excel from Start Menu.
b. Open New Spreadsheet.
c. In the Power Query tab, click From Other Sources and select Blank Query.
d. In the Query Editor, select Advanced Editor.
e. Enter your query string as given below.
Note: Replace “C:\Users\XYZ\Desktop\examplefile.json” with the path to your JSON file.
let Source = Json.Document(File.Contents(“C:\Users\XYZ\Desktop\examplefile.json”)), # “Converted to Table” = Record.ToTable(Source) in # “Converted to Table”