How to import JSON to Excel (Load File, REST API, SOAP XML)

Posted on by
Contents hide
1 Introduction
2 Requirements
3 Video Tutorial
4 An introduction to Rest API and OData
5 Configure ODBC DSN for ZappySys JSON Driver
6 How to import REST API data to Excel
7 How to import data from REST API to CSV
8 How to import JSON to Excel (From Local file)
9 How to query JSON or REST API
10 Schedule / Automate Excel file creation using command line
11 Calling XML SOAP Web Service in Excel
11.1 What is SOAP Web Service?
11.2 What is WSDL
11.3 Example SQL Query for SOAP API call using ZappySys XML Driver
11.4 Video Tutorial - Introduction to SOAP Web Service and SoapUI tool
11.5 Using SoapUI to test SOAP API call / Create Request Body XML
11.6 Create DSN using ZappySys XML Driver to call SOAP API
11.7 Preview SOAP API Response / Generate SQL Code for SOAP API Call
11.8 Preview Data
11.9 Generate Code Option
12 SOAP / REST API pagination in Excel
12.1 Paginate by Response Attribute
12.2 Paginate by URL Parameter (Loop until certain StatusCode)
12.3 Paginate by URL Path (Loop until no record)
12.4 Paginate by Header Link (RFC 5988)
13 SOAP / REST API Error Handling in Excel
13.1 METHOD 1 - Using Error Handling Options
13.1.1 When to use?
13.1.2 Scenario 1
13.1.3 Scenario 2
13.2 METHOD 2 - Using Connection [Retry Settings]
14 Security / Connection Types (Options for HTTP, OAuth, SOAP) in Excel
14.1 HTTP Connection
14.2 OAuth
14.3 API Limit / Throttling
14.4 2D Array Transformation
15 Troubleshooting Errors
16 Conclusion
17 References
17.1 More from ZappySys Blog

Introduction

json to excel

In this post, we will learn how to import JSON to Excel.  We will use ODBC PowerPack to connect and query a JSON file. This article also covers creating Excel from SOAP XML Web Service so read full article to learn about SOAP API Export.

JSON stands for Java Script Object Notation and it is an Open and Standard format to read object with attributes and values.  JSON is replacing XML because it is faster to read data, it is easier to parse data, it does not require end tags, it is shorter and it can use arrays.

We will load the data using the ZappySys JSON driver and then upload the data in Excel.

Requirements

This article assumes following requirements are met before you can follow the steps listed in this article.

  1. Make sure that Microsoft Excel installed
  2. Also, the driver ODBC PowerPack installed

Video Tutorial

Let’s take a look to this step by step tutorial.

An introduction to Rest API and OData

In this example, we will use OData (Open Data Protocol) to consume REST API. REST API (Representational State Transfer Application Program Interface) allows to handle the interoperability betwee computers and internet.

In REST API we can handle web services in different formats. In this example, we will work with the Northwind example. The Northwind example is available in this URL:

  1. By default the data is displayed in XML format. To show the data in JSON use this URL:

  2. There are collections of data like Categories, CustomerDemographic, Customers, etc. For example the following URL will show the data of the categories collection:

  3. In the next steps, we will use ZappySys drivers to connect to this URL and query using OData.

 

Configure ODBC DSN for ZappySys JSON Driver

ODBC driver can be accessed in two modes.

  1. Using DSN
  2. Without DSN (Supply direct Connection String e.g. DRIVER={ZappySys JSON Driver}; …….. )

In this article, we will use DSN approach (User DSN). We will first add the ZappySys JSON Driver in the ODBC Data source Administrator.

