How to connect to REST API in Excel using ODBC

ODBC PowerPack JSON Driver
, ,
1

Introduction

Connecting Excel to Web Services using ODBC opens powerful integration possibilities, allowing you to pull data from REST APIs, cloud platforms, or other HTTP-based services directly into Excel. This enables real-time reporting, dashboards, and analysis without manual exports or third-party scripts.

In this tutorial, you’ll learn how to connect Microsoft Excel to a Web API using ODBC, leveraging the ZappySys ODBC PowerPack to make RESTful API calls and retrieve data in tabular format.

Prerequisites

  • Download and install the ZappySys ODBC PowerPack
  • Microsoft Excel (2016 or later recommended)
  • Access to a public or private REST API (e.g., JSON-based web service)

Steps

Step 1: Create a new ODBC Driver

  1. Download and install the ZappySys ODBC PowerPack.

  2. Open ODBC Data Sources (64-bit) from the Start menu.

    Open ODBC Data Source
    Open ODBC Data Source342×436 61.8 KB

  3. Under the User DSN tab, click Add and select ZappySys JSON Driver. You can use other drivers like XML, CSV and more depending the REST API documentation you are using.

    Create new User DSN for ZappySys JSON Driver
    Create new User DSN for ZappySys JSON Driver604×515 62 KB

  4. In the connection configuration window, choose URL or File, and enter the following URL for this example:

    https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

    Use the filter:

    $.value[*]

    Alternatively, you can load an existing connection string as explained in this article.

    Configure JSON Driver
    Configure JSON Driver802×699 74 KB

  5. Click Test Connection, then open the Preview tab to view the API response.

  6. Select the desired table or dataset and verify the data preview.

    REST API result
    REST API result804×704 53.5 KB

  7. Click OK to save the DSN configuration.

Step 2: Read fata in Excel from the DSN

  1. Open Excel, go to the Data tab, then select Get Data > From Other Sources > From ODBC.

    Get Data from ODBC in Excel
    Get Data from ODBC in Excel512×830 49.5 KB

  2. In the dialog box, choose the data source name (DSN) you created earlier.

    Select DSN in Excel
    Select DSN in Excel700×221 4.15 KB

  3. If prompted for authentication, choose Windows and select Use my current credentials.

    Excel DSN Authentication
    Excel DSN Authentication701×370 23.1 KB

  4. Select a table or view from the list and click Load to import the data.

    Select Table in Excel
    Select Table in Excel880×700 33.9 KB

  5. Your data from the REST API is now available in an Excel worksheet.

    Excel with API data loaded
    Excel with API data loaded1339×693 81.1 KB

Conclusion

Using ODBC in Excel to connect to REST API allows for dynamic and scalable integration with cloud-based APIs. With tools like the ZappySys ODBC PowerPack, you can connect to REST API endpoints, like JSON or XML, and bring that data directly into Excel with no coding required.

Visit our official page to discover more connectors, powerful automation features, and real-time data integration options. Start building smarter, faster, and more scalable solutions today with ZappySys ODBC PowerPack.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.