Forum Discussion
Why is there no option to import Json into Excel?
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?
I'm using Excel 2016 and the option mentioned before is not in that application
(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
- 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.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 assumptionLooking 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...
- 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"
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-transform-query/ 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.- - 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.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.