Follow these steps to accomplish the task:

  1. First, Windows search, write ODBC and select the ODBC Data sources (32 bits)
    Open ODBC Data source

    Open ODBC Data Source

  2. As a second step, in ODBC Data source Administrator press the Add button.
    Add ZappySys

    Add ZappySys

     

  3. In this step, create new data source, select ZappySys ODBC Driver.
    add new zappysys

    Add new zappysys json driver

  4. Here we have several properties, write a data source name. In this example, the name will be ZappySys JSON to Excel.
  5. The Data Source (URL or file path) can specify the URL of the source or if it is a local file, you can specify the local path. In this example, the URL is:
    You can also specify local file path as Data Source

    For single file:  c:\data\myfile_1.json
    For multiple files: c:\data\myfile_*.json

  6. Expand Other settings and in Data Format, select OData. Press OK.

    OData properties json driver

How to import REST API data to Excel

  1. In Excel, go to Data Ribbon and select From Other Sources and From Microsoft Query.
    Open Excel and create queries

    Excel Data Ribbon

  2. In Choose Data Source, select ZappySys JSON for Excel and press OK.
    Select zappysys data source

    Select zappysys data source

  3. Click Value and press the > to display all the attributes and press next.
    Create json query in excel

    Create json query in excel

  4. You can filter data and select columns and check if a columns is equal to, greater than or less than a specific value. In this example, we will not apply filters. Press next.
    filter json data in excel

    filter json data in excel

  5. Now if you want to export data to sheet without using custom Query then select first option. If you like to enter custom query then select  Microsoft Query (second option) once you close the wizard you may get an option to enter custom SQL (see the toolbar of Graphical Designer)We will use first option for now.
    Return data in Excel

    Return data in Excel

  6. In this step, Excel will show the Import Data window. Excel will let you select the sheet to insert the data. We will choose the existing sheet.
    select excel sheet

    Choose excel sheet

  7. If everything is OK, you will be able to see the data.
    json data extracted

    Excel rows with data

 

How to import data from REST API to CSV

If you already have your data in Excel with the previous steps, converting the data extracted from Rest API to Excel to a CSV is a straight forward process.

  1. Using Excel, go to the File menú and select Save as and select a folder to store it.
    Save as in Excel

    Save as in Excel

  2. In order to save the data in CSV, save as type select CSV (Comma delimited)
    save file as csv

    convert excel to csv

How to import JSON to Excel (From Local file)

  1. To import data in Excel, the steps are the same for Rest API that for a physical json file. The only difference is that a local path is used instead of a URL. In order to get JSON data into Excel, you need to repeat the steps to add the ZappySys ODBC Driver in the ODBC Data source, but instead of specifying a URL, we just need to specify the local path.
  2. To test the driver, let’s say that we have the following named sample.json file:
  3. Finally, in the ZappySys driver, in Data Source (URL or File Path), write the path of your json file. In this example, c:\sql\sample.json. Also expand Data format and select Default.
    properties json file

    properties to query a local json file

    NOTE: When the Data format is set to OData and it is a local json file, the error message is “Data processing error: URI formats are not supported”. If the Data format is set to Original, the error message is “Query execution error: Requested value ‘Original’ was not found.” To solve this issue, make sure that in Other Settings, the Data format is set to Default.

How to query JSON or REST API

Our ZappySys driver is a very intuitive tool and you can write queries to data like a simple database table.

Let’s take a look to some examples:

In the ODBC Data Source Administrator, press Configure.

Configure ZappySys settings

Configure ZappySys settings

 

On Preview page, write this query:

The query will show the customers where the city is Berlin:

Query json with where clause

Query json information using filters

 

You can use in the where clause with double quotes:

or simple quotes:

It is also valid to comment code. The following example shows how to comment a single line (the where clause):

The following example shows how to comment multiple lines

Schedule / Automate Excel file creation using command line

There will be a time when you need to export REST API to Excel in an automated way (Scheduled Task). Possibly via Batch file, Python code, PowerShell script or Scheduler. Check this article to learn more.

You can use ZappyShell for DB (Command Line Tool) to export JSON / SOAP / REST API data to Excel Sheet. It supports any ODBC Connection string as a source and you can export to CSV, Excel, JSON and XML.

