Have you ever had trouble exporting data from one system to another? I thought the process would be a breeze, except the source file type was JSON, not a CSV file. It’s not my favorite file format, especially since I didn’t need all of it. In this tutorial, I’ll show how to use Microsoft Excel and Power Query to convert the file.
Knowledge You’ll Gain:
- Learn how to convert JSON files to Excel spreadsheets.
- Become proficient at using Microsoft Excel’s Power Query Editor.
- Understand the structure and common uses of JSON data files.
- Develop troubleshooting skills for handling common JSON conversion issues.
What is a JSON File?
A JSON file is a text-based data format used for exchanging information between web applications, with the abbreviation standing for JavaScript Object Notation. It is commonly used for data exchange between various online systems. Programmers like it because it is lightweight and easy to parse.
In short, JSON files are a handy way for computers to store information that is easier for people to read and work with, even if you don’t know how to write Javascript code. You can use many different software programs to open and work with JSON files, like Excel or Power Query.
Examples of JSON files
Here are some examples of how JSON files can be used:
- Storing and transmitting data: JSON files often store and transmit data between different systems. For example, a website might use a JSON file to send data from a server to a web browser.
- Integrating different systems: JSON files can integrate different systems and applications. For example, a company might use a JSON file to transfer data between two software programs requiring communication.
- Building APIs: JSON files are often used to build Application Programming Interfaces (APIs), allowing different software systems to communicate. For example, a social media website might use a JSON file to allow third-party apps to access their system and pull data, such as user information or posts.
- Storing configuration data: JSON files can store application configuration data, such as settings or preferences. For example, browser extensions use JSON files for configuration information.
- Logging data: JSON files can be used to log data for analysis or debugging purposes. For example, Google’s Takeout service uses a JSON file to store your Chrome history data.
You may see the file contents laid out differently based on the application. For example, here is part of a WordPress theme file. In this example, you tend to read down the file.
In contrast, the file below was exported from a SQL table. Notice how the list of records are in more of a table format. Also, some descriptive lines have been added to the top.
Review Your JSON File
JSON files are used in many different ways. In my case, I needed to convert the file contents of a very large JSON file and convert it to a CSV file. I was migrating from one service to another, and they had completely different data models.
When importing a JSON file to Excel, it helps to review it first. This can help you avoid errors and ensure all the data is included correctly. I prefer to look at my file in a code editor like Microsft’s VS CODE, which is free. It also can auto-identify JSON files.
Here are a few tips for reviewing your file:
- Check the file format: Make sure your file is in JSON format. These files will have { } and [ ].
- Look for field labels: This gives you an idea of what type of data is included. This will help you determine what information you’ll import into Excel.
- Look at the file layout: Does the file look like it’s laid out in rows and columns, similar to how data is organized in Excel? Or is the layout more vertical with indentations?
- Try to see how many records might be included: This can help you determine if the whole file was converted.
How to Import JSON Files to Excel
In my example, I’m using Microsoft 365, so my screens may differ. You just need to be using Microsoft Excel 2016 or a newer version to import a JSON file.
- Open a blank workbook in Microsoft Excel and click the “Data” tab.
- Click the Get Data button.
- From the side menu, select From File and then From JSON.
- Find your JSON file from the Import Data dialog box and click Open.
- Click the Import button. The Power Query Editor opens.
Excel Power Query Editor
Excel’s Power Query Editor is the tool that does all the heavy lifting. It allows us to transform the original file into another form. It also keeps track of our steps. The editor opens on top of your current workbook. Like worksheets, the editor also has tabs and buttons [1]. The tabs are conditional and change based on your task.
As we progress, we’ll see records and data in the middle area [2]. To the right, in the Properties area [3], is our source file name. Below that is Applied Steps. This area will change based on our actions. What’s nice about this section is that we can delete steps if the data doesn’t look correct.
Selecting the Columns to Import
- Click the Into Table button [1] in the Convert group.
- Click the tiny icon with opposing arrows on the far column. Also, note how a Converted to Table step shows in Applied Steps.
- Scroll through the list of column names and select the ones to import. There is also a textbox at the top you can use to search for a specific column.
- If you don’t see a column name, click the Load more link.
- Tick the checkbox Use original column name as prefix (optional).
- Click OK.
After you click OK, you will see the columns you selected and their names. To the right, you will see a new step, Expanded Value. If, after loading the columns into the Query Editor, you decided you didn’t want the original column name, you could click the X to the left of the step name. It will delete that step, and you can make your changes.
Or, if you realized you forgot a column or didn’t want one, you could click the starburst icon to the right. That will open a dialog showing all the columns again.
Removing Extra Data
You may have extra rows you don’t need depending on your file. In my screen snap below, I’ve highlighted a row that mainly has Null values for column values. If you click the row, the cell entries will show in the bottom half of your screen.
You could delete this row after you load the data or before. If you wish to delete beforehand, click the Remove Rows button in the Reduce Rows group. This will present more options:
- Remove Top Rows
- Remove Bottom Rows
- Remove Alternate Rows
- Remove Duplicates
- Remove Blank Rows
- Remove Errors
If I select Remove Bottom Rows, I’ll get a dialog box asking how many rows I wish to remove. I’ll enter 1 and click OK.
After editing your JSON data, click the Close & Load button in the Power Query Editor to import the data to a new Excel table. You can save your data from here as an Excel or CSV file.
Tips & Troubleshooting
I wish I could tell you that importing my JSON files to Microsoft Excel was easy. I ran into multiple issues, which I’ll outline below.
- The file is too large – My first attempt to convert a JSON file to an Excel file encountered a size issue. My source file was 3MB. It was too much for Power Query and had too many columns and rows.
- Incorrect file format – If you’re having trouble importing your JSON file, it may be because you’re using the wrong file format. Make sure that your JSON file is saved in the correct format and that it doesn’t have any errors or inconsistencies. Again, this is where Microsoft VS Code can help.
- Invalid column header – If your JSON file has an invalid column header, Excel may not be able to import it correctly. If you look at the JSON example above, which is more in a tabular format, you’ll see there are 3 “type” lines. While Excel imported the file, it only processed those three lines, not the items underneath. My fix was to edit the file and remove the extra lines.
- Query editor – If you’re having trouble with the Power Query Editor, you may need to update your Excel version.
- Table Layout – The file I used here contained 35 columns and 1 row. With this layout, I prefer to transpose my columns and rows. However, you need to convert the table to a range, transpose it, and then convert it back to a table.
Can Audio Improves Performance?
Microsoft Excel Alternatives
There are alternatives available that can help you with your data needs. As I mentioned, in my first attempt the file was too big. However, I found gigasheet.com. It’s a web-based tool that allows you to import and edit data from various sources. You can also collaborate with your team members in real-time. The free plan worked for me, and I found the interface quite good.
Another alternative is data.page. It’s also a web-based tool that stores transforms, and analyzes data. You can easily import data from various sources and create charts and graphs to visualize your data.