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?

Mostafa Aghajani · Accepted Answer · 2020-04-10 15:00:27Z

10

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.

edited Apr 10, 2020 at 15:00

answered Apr 10, 2020 at 1:23

Mostafa Aghajani

2,3982 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

teylyn Over a year ago

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

teylyn Over a year ago

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

Mostafa Aghajani Over a year ago

@teylyn 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.

teylyn Over a year ago

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.

N R Over a year ago

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

Richard Squires · Accepted Answer · 2023-03-09 12:53:44Z

4

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)

answered Mar 9, 2023 at 12:53

Richard Squires

815 bronze badges

Collectives™ on Stack Overflow

How to import JSON file to Excel without coding?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related