How to import JSON file to Excel without coding?

Question

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?

Euchre

Answer 1

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.

Euchre

Are you going to create a self-answer for all kinds of questions now? Why not start a blog? – Oswell

You typically do the conversion before the final statement, so the in only contains the finished step, not another action. Look at the JSON example in the documentation – Oswell

@Oswell I already mentioned you need to use one of those 2 last lines starting with in but I changed my answer to make it more clear. – Euchre

You didn't understand what I meant. There should be a step after Source that contains the Table.FromList() command. That is then followed by the 'in'. For example TheTable = Table.FromList(Source, Record.FieldValues) followed by in TheTable. That is the convention. – Oswell

This is nice, but you don't get the column names. Without those the value is minimal. Richard Squire's answer below gets you the column names – Protozoon

Answer 2

Another method is using "Table.FromRecords" as this will populate the column headings for you.

let
    Source = Json.Document(File.Contents("C:\someJsonFileHere.json"))
in
    Table.FromRecords(Source)

Gristly