Import JSON into Excel

Purpose: 

Data accessibility is an improtant facet of the aMES and we aim to increase this accessibility by providing easy access to all of your data via our open API. If you have an Authentise account, you can easily access your data via our API and you are no longer limited to hardcoded, CSV exports. If you are having trouble getting to any of your data or would like guidance with our API, please reach out to support@authentise.com!


This document designed to help our partners visualize their data in Excel by converting our API's JSON output into an Excel table. This should ease the adoption of using our API to gather diverse datasets for your specific data analytics and business informatics needs. We will provide Step-by-step illustrations of the process to support you in importing JSON files from our API into Excel via Power Query. Power Query can also be used to easily import our API's JSON files into datasets for Power BI and similar data visualization tools. 

 

What is a JSON file?

JSON is a file format in Javascript. JSON structures data in an organized and easy-to-read manner. One of the primary uses is transmitting web application data to servers. Our API returns data as requested as JSON files. 

 

Section 1: Accessing an API Endpoint and obtaining a JSON file:

  • Log in to Authentise. 
  • In a separate tab, navigate to the desired API endpoint to request your desired data. For example, our flattened Order data can be found at https://data.authentise.com/flat-order/ 
  • Right-click on the text and click Save as... to save the JSON file to your device. Some browsers may offer a dedicated save button when viewing JSON files in-browser.

mceclip2.png

 

Section 2: Importing JSON files into Excel:

  • Open Microsoft Excel
  • On the ribbon, go to Data
  • Click on Get Data.
  • From the drop-down menu select From File
  • Then select From JSON
  • Select the JSON file that was downloaded in Section 1 and click Import

mceclip1.png

mceclip3.png

Section 3: Adjust Power Query Settings and Create Excel Table from JSON Data

Excel offers a diverse set of data manipulation capabilities via Power Query. This can automate data transformations and is a very powerful tool for data analytics and manipulation. For this guide, we will be using a very basic set of these data manipulation capabilities to get the data into a familar tabular format.

  • After importing the JSON file, the Power Query editor window will open.
  • From the displayed links, click on Resources > List
  • The list of resources will expand. 
  • In the ribbon, click on To Table
  • In the To Table window, keep the default settings (no changes) and click Next

mceclip4.png

 

mceclip5.png

mceclip6.png

  • Expand Column1 by clicking on the Extension icon in the column's header
  • A list of all column headers will display (leave all selected or uncheck the columns that you do not need to customize your data set)
  • Select use original name as prefix (this uses the name for columns as represented in the API/JSON data)
  • Click OK to load the preview table
  • Click Load & Close to upload the data into a table within an Excel worksheet

mceclip7.png

mceclip8.png

mceclip9.png

 

0 Comments

Please sign in to leave a comment.
Powered by Zendesk