How to import JSON file to Excel without coding?

user_1867908 0
Viewed 9357

Let's say you have a file named input.json which contains an array of objects defined in standard JSON format. Something like:

[
    {"name": "notebook", "price": 500.00, "rate": 4.2},
    {"name": "sd-card", "price": 60.49, "rate": 3.5}
]

How can I import it as a table in Microsoft Excel without VBA or scripting?

powerquery excel json
1 Answers

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 Editor window and on the ribbon bar, choose: View => Advanced Editor and 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.

user_18679080
Related