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.
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) Cant 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 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 isnt available in your product :-(( in which case you will have to look for alternatives Preview file 19 KB
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 dont 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 theres 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
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.
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.
#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 its valid Json file have fun parsing it…
Each record below contains 171 fields. If I remember correctly, I need to create columns for each of the 171 fields
Its 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)
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.
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
JSON (JavaScript Object Notation) has become a ubiquitous data format used widely for transmitting and storing data. Excel provides a convenient way to convert JSON files into spreadsheets for easier analysis and reporting.
In this comprehensive guide, we will walk through the steps to open, import, and convert JSON data files into Excel workbooks.
What is a JSON File?
JSON is an open-standard file format that uses human-readable text to store and transmit data objects made up of attribute–value pairs and array data types.
Here are some key properties of JSON files
- Uses JavaScript syntax with a .json extension
- Data is represented in key/value pairs and ordered lists
- Very lightweight and highly portable between systems
- Integrates easily with most programming languages
- Commonly used as an alternative to XML for web services and APIs
Due to these advantages, JSON has become a standard for transmitting data, especially for web applications.
Why Import JSON into Excel?
Here are some common reasons to import JSON files into Excel:
- Excel provides easy ways to visualize and analyze JSON data
- You can utilize Excel’s functions and tools like PivotTables on the converted dataset
- Importing JSON avoids the need for manual data entry and reformatting
- Excel integration allows combining JSON data with other datasets
- Spreadsheet format enables sharing JSON data with non-technical users
- Excel can export cleaned JSON data back to the original format
Overall, converting JSON to Excel makes the data more accessible for further processing and dashboarding.
Methods to Open JSON Files in Excel
There are a couple of straightforward methods to import JSON files into Excel worksheets:
1. Get & Transform Data in Excel 2016+
The Get & Transform section under the Data tab in Excel 2016 onwards provides an easy way to import JSON data:
-
Go to Data > Get & Transform Data > From JSON
-
Browse and select the JSON file to open
-
In the JSON Import Wizard, select objects levels to import
-
Adjust column headers and data types if required
-
Load the JSON data into a worksheet or create a PivotTable
This gives you a user-friendly wizard interface to parse nested JSON data and load it into Excel.
2. Use the FROMJSON() Function
For Excel 365 users, the FROMJSON() function converts JSON text into an Excel data structure:
-
Paste or import the JSON text into a worksheet cell
-
Use =FROMJSON(A1) where A1 contains the JSON text
-
Expand columns and arrays to view the converted Excel data
-
Load the final output to a worksheet or Power Pivot
The formula approach gives you more control over parsing the JSON during the conversion process.
Importing JSON from Websites
Many websites provide JSON data via public API endpoints. You can import that web data directly into Excel:
-
Copy the API endpoint URL that returns JSON
-
Go to Data > From Web in Excel and paste the URL
-
Adjust query parameters if needed and click Import
-
Convert the loaded JSON output using Get & Transform or FROMJSON()
This allows fetching live JSON data from web services without downloading a file first.
Tips for Converting JSON to Excel
Follow these tips for optimal JSON to Excel conversion:
- Review the JSON structure first in a text editor before importing
- Flatten nested objects into columns for easier analysis
- Adjust column data types like strings, dates, and numbers
- Split object arrays into rows for normalized Excel data
- Handle data inconsistencies using Power Query transformations
- Add helper columns with formulas to parse complex attributes
- Load JSON data into Power Pivot to create a reusable model
Taking these steps will integrate JSON seamlessly into the Excel workflow.
Common Issues with JSON Conversions
Be aware of these common pitfalls when working with JSON in Excel:
- Extremely nested JSON objects lead to messy Excel data
- Arrays get imported as repeating sets of columns
- Dates and numbers show as text values if formats don’t match
- Blank values and nulls can cause errors in calculations
- Workbooks slow down with overloaded arrays in cells
- Formulas like vlookups may fail on converted JSON data
- PivotTables based on JSON data can misbehave if not normalized
Spending time cleaning the JSON first results in a more viable Excel dataset.
Enhancing JSON Data in Excel
Once you import JSON into Excel, consider these enhancements for improved analysis:
- Add columns with formulas to unpack nested attributes
- Split delimited cell values like tags into separate columns
- Concatenate or merge columns to consolidate related attributes
- Apply custom formatting, data types, color coding to columns
- Summarize the data with PivotTables and PivotCharts
- Use Power Pivot to create relationships between JSON objects
- Build dashboards and templates to standardize JSON reporting
Augmenting your JSON data will make it more reusable across projects.
Exporting Excel Data as JSON
After refining JSON data in Excel, you can export it back to the original JSON format:
- Use Get & Transform > To JSON or the TOJSON() formula
- Adjust column order, nesting, and data types
- Set options to export as array or object
- Enable date conversion to ISO-8601 format
- Pretty print JSON output for readability
- Validate exported JSON in an online linter tool
This roundtrip workflow provides a nice integration between JSON and Excel formats.
Key Takeaways
The techniques explored in this guide enable easy integration between JSON and Excel data:
- Import JSON files into Excel using Get & Transform or FROMJSON() formula
- Convert JSON objects into tabular data and arrays into rows
- Load web JSON data by connecting to API endpoints
- Normalize complex JSON into clean Excel tables for usability
- Enhance JSON data in Excel with custom columns, formatting, modeling
- Export refined Excel data back into standard JSON format
Unlocking JSON in Excel expands your ability to leverage web data. By following these best practices, you can integrate JSON smoothly into your Excel analysis workflow.
So next time you need to work with JSON data in Excel, use this guide to open, import, convert, and export JSON seamlessly.
Related Discussions View all
by JSTcanuck99 on October 01, 2022
How do I open a JSON file in Excel 2016?
How do I import a JSON file into Excel?
On the spreadsheet window, in Excel’s ribbon at the top, click the “Data” tab. On the “Data” tab, from the “Get & Transform Data” section, select Get Data > From File > From JSON. You will see your computer’s standard “Import” window. Here, open the folder where your JSON file is located. Double-click the file to connect it to Excel.
How do I parse a JSON file in Excel?
To start parsing a JSON file, open Microsoft Excel on your computer and start a new spreadsheet. On the spreadsheet window, in Excel’s ribbon at the top, click the “Data” tab. On the “Data” tab, from the “Get & Transform Data” section, select Get Data > From File > From JSON. You will see your computer’s standard “Import” window.
How do I open a JSON file in Excel 2016?
Excel 2016 and later versions have native support for opening JSON files. This functionality is available under Get Data > From File> JSON in the Data tab. Are there online tools to convert JSON to Excel format?
How to get data from a JSON file in Excel?
First, open Microsoft Excel, switch to the ‘Data’ tab, and click the ‘Get Data’ button in the ‘Get and Transform Data’ group at the leftmost corner of the ribbon. From the drop-down, expand ‘From File’ and select ‘From JSON’. When you click the ‘From JSON’, you will get a file browser window.
