Introduction
PreviousNext

Introduction

API driver can read data from many popular online services (see full list here) using familiar SQL language. Driver allows to query nested structure and output as flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.

Content

Video Tutorial

Step-By-Step

After you install ZappySys ODBC Drivers you can use Driver functionality few ways (see below).
  1. Using ODBC DSN (User level DSN or System level DSN)
  2. Using Direct ODBC Connection String
  3. Connect client app/server from Windows, Linux, Mac OS to ZappySys Data Gateway using Microsoft SQL Server Driver (i.e. JDBC, ODBC, OLEDB ...)
Regardless which method you choose from your App below steps will help you to configure your connectivity in correct way.
If you choose to connect using direct ConnectionString then also try below Steps to create correct ConnectionString which you can copy from DSN Config Tool and paste inside your App / Programming Language Code.

Configure ODBC DSN (User level DSN or System level DSN)

In this section you will learn how to Configure ODBC DSN. Later on this DSN can be used to use ZappySys Driver. ODBC DSN can be stored at Machine Level (for all users) or Current User Level. In this case we are going to load data into Microsoft Excel from OData API using ZappySys API ODBC Driver.
  1. Type odbcad32.exe in your search box and launch the DSN Config utility.
  2. If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
    ZappySys ODBC Driver - Open UI
  3. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys API Driver].
    ZappySys ODBC Driver - Create API Driver
  4. Now, When you see DSN Config Editor with zappysys logo first thing you need to do is change default DSN Name at the top and Configure API Driver.
  5. API driver may show you wizard for the first time like below. You can choose any of those 4 methods but for this example we will choose Method#1.

    Method#1 Choose from Embedded Connector List


    ODBC API Driver - Choose API Connector from List

    --OR -- Method#2 Search Online


    ODBC API Driver - Search API Connector Online

    --OR -- Method#3 Choose from File (Custom Connector File)


    --OR -- Method#4 Supply Config XML for Connector

  6. Select [OData] from the API Providers dropdown and click [Continue]
  7. Click on Test Connection to verify.
    Note : For this example we will use inbuilt example OData service without any credentials but in real world you can choose correct Authentication Type and enter your own OData Service URL and use HTTP / OAuth credentials.
    
    ODBC API Driver - Create Connection
  8. Click on Preview Tab, Select Table from Tables Dropdown or you can enter or modify a SQL query and click on Preview Data.
    This example shows how to write simple SQL query (Structured Query Language). It uses WHERE clause. For more SQL Queries click here.
    SELECT 
     "CustomerID",
     "CompanyName",
     "ContactName",
     "ContactTitle",
     "Address",
     "City",
     "Region" 
     FROM "Customers"
    
    ZappySys API ODBC Driver - Select Table and Preview Data
  9. If you are using Direct ConnectionString method in other tool/code then just Click [Copy ConnectionString] button and hit cancel so we don't create DSN.
  10. If you are using DSN method then Click OK to save our DSN Configuration.

Build SQL using Query Builder

You can create custom SQL with user defined parameters by clicking on Query Builder like below.
ZappySys API Driver - Query Builder - Generate SQL

Load API Driver data into Excel

  1. Now, lets load data into Excel Sheet Using ODBC DSN.
  2. Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
    ZappySys ODBC Driver : Load Data Into MS-Excel - Select ODBC Source
  3. Now, select DSN we have created, you can write SQL Statement like below if you want, otherwise leave it blank for through select Table.
    SELECT 
     "CustomerID",
     "CompanyName",
     "ContactName",
     "ContactTitle",
     "Address",
     "City",
     "Region" 
     FROM "Customers"
    

    ZappySys ODBC Driver : Load Data Into MS-Excel - Select DSN
  4. Click on OK button.
  5. Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
    ZappySys ODBC Driver - Create Credentials
  6. Now, Select Table you want to load data into Microsoft Excel.
    ZappySys ODBC Driver : Load Data Into MS-Excel - Select Table
  7. Select New Worksheet for the data should be loaded.
    ZappySys ODBC Driver : Load Data Into MS-Excel - Select Table
  8. Click on Load button to load data.
  9. You can verify your data.
    ZappySys ODBC Driver : Load Data Into MS-Excel - Success

Access Driver inside SQL Server (T-SQL Code)

Click here for steps to create, Loading data inside SQL Server (T-SQL).

Access Driver inside Other Tools / Programming Languages

Click here for App Integration (ETL, Reporting, Analytics).

Click here Programming Languages.

Articles

Click here to see all articles for [CSV File / REST API Driver] category
How to import Amazon MWS data into SQL Server (T-SQL)

How to import Amazon MWS data into SQL Server (T-SQL)

Introduction In our previous blog post we saw how to import rest API in SQL Server. Using same concepts lets look at how to import Amazon MWS Data into SQL Server. We will explore many techniques to call Amazon MWS API and learn how to automate Amazon MWS data extraction without doing any ETL. We […]



Copyrights reserved. ZappySys LLC.