How to import JSON file to Excel without coding?
If you have PowerQuery in Excel (I think 2010+) then it's very simple and straightforward. Similar scenarios can be used to cover more complicated cases too. Just follow these steps:
- On the ribbon bar, choose:
Data => Get Data => From File => From Json - Select your Json file (input.json in this example)
- In the opened
Power Query Editorwindow and on the ribbon bar, choose:View => Advanced Editorand input:
let Source = Json.Document(File.Contents("input.json"))in Table.FromList(Source, Record.FieldValues, {"name","price","rate"})or if you want auto-import without specifying column names, use the following block instead:
let Source = Json.Document(File.Contents("input.json"))in Table.FromList(Source, Record.FieldValues) Now on the ribbon bar choose:Home => Close & Load
and you will see a beautiful imported table with all Excel functionality you like.