How to connect Excel with REST API using ODBC

[daniel]

Introduction

The article provides steps to integrate REST API data into MS Excel without coding using ODBC.

Here’s a summary of the steps outlined in the article:

Steps

Create a new Driver

1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
   

2. If you want access for yourself or other users, go to the User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the “Add” button.
   

3. From the driver list, select ZappySys JSON Driver, ZappySys XMLDriver, ZappySys CSV Driver, or another driver to get information from a REST API service.
   

JSON Driver

1. Select an Url; here is the example we will use for ZappySys JSON Driver:
   https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

2. Test the connection and then enter JSONPath expression in the Array Filter textbox to extract only specific parts of the JSON file as below ($.value[*])
   

   

3. Once you configured a data source, you can preview data. Hit the Preview tab, and use similar settings to preview data. Then press OK to save the configuration.
   

   

XML Driver

1. In the upper section, we check how to make the JSON API call using JSON Driver and parse the JSON string response. Same way if your API is XML/SOAP. Select an Url; here is the example we will use for ZappySys XML Driver:
   https://services.odata.org/V3/Northwind/Northwind.svc/Customers

2. Test the connection and then enter Path expression in the Array Filter textbox to extract only the specific parts of the XML file as below ($.feed.entry[*])
   

   

3. You can preview data Once you have configured a data source. Hit the Preview tab, and use similar settings to preview data. Then press OK to save the configuration.
   

   

CSV Driver

1. In the upper section, we check how to make the XML/Soap API call using XML Driver and parse the XML string response. It is the same if your API is CSV Type or you want to parse the CSV file data. Select an Url; here is the example we will use for ZappySys CSV Driver:
   https://zappysys.com/downloads/files/test/cust-1.csv.zip

2. Test the connection and select ZIP as a file compression format
   

   

3. You can preview data Once you have configured a data source. Hit the Preview tab, and use similar settings to preview data. Then press OK to save the configuration.
   

   

Read data in Excel from the DSN

1. In Excel click Data, then select Get Data, proceed with From Other Sources and choose From ODBC item. This will get data from the ODBC data source we created:
   

   

2. A small window opens, then select the data source you created in previous steps:
   

   

3. You will most likely be asked to authenticate to a newly created DSN. Just select the Windows authentication option together with the Use my current credentials option:
   

   

4. You will be asked to select a table or view to get data. Select one and load the data!
   

   

5. Finally, use data extracted from REST API API in an Excel worksheet:
   

   

Conclusion

.
The article provides steps to guide users through connecting Excel with REST API services using ODBC. If you encounter any issues or need further assistance, feel free to contact our support team via chat on our website or through email at support@zappysys.com.

Refences

Connect Excel with REST API using ODBC