Here is a sample command line. You can automate it via Scheduled Job (e.g. Windows Scheduler or SQL Agent Job)

First create below file (name it script.txt)

Then call this (assuming you have zappyshell in c:\zappyshell folder

You can also use DSN less connection string such as below

Export JSON / SOAP XML / REST API to Excel File - Create Excel File using Automated Command Line (Schedule)

Export JSON / SOAP XML / REST API to Excel File – Create Excel File using Automated Command Line (Schedule)

 

Calling XML SOAP Web Service in Excel

So far we have looked at examples to consume data using JSON driver. Now let’s look at an example, to call XML SOAP Web Service in Excel.

To call SOAP API you need to know Request XML Body Structure. If you are not sure how to create SOAP Request body then no worries. Check this article to learn how to generate SOAP Request body using the Free tool SoapUI. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.

What is SOAP Web Service?

If you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept about SOAP Web service standard from this link There are two important aspects in SOAP Web service.
  1. Getting WSDL file or URL
  2. Knowing exact Web Service URL

What is WSDL

In very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then analyze or sometimes you may get direct URL (e.g. http://api.mycompany.com/hr-soap-service/?wsdl )

Example SQL Query for SOAP API call using ZappySys XML Driver

Here is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding. 
SELECT * FROM $
WITH(
	 Src='http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx'
	,DataConnectionType='HTTP'
	,CredentialType='Basic' --OR SoapWss
	,SoapWssPasswordType='PasswordText'
	,UserName='myuser'
	,Password='pass$$w123'
	,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'
	,ElementsToTreatAsArray='HolidayCode'	
	,RequestMethod='POST'	
	,Header='Content-Type: text/xml;charset=UTF-8 || SOAPAction: "http://www.holidaywebservice.com/HolidayService_v2/GetHolidaysAvailable"'
	,RequestData='
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:hol="http://www.holidaywebservice.com/HolidayService_v2/">
   <soapenv:Header/>
   <soapenv:Body>
      <hol:GetHolidaysAvailable>
         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->
         <hol:countryCode>UnitedStates</hol:countryCode>
      </hol:GetHolidaysAvailable>
   </soapenv:Body>
</soapenv:Envelope>'
)
Now let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)

Video Tutorial - Introduction to SOAP Web Service and SoapUI tool

Before we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.  

Using SoapUI to test SOAP API call / Create Request Body XML

Assuming you have downloaded and installed SoapUI from here, now we are ready to use WSDL for your SOAP Web Service Calls. If you do not have WSDL file or URL handy then contact your API provider (sometimes you just have to add ?wsdl at the end of your Service URL to get WSDL so try that. Example: http://mycompany/myservice?wsdl ). If you don't know what is WSDL then in short, WSDL is Web service Description Language (i.e. XML file which describes your SOAP Service). WSDL helps to craft SOAP API request Body for ZappySys XML Driver. So Let's get started.
  1. Open SoapUI and click SOAP button to create new SOAP Project
  2. Enter WSDL URL or File Path of WSDLFor example WSDL for our sample service can be accessed via this URL 
    http://www.dneonline.com/calculator.asmx?wsdl
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
    Create new SOAP API Project in SoapUI tool for SOAP API Testing
  3. Once WSDL is loaded you will see possible operations you can call for your SOAP Web Service.
  4. If your web service requires credentials then you have to configure it. There are two common credential types for public services (SOAP WSS or BASIC )
    1. To use SOAP WSS Credentials select request node and enter UserId, Password, and WSS-PasswordType (PasswordText or PasswordHash)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
      Configure SOAP WSS Credentials for SoapUI (SOAP API Testing Tool)
    2. To use BASIC Auth Credentials select request node and double-click it. At the bottom click on Auth (Basic) and From Authorization dropdown click Add New and Select Basic.Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
      Configure Basic Authorization for SoapUI (SOAP API Testing Tool)
  5. Now you can test your request first Double-click on the request node to open request editor.
  6. Change necessary parameters, remove optional or unwanted parameters. If you want to regenerate request you can click on Recreate default request toolbar icon. Create SOAP Request XML (With Optional Parameters)
    Create SOAP Request XML (With Optional Parameters)
  7. Once your SOAP Request XML is ready, Click the Play button in the toolbar to execute SOAP API Request and Response will appear in Right side panel. Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response) Test SOAP API using SoapUI Tool (Change Default XML Body / Parameters, Execute and See Response)

Create DSN using ZappySys XML Driver to call SOAP API

Once you have tested your SOAP API in SoapUI tool, we are ready to use ZappySys XML driver to call SOAP API in your preferred BI tool or Programming language.
  1. First open ODBC Data Sources (search ODBC in your start menu or go under ZappySys > ODBC PowerPack > ODBC 64 bit)
  2. Goto System DSN Tab (or User DSN which is not used by Service account)
  3. Click Add and Select ZappySys XML Driver ZappySys ODBC Driver for XML / SOAP API ZappySys ODBC Driver for XML / SOAP API
  4. Configure API URL, Request Method and Request Body as below ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body ZappySys XML Driver - Calling SOAP API - Configure URL, Method, Body
  5. (This step is Optional) If your SOAP API requires credentials then Select Connection Type to HTTP and configure as below. ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
    ZappySys XML Driver - Configure SOAP WSS Credentials or Basic Authorization (Userid, Password)
  6. Configure-Request Headers as below (You can get it from Request > Raw tab from SoapUI after you test the request by clicking the Play button) Configure SOAP API Request Headers - ZappySys XML Driver Configure SOAP API Request Headers - ZappySys XML Driver
  7. Once credentials entered you can select Filter to extract data from the desired node. Make sure to select array node (see special icon) or select the node which contains all necessary columns if you don't have array node. Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy) Select Filter - Extract data from nested XML / SOAP API Response (Denormalize Hierarchy)
  8. If prompted select yes to treat selected node as Array (This is helpful when you expect one or more record for selected node) Treat selected node as XML Array Option for SOAP API Response XML Treat selected node as XML Array Option for SOAP API Response XML

Preview SOAP API Response / Generate SQL Code for SOAP API Call

Once you configure settings for XML Driver now you can preview data or generate example code for desired language (e.g. C#, Python, Java, SQL Server). Go to Preview tab and you will see default query generated based on settings you entered in previous sections. Attributes listed in WITH clause are optional. If you omit attribute in WITH clause it will use it from Properties tab.

Preview Data

Preview SOAP API Response in ZappySys XML Driver Preview SOAP API Response in ZappySys XML Driver

Generate Code Option

Generate Example Code for ZappySys Driver
Generate Example Code for ZappySys Driver

SOAP / REST API pagination in Excel

Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that ZappySys ODBC Driver includes many options to cover virtually any pagination method.
Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):

Paginate by Response Attribute

This example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page. 
SELECT * FROM $
WITH(
SRC=@'https://zappysys.com/downloads/files/test/pagination_nextlink_inarray_1.json'
,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']
)

Paginate by URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found). 
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

