How to Import Json File to Excel Without Coding?
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"...
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?
1 Answer
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
