Why is there no option to import Json into Excel?

[Martin Kimzey]

I am using Microsoft Excel 2016.  (16.0.5400.1000).

My Data Ribbon has no options to open a Json file. Much of the documentation for 2016 looks nothing like my Data Ribbon.

Data Ribbon in Excel 2016

There is no "Get Data" button. JSON is not in any of the options listed. I checked the customization options for the Data Ribbon. Nothing is there for JSON.

Reading some of the articles, Excel supported this at one time.  The documentation is wrong and creates confusion.

Is there any documentation for importing Json into this version of Excel 2016?

[#2]

@Martin Kimzey 

I put your question to the Bing chatbot. Here is its answer:

There is an option to import JSON data into Excel 2016. You can do this by opening Excel and going to the `Data` tab. From there, navigate to `Get Data > From File > From JSON`. You can then select how you want your data to be imported and Excel will handle the rest automatically ¹. Is there anything else you would like to know?

Quelle: Unterhaltung mit Bing, 19.6.2023(1) Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get .... https://syntaxbytetutorials.com/import-json-data-in-excel-2016-or-2019-or-office-365-using-a-get-tra... Zugegriffen 19.6.2023.
(2) Can't import JSON in Excel 2016 using "Get & Transform" feature. https://superuser.com/questions/1267768/cant-import-json-in-excel-2016-using-get-transform-feature Zugegriffen 19.6.2023.
(3) How to Convert a JSON File to Microsoft Excel - How-To Geek. https://www.howtogeek.com/775651/how-to-convert-a-json-file-to-microsoft-excel/ Zugegriffen 19.6.2023.
(4) How to Convert JSON to Excel Using Power Query. https://excelunlocked.com/convert-json-to-excel Zugegriffen 19.6.2023.

[#3]

- If you look at the Data ribbon in the original post, there is no "Get Data" button at all on the tab.

- There is no "Get Data". There is no "From File". There is no "From JSON".

- Additionally, there are no options in Customize Ribbon to add "Get Data" or anything Json related to Excel 2016 (Build 16.0.5400.1000).

- The links cited above do not match this version of Excel 2016.

-There is nothing Json related under the "From Other Sources".

- If I try the "From Web" approach - Excel launches Visual Studio and opens the Json file as a large text string.

- If I try to use "From Text" if launches the Text Import Wizard which does not recognize a Json File.

- The documentation for the Data tab above does not match my version of Excel 2016.

AI Bots are only as good as the information they are working with. If this documentation is wrong, the answer with be wrong.

[#4]

@Martin Kimzey 

I'm sorry that the tips weren't helpful.

From what I read the workarounds should have worked.

I can't check it myself because I use Office 365.

I guess you have to wait for someone who still uses Excel 2016.

[#5]

@Martin Kimzey 

(I don't run 2016). Please download the attached file and Refresh the query as documented

If you get "You CAN import Json Documents with the in used product" I can help to get you started assuming you can share 2016 screenshots

If you get "You CANNOT import Json Documents with the in used product" this means PQ function Json.Document isn't available in your product :-(( in which case you will have to look for alternatives

[#6]

The version of Excel 2016 that I am using is stamped with Office 365. On my Account page it tells me "Product Activated Microsoft Office Standard 2016"

[#7]

My product is Microsoft Office Standard 2016 Build 16.0.5400.1000 32-bit, Product Id: 00340-93334-98401-AA044.

- When I open the "PQ_IsJsonDocumentAvailable.xlsx", I get the ""You CAN import Json Documents with the in used product"

- Json is not mentioned anywhere on the Data Tab or Ribbon bar commands that are not shown.

- I have a Query Tab in my spreadsheet - but every item is grayed out (so I cannot start a Power Query session in my new or existing Excel files).

- They are enabled in your "PQIsJasonDocumentAvailable.xlsx". How did you enable the Query tab items? The Unhide button on the View tab is grayed out.

- There is no "Get Data" item on my Data tab. This is referenced in the documentation to launch a new query.

- I think I am going to create an application using NewtonSoft and EPPlus. This would probably be quicker and more straightforward.

[#8]

@Martin Kimzey 

I have a Query Tab in my spreadsheet - but every item is grayed out...They are enabled in your "PQIsJasonDocumentAvailable.xlsx". How did you enable the Query tab items?
Not sure at all as I don't see what you see but your Query tab (and its options) is probably only available (not grayed out) when at least one query exist in your workbook. The fact that the Query tab is active with the file I shared seems to confirm this assumption

Looking at the initial picture you posted, in the Data tab there's a New Query option:

If you natigate in the sub-options you should find something like Blank Query. If you find this select that option and the Power Query Editor will be lauched to create a new query from scratch

From there I can get your started...

[#9]

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

[#10]

@Martin Kimzey 

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

[#11]

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.

[#12]

@Martin Kimzey 

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.

[#13]

@Martin Kimzey 

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:

• Automatically expand all fields from a JSON document in Power BI and Power Query
• How to open a complex JSON record in Power BI and Power Query

[#14]

@Martin Kimzey 

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)

[#15]

@Lorenzo 

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.

[#16]

Hi @Martin Kimzey 

Glad I could help a bit & Thanks for posting back

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

Totally makes sense to me

All the best...