Introduction
PreviousNext

Introduction

JSON ODBC driver can read data from local files or Web API (REST, OData) using familiar SQL language. Driver allows to query nested structure and output as flat table.

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.

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

Configure 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 JSON(In this case its from Zip) file using ZappySys JSON 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 JSON Driver].
    ZappySys ODBC Driver - Create JSON Driver
  4. When you see DSN Config Editor with zappysys logo first thing you need to do is change default DSN Name at the top. For this example call it [ZS-ODATA-Customers]. Enter following URL in the Data Source and In the Compression (Zip/Gzip) tab select suitable file compression format.
    https://zappysys.com/downloads/files/test/cust-1.json.zip
    ZappySys ODBC Driver - Configure JSON Driver
  5. Click on Preview Tab, Select Table from Tables Dropdown and select [value] and click Preview.
    ZappySys ODBC Driver - Preview Data
  6. 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.
  7. If you are using DSN method then Click OK to save our DSN Configuration.
  8. Now, lets load data into Excel Sheet Using ODBC DSN.
  9. Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
    ZappySys JSON ODBC Driver : Load Data Into MS-Excel - Select ODBC Source
  10. Now, select DSN we have created, you can write SQL Statement if you want, otherwise leave it blank for through select Table.
    ZappySys JSON ODBC Driver : Load Data Into MS-Excel - Select DSN
  11. Click on OK button.
  12. Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
    ZappySys ODBC Driver - Create Credentials
  13. Now, Select Table you want to load data into Microsoft Excel.
    ZappySys JSON ODBC Driver : Load Data Into MS-Excel - Select Table
  14. Select New Worksheet for the data should be loaded.
    ZappySys JSON ODBC Driver : Load Data Into MS-Excel - Select Table
  15. Click on Load button to load data.
  16. You can verify your data.
    ZappySys JSON 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 [JSON File / REST API Driver] category
How to Export REST API to MS Access using VBA Command Button

How to Export REST API to MS Access using VBA Command Button

Introduction In our previous blog we saw how to export REST API to CSV using c# or Python. In this post we will look at specific example on How to Export REST API to MS Access using VBA Command Button, along with few other topics such as how to make REST API Call, how to […]


Read WordPress data using SSIS REST API Call

Read WordPress data using SSIS REST API Call

Introduction In this post you will learn how to Read WordPress data using SSIS REST API Call (Drag and drop approach without any coding).  We will use SSIS JSON/ REST API Source to extract data from WordPress API. In this blog we will see how to get all WordPress Posts, Pages and Categories using JSON Source and […]


Read data from QuickBooks Online into SQL Server via ODBC Driver

Read data from QuickBooks Online into SQL Server via ODBC Driver

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using ODBC in few clicks. We will use ODBC JSON / REST API Driver to read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth […]


Read / Write REST API data in Talend (JSON / XML / SOAP)

Read / Write REST API data in Talend (JSON / XML / SOAP)

Introduction In this post we will learn how to read / write REST API data in Talend Open Studio. We will create a simple Talend Job using ZappySys JSON  Driver to read from REST API / JSON Files and load into Target (e.g. File / DB). Techniques listed in this article can be also used to read […]


Insert, Update and Delete JIRA Issue in SQL Server

Insert, Update and Delete JIRA Issue in SQL Server

Introduction In our previous blog we saw how to read JIRA data in SQL Server. In this blog, we will learn how to Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other […]


Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Read JIRA data in SSRS Reports (SQL Server Reporting Services)

Introduction In our previous blog we saw how to read rest api in SSRS reports. We recommend you go through that article first. In this post we will look at specific example on How to read JIRA data in SSRS Reports (SQL Server Reporting Services), along with few other topics such as how to generate an […]


How to Import JIRA data in Power BI

How to Import JIRA data in Power BI

Introduction In our previous blog we saw how to import rest api in Power BI. We recommend you go through that article first. In this article, we will learn how to Import JIRA data in Power BI. For that, we will create a successful HTML Connection then request for reading JIRA data and loading into Power […]


Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Get Office 365 data in Power BI using Microsoft Graph API and ODBC

Introduction In this article, we will get Office 365  data in Power BI using Microsoft Graph API and ODBC drivers. Specifically, we will get Excel file data in OneDrive, events from a Calendar and finally, a list in a note in OneNote and then load that data into a Power BI report. We will achieve […]


Push data into a Power BI dataset from SQL Server

Push data into a Power BI dataset from SQL Server

Introduction In this article, you will learn how to push data into a Power BI dataset from SQL Server. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Among its dataset types, it has a streaming dataset into which […]


Load JIRA data into SQL Server with ODBC – Call REST API

Load JIRA data into SQL Server with ODBC – Call REST API

Introduction In this blog, we will learn How to Load JIRA data in ODBC and load JIRA data into SQL Server Table, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call, how to read all Issues data from JIRA with ODBC. We will go through […]



Copyrights reserved. ZappySys LLC.