The following post reviews the top 5 ways to open a JSON file. Instead of using a JSON viewer, we review hot to open a .json file with Notepad++, Excel, Gigasheet, VS Code, and a text editor so you can open, view, and edit JSON format files.. For more information on opening large JSON files continue reading or use the table of contents to skip to specific sections.
Table of Contents
- The top 5 ways to Open JSON files
- What does JSON stand for and what are JSON files?
- What do JSON files look like?
- Why is it difficult to work with JSON data files?
- Conclusion
The Top 5 ways to Open JSON Files
Notepad++
Notepad++ is a free downloadable text editor that can easily open big JSON files and display the content in plain text format and make edits. The plain text based format can be difficult to read because columns aren't always aligned. It is also difficult to make column level edits and transformations unless you know a programming language. Notepad++ is free to download and is easily installed. Use the following steps to open your JSON file:
- Download Notedpad++
- Once installed on a computer, open the Notepad++ program and select File>Open.
- In the open menu, navigate to the location on your computer where the JSON file is saved. Select and click 'Open' to open your large JSON file.
- Once the JSON file is open you can scroll through the data and make simple revisions.
Excel
Excel is the most popular data analysis tool and nearly every business user has Excel on their computer. Excel is great at a lot of things and Microsoft has been building new capabilities for the past 40 years. Open JSON files in Excel is not straight forward but can be done with some Excel's power features. The benefit to opening a JSON file with Excel is the program attempts to put your data in rows and columns, making it easier to evaluate. The downside is if you open a big JSON file, it may cause Excel to freeze or crash. To open a JSON file in Excel, follow the steps below. For more detailed instructions, see our blog called How to open a JSON file in Excel.
1. Import data
Open an Excel file and click on Data in the top menu. Then select the Get Data. From the options in the drop down, select From File > From JSON. Then select your file and hit import.
2. Power Query Editor
Excel will then open the file using the Power Query Editor, which is a feature that creates a database out of the dataset. Excel will initially show you a view of your file like the image below. Don't panic, there are some additional steps needed before opening the file.
3. Into Table
Select Into Table in the upper left-hand corner. Then click on the double arrow icon at the top of the second column and select Expand to new rows.
4. Deselect 'use original column name as prefix'
Then click the same double arrow again and deselect the 'use original column name as prefix.' Then click ok. Repeat these steps until you no longer have columns that say 'List'. When you are done, click close and load in the upper left-hand corner.
Gigasheet
Gigasheet is hosted software application that lets you upload/import, view, edit, transform and analyze large data sets, hence the name 'Gigasheet.' The product loads data into a database and provides a UI that interacts with the database to let users edit and manipulate the dataset. With Gigasheet, datasets can be imported from a number of different cloud repositories and the applications provides pre-built data transformation features. If you need to open a big JSON file, Gigasheet is a good option. Simply sign up and click "+ New" in the upper left-hand corner. Then select "upload file." If your JSON resides in a cloud repository, you can select one of those import options as well. Gigasheet will
Gigasheet has a free tier that lets users import and preview 1 billion row data sets but only edit the first 1000 rows. To edit anything beyond the first 1000 rows users must upgrade to the paid tier for $95/month
Visual Studio
Microsoft Visual Studio is a free comprehensive integrated development environment (IDE) used for developing applications across various platforms, including Windows, web, and mobile. To open a JSON file with Visual Studio, first download visual studio, then navigate to the JSON file on your computer, right click, and select 'open with Visual Studio Code.' Visual Studio offers several advantages for working with JSON files, such as syntax highlighting, auto-completion, and schema validation, making it easier to read and edit complex JSON structures. However, some drawbacks include its relatively large memory footprint and resource consumption, which might be overkill for simple JSON editing tasks. Visual Studio would not be a good option for opening a large JSON file. Additionally, while Visual Studio provides robust features, users seeking lightweight and quick edits may find VS Code to be overly complex and may prefer simpler text editors.
Text Editor
For a quick and simple option to view a JSON file you can also use the standard text editor that comes on every Windows and PC computer. Simply open the program (notepad on Windows and TextEdit on a Mac), navigate to your JSON file and open it. The JSON file will open showing the raw contents. Text editors are simple programs and you won't be able to transform or structure the data.
What does JSON stand for and what are JSON files?
JSON stands for JavaScript Object Notation, which is a data format based on a portion of the JavaScript programming language. JSON files are compatible with all programming languages and as a lightweight way to format and store data. JSON files end in the .json
file extension are often used when transporting data within applications, which is why the JSON format is common when data is exported from software applications. The JSON format is great for storing hierarchical data, with multiple layers of categories and subcategories. This often leads to JSON files storing large amounts of data. You can read more about JSON format at JSON.org
What do JSON files look like?
JSON files all end in the .json file extension. Data in JSON files is written as name/value pairs in the following format: "data name":"data"
. An example might be "dogName":"Rover"
. The JSON format typically contains many of these pairs for the following data types:
- Strings:
{"name":"Brett"}
- Numbers:
{"weight":"178"}
- Objects:
{"player":{"name":"Brett", "age":"41", "school":"West Virginia"}}
- Arrays:
{"players":["Mark", 41, "Brett"]}
- Booleans:
{"active":true}
- Null values:
{"injuries":null}
JSON files also typically store ordered lists of values containing all the key/value pairs. An example of a JSON file with fantasy football data is included here. As you can see, the JSON format isn't too complicated but has distinct differences from file types like CSV and Parquet.
[{"player_id": 1809, "name": "Robert Blackmon", "position": "DB", "height" : "6-0", "weight": "208", "current_team": null, "birth_date": "1967-05-12" , "birth_place": "Bay City, TX", "death_date": null, "college": "Baylor", "high_school": "Van Vleck, TX", "draft_team": "Seattle Seahawks", "draft_round": "2", "draft_position": "34", "draft_year": "1990", "current_salary": null, "hof_induction_year": null}, {"player_id": 23586, "name": "Dean Wells", "position": "LB", "height": "6-3", "weight": "248", "current_team": null, "birth_date": "1970-07-20", "birth_place": "Louisville, KY", "death_date": null, "college": "Kentucky", "high_school": "Holy Cross, KY", "draft_team": "Seattle Seahawks", "draft_round": "4", "draft_position": "85", "draft_year": "1993", "current_salary": null, "hof_induction_year": null}, {"player_id": 355, "name": "Kiko Alonso", "position": "ILB", "height": "6-3", "weight": "238", "current_team": "Miami Dolphins", "birth_date": "1990-08-14", "birth_place": "Newton, MA", "death_date": null, "college": "Oregon", "high_school": "Los Gatos, CA", "draft_team": "Buffalo Bills", "draft_round": "2", "draft_position": "46", "draft_year": "2013", "current_salary": "1,075,000" , "hof_induction_year": null},
Why is it difficult to work with JSON files?
There are two reasons JSON data are hard to work with, the format and the file size.
File Format
JSON files themselves are easy to work with when writing JavaScript but when a human exports data from a software application and saves a .json
file on their computer, it's often difficult to analyze. JSON files often store hierarchical data, which doesn't easily map to a spreadsheet type viewer. When opened in a raw format, like a text editor, the JSON format is not as easily human readable as CSVs and don't align data into columns, like we are used to seeing in spreadsheets. When it comes to spreadsheets, Excel and Google Sheets don't easily open .json
files, making it difficult for folks without programming experience to open, analyze, or manipulate JSON files. If you are one of these people, continue reading our instructions to open JSON files in Excel, Gigasheet, VS Code, Notepad++ and a text editor.
File Size
Due to JSON data files being produced by software applications, they can often grow quite large, which prevents popular spreadsheets from opening them. Excel has a 1,048,576 row limit and Google Sheets has a 10,000,000 cell limit. Because both applications use the compute power on your laptop, they are not able to open big JSON files and will often freeze or crash when trying to do so. This is also true for other applications, like Visual Studio (covered below).
Conclusion
Opening JSON files is not straight forward. There are even fewer options for opening big JSON files than there are for CSVs. For this reason, it is important to select the right tool for your needs. If you have a large JSON file, Notepad++ or Gigasheet are your best options. If you need to clearly see all the data in your file in a structured format and your JSON file isn't too big, Excel is a good choice. Lastly, if you simply want a quick view of the content in the JSON data, a text editor or Microsoft Visual Studio are excellent choices. JSON files contain hierarchical fromatted information that can be very valuable so make sure to choose the best option to open your file.