Jun 19 2023 12:44 PM
Jun 19 2023 12:44 PM
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.
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?
Jun 19 2023 01:43 PM
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.
Jun 21 2023 04:01 AM
Jun 21 2023 04:30 AM
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.
Jun 21 2023 05:44 AM
(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
Jun 22 2023 02:55 AM
Jun 22 2023 03:23 AM
Jun 22 2023 03:47 AM
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...
Jun 22 2023 04:00 AM
@L z. 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.
Jun 22 2023 04:39 AM
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
Jun 22 2023 05:18 AM
Jun 22 2023 05:41 AM
If I use:
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.
Jun 22 2023 05:46 AM
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:
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:
Jun 22 2023 05:56 AM
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)
Jun 25 2023 05:32 AM
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.
Jun 25 2023 09:51 AM
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...