Do you need to import data from an online published JSON file or even load information from the REST API for reporting in Excel? With the right approach and tools, you can do this without any hassle and even automate the workflow. Let’s explore different methods to import JSON data to Excel so you can turn data into valuable insights.
Options to import JSON to Excel
Basically, there are three methods to export data from JSON API into Excel, each offering its own advantages:
- Coupler.io: With this reporting automation platform, you can import JSON to Excel, automate the data flow on a schedule, transform and filter your data on the go, and blend it with information from other sources. It also supports other destinations, for example, if you want to import JSON to Google Sheets.
- Power Query: You can import JSON data stored in your local machine or query the JSON URL using Excel’s built-in tool.
- VBA (Visual Basic for Applications): ??This coding option is well-suited for Excel users with a strong technical background. With VBA, you can write custom scripts to import JSON to Excel.
Let’s explore each option in detail using this online JSON file as a data sample.
How to import JSON to Excel and automate the flow with Coupler.io
It only takes three steps to set up a JSON integration with Excel using Coupler.io. We’ve already preselected JSON as the source application and Microsoft Excel as the destination application, so click Proceed. You’ll be offered to create a Coupler.io account for free. After that, configure the connections.
1. Extract data
- Insert the URL to your JSON file in the JSON URL field.
- For the HTTP method, select GET.
- If your file is password-protected, you will need to authorize it by providing your API key in the Request headers.
- Additionally, you can also make use of the other optional settings – for instance, select the specific fields to be imported, specify the number of rows to skip, etc.
If those settings are not required, click Finish and proceed to go to the next steps.
2. Transform data
In the Transform data section, you can preview your data and rearrange it if needed – hide or rename columns, add columns, filter and sort the data, calculate custom metrics directly, and so on.
- Once everything looks good, click Proceed in the upper right corner to continue.
3. Load data and schedule refresh
- Connect to your Microsoft account, and grant the requested permissions. Then select a workbook and a worksheet where the data will be loaded. You can select an existing Excel worksheet or create a new one.
If you’re using Excel as an intermediary to load data to Power BI, check out the direct JSON to Power BI data flow.
- Enable the Automatic data refresh and customize the schedule for your data imports. That’s it!
How to import JSON to Excel with Power Query
Power Query is a data transformation and connectivity tool available in Excel. It allows you to shape, clean, and transform your data from various sources, including JSON files, databases, web pages, and more. However, the main drawback of PowerQuery is that it’s only available for the Excel desktop app. Within Power Query, there are typically two methods to import JSON into Excel.
How to import a JSON file to Excel using Power Query
Using the From JSON option, you can import data from JSON files stored in your local machine to Excel. To do this:
- Navigate to the Data tab.
- Click on Get Data.
- From the drop-down menu, choose From File and then select From JSON.
- This will open the import data dialog box. In the dialog box, locate the JSON file you want to import and click on Import.
- Excel will open a Power Query Editor window. Here, you will preview your data and decide how JSON’s data will be loaded into your spreadsheet
- In the Query Editor, you can click on the Transform option and then select To Table to convert the JSON data into a table format. This conversion allows you to perform various transformations on the data before loading it into your Excel workbook.
- You might notice that the values are currently hidden in the Query Editor. This is because you need to select which columns you want to display. To do this, click on the Expand button, which will allow you to choose the specific columns you want to include and make their values visible in the editor.
- At this point, you have the flexibility to modify your data. For this example, we deselected the Use column name as prefix option to remove the default column name prefix and add your custom column names. This gives you control over how your data’s named. Additionally, you can select columns that you want to see in the table. After making these selections, click OK.
- Now you can observe that your data has been properly loaded into rows and columns in the Query Editor. To proceed with loading this transformed data into Excel sheets, Click on Close & Load.
Here is your JSON data after it has been imported.
How to import data from JSON URL using Power Query
To import data from a JSON URL using Power Query, you will need to provide the URL of the online JSON file you wish to import and follow these steps:
- Navigate to the Data tab.
- Click on Get Data and select From Other Sources.
- Choose the option labeled From Web.
- Enter the JSON URL.
- Click on OK to import the JSON data.
- Choose the web content access. For this example, we will go for Anonymous. Click Connect.
- The Power Query editor will open, allowing you to preview the data loaded from the JSON URL.
From here, follow steps 6-9 in the above section as they are the same.
Importing JSON data to Excel using VBA
VBA (Visual Basic for Applications) is a programming language integrated into Excel that allows you to automate tasks, create custom functions, and enhance Excel’s functionality through code. With VBA you have the ability to create macros that can automate tasks or handle operations.
When it comes to importing data from JSON files to Excel using VBA you usually need to write custom code that interacts with the JSON file and processes the data. The advantage of using code is that it can be tailored to suit your requirements. If you are familiar with VBA you can customize the code according to your needs. However, if you are new to VBA it may require some time and effort to grasp the basics. Don’t worry though we have prepared a guide that simplifies and explains the fundamentals of using Excel VBA.
Choosing the best method to import JSON data to Excel
JSON (JavaScript Object Notation) is a popular data format that is widely used for storing and exchanging data between web applications. On the other hand, Excel is a powerful spreadsheet tool that is used for data manipulation and analysis.
The best choice to import data from JSON to Excel depends on your requirements and skillset. If you prefer a user interface and don’t have programming experience Power Query is an excellent option. On the other hand, if you need automated and scheduled imports without coding knowledge, Coupler.io is a go-to solution. It offers a no-code approach where you can easily import JSON data and other types of data into Excel.
For those who’re proficient, in Excel and have programming skills then VBA is a good option because it allows you to write your own code and automate various tasks within Excel. Out of all the methods we’ve discussed the no-code approach is generally favored by many, including ourselves. It offers advantages, such as a selection of pre-built integrations and the ability to import data into other applications like Google Sheets, BigQuery, etc. You can give Coupler.io a try for free.
Automate data export to Excel with Coupler.io
Get started for free