Paginate by URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found). 
SELECT * FROM $
WITH (
SRC=@'https://zappysys.com/downloads/files/test/cust-<%page%>.xml'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

Paginate by Header Link (RFC 5988)

API like GitHub / Wordpress use Next link in Headers (RFC 5988) 
SELECT * FROM $
LIMIT 25
WITH(
	 Src='https://wordpress.org/news/wp-json/wp/v2/categories?per_page=10'
	,PagingMode='ByResponseHeaderRfc5988'
	,WaitTimeMs='200' --//wait 200 ms after each request
)
 

SOAP / REST API Error Handling in Excel

Sometimes errors occur... they just do and there is nothing you can do! Or can you? Actually, in ODBC PowerPack you can handle them in two ways.

METHOD 1 - Using Error Handling Options

When to use?

You may want to use them when your source is a resource located on the Internet; e.g. a file on a website, a file on an FTP server or just a plain API HTTP response. By default, when a remote server returns an error, data retrieval is stopped, an error is raised and no data is given back to you. This might not be always desirable.

Scenario 1

Imagine a scenario, that there is a web server which each day at 12 AM releases a new JSON file with that day's date as filename, e.g. http://www.some-server.com/data/2018-06-20.json. And, of course, you want to download it and use it daily in your Power BI report. But you have a problem: Power BI report data sources are refreshed each hour and you may get HTTP 404 status code (no file was found) when a file is not released yet. Which consequentially means other data sources won't be updated as well and you will see old and cached data on the report. That's where you could use Continue on any error or Continue when Url is invalid or missing (404 Errors) to avoid an error being raised and let other data sources to be updated.

Scenario 2

Another scenario is when you expect a web server to raise some kind of HTTP error when accessing a URL. You don't want ODBC Data Source to raise an error but instead, you want to get response data. That's where you can use Continue on any error or alike together with  Get response data on error to continue on an error and get the data:

METHOD 2 - Using Connection [Retry Settings]

Another scenario you may run into is a buggy web server. You ask it to give you some file or data and it, like a snotty kid, just doesn't give it to you! You have to ask twice or thrice before it does its job. If that's the case, you have to retry HTTP requests using Connection:

Security / Connection Types (Options for HTTP, OAuth, SOAP) in Excel

If you need to authenticate or authorize your user to access a web resource, you will need to use one of the Connections:
  • HTTP
  • OAuth
ZappySys XML Driver - HTTP and OAuth Connection Types

HTTP Connection

Use HTTP Connection for simple Windows, Basic, NTLM or Kerberos authentication. Just fill in a username and a password and you are good to go!
You can also use HTTP Connection for more sophisticated authentication like:
  • SOAP WSS (when accessing a SOAP WebService)
  • Static Token / API Key (when need to pass an API key in HTTP header)
  • Dynamic Token (same as Static Token method except that each time you need to log in and retrieve a fresh API key)
  • JWT Token (As per RFC 7519)

OAuth

If you are trying to access REST API resource, it is a huge chance, you will need to use OAuth Connection. Read this article to understand how OAuth authentication and authorization works and how to use it (article originally was written for SSIS PowerPack, but the concepts and UI stay the same): https://zappysys.com/blog/rest-api-authentication-with-oauth-2-0-using-ssis
There are few settings you can coder while calling Web API

API Limit / Throttling

While calling public API or other external web services one important aspect you have to check,  how many requests are allowed by your API. Especially when you use API pagination options to pull many records you have to slow down based on API limits. For example, your API may allow you only 5 requests per second. Use Throttling Tab on Driver UI to set delay after each request.

2D Array Transformation

If you are using JSON or XML API Driver then possible you may have to transform your data using 2D array transformation feature. Check this link for more information.  

Troubleshooting Errors

While running in Access\Excel\other and reading data from DSN created with ODBC PowerPack, if you get this error "License type [ODBC_PP_TRIAL] not found or its expired"

Please refer to this article for the same:  Troubleshooting "License type [ODBC_PP_TRIAL] not found or its expired" error in Microsoft Access

Conclusion

In this article, we learned how to use the ZappySys ODBC PowerPack to import JSON to Excel. We used the OData protocol and then we learned how to import from a JSON file to Excel.  With ZappySys ODBC Power Pack, you can query REST API information or JSON files using SQL and filter the information or write custom queries according to your needs.

References

Keywords: How to import JSON to Excel (Load File or REST API), how to convert JSON to Excel, Import JSON to Excel 2016, Load JSON to Excel 2013, Import REST API to Excel 2010

More from ZappySys Blog

Posted in JSON File / REST API Driver, ODBC PowerPack, Reporting - Microsoft Excel and tagged , , , , , , , .