Forum Discussion
Why is there no option to import Json into Excel?
Lorenzo There is nothing like "Blank Query" under New Query.
I have tried the "From Text" item. But this launches the CSV text import wizard.
I also tried the "From Web" item in the "From Other Sources". When I specify the Json file instead of a URL, it opens the Json file in Visual Studio.
Don't try text/csv or anything else like that you waste your time
From your screenshot the Blank Query option is probably in the From Other Sources sub-option
- I have opened the Power Query Editor from the "From Other Sources". I don't see any Json options in the "New Source".
I am looking at the article at https://learn.microsoft.com/en-us/power-query/connectors/json.
Again, this article references a "Get Data" option that does not exist in this version of Power Query.PLEASE save your and my time following below steps:
#1 Restart from scratch (close the Power Query Editor discarding all) to come back to Excel
#2 Go to Data tab > From Other Sources > Blank Query (the Power Query Editor opens & auto. creates a new query)
#3 In the Power Query Editor formula bar enter exactly (Power Query is case-sentive), replacing X:\PathToYourFile\YourJsonFile.xyz with your file path:
= Json.Document(File.Contents("X:\PathToYourFile\YourJsonFile.xyz"))then Validate. If the Path to your file is ok and it's valid Json file have fun parsing it...
Some pointers just in case:
- https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/
- https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-query/
Thanks for all the help. To keep the complexity and additional support requirements at a minimum, we are writing a .NET application using EPPlus and Newtonsoft.
Since this is something our users would be doing on a regular basis, we feel that a simple application would save a great deal of time and effort and not require end-users (who are not power users) to learn Power Query and the M language. The support requirement for Power Query is a non-starter.
If I use:
= Json.Document(File.Contents("file:///C:/Projects/Workspace/Json/Roster.json"))
as my source, it loads the following. Each record below contains 171 fields. If I remember correctly, I need to create columns for each of the 171 fields.
However, all options on the "Add Column" tab are disabled.
Each record below contains 171 fields. If I remember correctly, I need to create columns for each of the 171 fields
It's all about parsing a Json. Can be easy or not. It depends on the Json and what you expect to get out of it. Have a look to the pointers in my previous reply
And if you need help parsing it please create a new discussion/thread, sharing your Json and the expected result
This discussion/thread was about getting access to a Json file with Excel 2016 where the option is obviously not available in the Excel User Interface - topic closed from my perspective
Hope this all makes sense & Thanks in advance to Mark the solution to help others who Search (topics marked appear first when you Search)