How to Build Excel reports using DataDirect Autonomous REST Connector for ODBC

Progress DataDirect Autonomous REST Connector delivers seamless, real-time connectivity between REST data and your ODBC/JDBC tools and applications. Autonomous REST Connector rapidly decreases the amount of time required for applications to understand and consume APIs through its codeless configuration and intelligent sampling. 

With intelligent sampling, Autonomous REST Connector can point to an API and automatically create a configuration file that allows you to start working with the data immediately — no code, no hassle. The configuration file can also be customized to determine how to navigate the various APIs you need to leverage. 

In addition, Autonomous REST Connector supports a wide variety of authentication methods and normalizes the API’s response schema to a SQL-friendly format.  It’s always been easy to create a connector using an Autonomous REST Connector, but we have simplified this even further with the Autonomous REST Connector UI.  The new UI helps you create connectors faster. You don’t need to manually create or edit configurations or worry about the correctness of syntax to configure your API. Just add your configurations in the UI and the UI will take care of the rest. Simply download the configuration file. 

In this blog, we will take you through the steps on how to connect REST API from Excel using an Autonomous REST connector. To connect Microsoft Excel with a REST API using Autonomous REST connector ODBC, you will need to first set up an ODBC data source that points to the API you want to connect to. In this tutorial, we will generate a model and establish a connection with it. 

The Autonomous REST Composer allows you to generate and edit Model files, instead of manually creating a file. The primary purpose of the Model file is to define endpoints and table mapping, but it is also capable of configuring several driver behaviors, such as paging and pushdowns. After generating the Model file, you can share it among multiple installations of the driver. See "Using the Model file method" for an overview of the Model file. 

To generate your model file: 

1. Downloadand install the Progress DataDirect Autonomous REST ODBC driver using the 15-day trial software. Ensure that Microsoft Excel and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Excel installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed and vice-versa.  

2. After installation a shortcut file is created on the desktop. Open the Autonomous REST Composer by selecting the Autonomous REST Composer (ODBC) icon from your desktop or the Windows Start menu. The Autonomous REST Composer opens in your default web browser. 

3. You can create a Model, or you can use the model files. If you want to create a new model go through the detailed documentation on Generating a Model file with the Autonomous REST Composer. In this tutorial, we are using the SpaceX model. Click on the model, you will be redirected to the SpaceX composer. 

4. Go to the “Configure Endpoints” option present on the left side and download it. All the endpoints will be downloaded.  

1. After the DataDirect Autonomous REST ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add”.  

2. Choose the “DataDirect X.X Autonomous REST Connector” and click on “Finish.”  

3. The ODBC Autonomous REST configuration manager will open in the default browser. 

Enter Data Source Name (DSN), and then you need to enter the REST config file. 

4. To get the REST config file, go to downloads and copy the path of the model file downloaded before. Click on properties > security and copy the object name. 

5. Paste the copied path in the REST config file, click on save, and then test connect. 

6. A dialogue box will appear showing the preview of the data. It also shows the connection status. 

When the driver installation and configuration are completed, you can import data from Autonomous REST using MS Excel following the mentioned steps. 

1. Open MS Excel’s new workbook and navigate to the “Data” tab 
 
2. To import data, click on “Get Data” and navigate to “From Other Sources”-> “From ODBC”  

3. Select the Autonomous REST data source name (DSN) you want to connect to and click on “ok”. 

4. A dialogue box will appear asking for a username and password. Go to the Default or custom option on the left side and click on “Connect”. 

5. A navigator will open. Choose the data you want to import and load the data into the workbook.  

6. Once the import of the data is complete the data will be available in the Workbook for you to work on.  

7. Once you have the data in the workbook, you can start analyzing that with various analytical tools available as part of Microsoft excel e.g., the below image shows recommended Pivot table reports that can be built up on the populated data 
 

Get started today with a free 15-day trial of Progress DataDirect Autonomous REST ODBC drivers, and connect Microsoft Excel to all your data!   

Contact Us for assistance with any questions you may have, and we will be happy to help!