7

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
  • 1,844
  • 2
  • 14
  • 19

2 Answers2

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.

Mostafa Aghajani
  • 1,844
  • 2
  • 14
  • 19
  • Are you going to create a self-answer for all kinds of questions now? Why not start a blog? – teylyn Apr 10 '20 at 02:52
  • 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](https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a) – teylyn Apr 10 '20 at 03:12
  • @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. – Mostafa Aghajani Apr 10 '20 at 15:03
  • 2
    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. – teylyn Apr 11 '20 at 01:21
  • This is nice, but you don't get the column names. Without those the value is minimal. [Richard Squire's answer below](https://stackoverflow.com/a/75684905/6594107) gets you the column names – N R Aug 01 '23 at 21:42
3

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)
Richard Squires
  • 61
  • 5