ODBC CSV / REST API Driver

ODBC CSV / REST API Driver

ODBC CSV / REST API Driver can be used to read / write data from Web API (Cloud / Internal Data) or Local CSV / TSV files. Easily integrate virtually any Web API inside your BI / ETL / Reporting / Tools or Programming Languages. Write familiar SQL queries to read /write data. This driver supports latest API security standards (i.e. OAuth 2.0, JWT, X509), and optimized for large data files.

This driver is using same high performance data processing engine which was originally developed for CSV / REST Connector in SSIS PowerPack. ODBC PowerPack and SSIS PowerPack, both products share many UI elements and concepts. We wrote many articles to explain various features in one product but concepts are mostly same in both products so hope you can reuse steps explained in different articles even though screenshots /steps may be slightly different.

Feature Summary

  • Read /write CSV data from Web API / REST API or local CSV files using familiar SQL Query language
  • Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, QlikSSRSInformaticaExcel, SSIS)
  • Support for programming languages such as JAVA, C#, Python, PowerShell and more…
  • Tight integration with Microsoft SQL Server (With support for Gateway Option – No need to install Driver on Server)
  • Familiar SQL Query language support including WHERE, ORDER BY, GROUP BY constructs
  • Support for custom math/ string / datetime functions in SQL query Language
  • The unparalleled performance with large file support
  • POST data to API server, Upload File to server (Support MultiPart File Upload).
  • Latest security standards such as OData,  OAuth and JWT (RFC 7519) (Call services like Google API, Facebook …)
  • Call API using Dynamic Token Authenticate or Basic authorization (i.e. UserID / Password)
  • Reading from multiple files using wildcard pattern (e.g. Year??_data*.json)
  • HTTP Request with Custom Headers / Body / URL Parameters.
  • REST API Pagination Support to consume multiple requests / loop (many options explained here and here)
  • HTTPS / SSL / TLS 1.2 Support for more secure communication
  • Support for passing cookies
  • Support for Proxy Server

Download Help File Buy
View All Drivers

Featured Articles

Integration Scenarios (Reporting / ETL / BI / Programming)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn’t appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.

ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

ZappySys Drivers for REST API, JSON, XML – Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Talend, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, JAVA, VB.net, PHP. PowerShell

ETL Tools
Integration

Programming Languages
Integration

ODBC Integration Screenshots in various tools

  • Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
    Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData

SQL Query Examples – CSV Driver


/*--------- Basic Read (API, File, Embedded) Query CSV API Service ---------*/ 
SELECT
  *
FROM
  $ WITH(
    SRC = 'https://zappysys.com/downloads/files/test/invoices.csv',
    ColumnDelimiter = ',',
    HasColumnHeaderRow = 'True' --,DateFormatString='yyyy-MM-dd HH:mm:ss'
    --,FileCompressionType='GZip' --or-- Zip --or-- None
    --,SkipRows='5'
    --,SkipHeaderCommentRows='5'
  )

/*--------- Basic Read (API, File, Embedded) Query Single File ---------*/
SELECT
  *
FROM
  $ WITH (SRC = 'C:\Data\cust-1.csv')

/*--------- Basic Read (API, File, Embedded) Query Multiple Files ---------*/
SELECT
  *
FROM
  $ WITH (
    SRC = 'C:\Data\cust*-?.csv' --,RECURSIVE='True' --Include files from sub folder
  )

/*--------- Basic Read (API, File, Embedded) Query direct CSV string (embedded inside query) ---------*/
SELECT
  *
FROM
  $ WITH (
    DATA = 'Name|Age|Income|Hiredate
Bob|55|5000.00|2018-01-01
Sam|51|2000.00|2016-02-01
John|45|3000.00|2014-07-01
',
    ColumnDelimiter = '|'
  )

/*--------- API Authentication (HTTP,OAuth) Basic Authentication (User Id/Password) ---------*/
--Credentials via Query
SELECT
  *
FROM
  $ WITH (
    SRC = 'https://zappysys.com/downloads/files/test/invoices.csv',
    DataConnectionType = 'HTTP',
    CredentialType = 'Basic',
    UserName = 'user1',
    Password = 'pass123'
  ) --Credentials inside connection string [Use Data source setup grid >> Select Connection = HTTP (Click Configure) >> Set Credential type=Basic, UserId=user1, Password=pass123]
  --SELECT * FROM $ WITH (SRC='https://zappysys.com/downloads/files/test/invoices.csv')

/*--------- API Authentication (HTTP,OAuth) NTLM / Windows Authentication ---------*/
--Credentials via Query
SELECT
  *
FROM
  $ WITH (
    SRC = 'http://yourhost/some-service',
    DataConnectionType = 'HTTP',
    CredentialType = 'Ntlm'
  ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=Ntlm]
  --SELECT * FROM $ WITH (SRC='http://yourhost/some-service')

/*--------- API Authentication (HTTP,OAuth) OAuth 2.0 Authentication (3-Legged) - Custom API Example ---------*/
--Credentials via Query
SELECT
  *
FROM
  $ WITH (
    SRC = 'http://yourhost/api/some-service',
    DataConnectionType = 'OAuth',
    UseCustomApp = 'True',
    OAuthVersion = 'OAuth2',
    AuthUrl = 'https://yourhost/api/authorize',
    TokenUrl = 'https://yourhost/api/token',
    ClientId = 'xxxxxxxxxxx',
    ClientSecret = 'xxxxxxxxxxxxx',
    AccessToken = 'xxxxxxxxxxx',
    RefreshToken = 'xxxxxxxxxxx' --//This is only obtained from UI by clicking Generate Token Button
  ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT
  *
FROM
  $ WITH (SRC = 'http://yourhost/api/some-service')

/*--------- API Authentication (HTTP,OAuth) OAuth 2.0 Authentication (3-Legged) - Google API Example ---------*/
--Credentials via Query
--Check this link to learn more about how to generate ClientId / ClientSecret >> https://zappysys.com/blog/register-google-oauth-application-get-clientid-clientsecret/
--To get Access Token and Refresh Token >> Goto properties grid >> Change Authentication type=OAuth >> Click OAuth Button to configure connection >> Select Provider='Google' >> Use Custom App >> Select Scope and click Generate Token >> Save tokens to file
SELECT
  *
FROM
  $ WITH (
    SRC = 'https://www.googleapis.com/analytics/v3/metadata/ga/columns',
    UseCustomApp = 'True',
    DataConnectionType = 'OAuth',
    ServiceProvider = 'Google',
    ClientId = '3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com',
    ClientSecret = 'qF7axxxxxxxxxxxWfs',
    AccessToken = 'ya29.GlsxxxxxxxxxIam',
    RefreshToken = '1/pFOxxxxxxxxxxgU' --//This is only obtained from UI by clicking Generate Token Button
  ) --Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT
  *
FROM
  $ WITH (
    SRC = 'https://www.googleapis.com/analytics/v3/metadata/ga/columns'
  )

/*--------- API Authentication (HTTP,OAuth) Dynamic Token Authentication (2 Steps) - Query REST API ---------*/
SELECT
  *
FROM
  $ WITH(
    Src = 'https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=csv' --,RequestMethod='POST'
    --,RequestData='sessionid=[$token$]"',
    Header = 'Content-Type: text/plain || Accept: */* || Cache-Control: no-cache',
    DataConnectionType = 'HTTP',
    CredentialType = 'TokenDynamic',
    UserName = 'MyUser001',
    Password = 'P@$$w0rdAAc12',
    AuthScheme = '{none}',
    TokenUrl = 'https://zappysys.com/downloads/files/test/rest-login.aspx',
    TokenRequestData = '{ user:"[$userid$]", password:"[$password$]" }',
    TokenRequestMethod = 'POST',
    TokenResponseContentFilter = '$.sessionid',
    TokenRequestHeaders = 'Content-Type:application/json | Accept:*/* | Cache-Control:no-cache',
    TokenResponseContentType = 'Json'
  )

/*--------- API Pagination Paginate based on URL Parameter (Loop until certain StatusCode) ---------*/
SELECT
  *
FROM
  $ WITH (
    SRC = 'https://zappysys.com/downloads/files/test/page-csv.aspx?page=1&mode=DetectBasedOnResponseStatusCode',
    PagingMode = 'ByUrlParameter',
    PagingByUrlAttributeName = 'page',
    PagingByUrlEndStrategy = 'DetectBasedOnResponseStatusCode',
    PagingByUrlCheckResponseStatusCode = 401,
    IncrementBy = 1
  )

/*--------- API Pagination Paginate based on URL Path (Loop until no record) ---------*/
SELECT
  *
FROM
  $ WITH (
    SRC = 'https://zappysys.com/downloads/files/test/cust-<%page%>.csv',
    PagingMode = 'ByUrlPath',
    Filter = '$.root.element[*]',
    PagingByUrlAttributeName = '<%page%>',
    PagingByUrlEndStrategy = 'DetectBasedOnRecordCount',
    IncrementBy = 1
  )

/*--------- URL JOIN Pass parent URL data to Child URL ---------*/
SELECT 	
  c.custid,
  c.name,
  c.city,
  o.orderid,
  o.orderdate,
  o.total
  /* ,i.itemid,i.qty,i.shipped */
FROM
  $ WITH(
    --1st level (root)--
    src = 'https://zappysys.com/downloads/files/test/join/customers.csv',
    alias = 'c',
    WaitTimeMs = '500' --2nd level--
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] ),
    join1_src = 'https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.csv'<,
    join1_alias = 'o',
    join1_WaitTimeMs = '500'
    /*
        --3rd level-- 
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
    ,join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.csv'
    ,join2_alias='i'
    ,join2_WaitTimeMs='500'

    --maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
    -- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
        */
  )

/*--------- URL JOIN Custom Metadata for Child URL and final output ---------*/
SELECT
  _pid,
  q._rid,
  r.custid as CUST_ID,
  r.city as CUST_CITY,
  q.VAL1,
  q.VAL2,
  q.VAL3,
  q.DESCR
FROM
  $
ORDER BY 	
  _pid,
  q._rid WITH (
    SRC = 'https://zappysys.com/downloads/files/test/join/customers.xml',
    ALIAS = 'r',
    JOIN1_DATA = 'VAL1,VAL2,VAL3,DESCR
ABC123,000100,000100,Some notes1
000100,000200,000200,Some notes2
000200,000300,000300,Some notes3',
    JOIN1_ALIAS = 'q' --metadata for final output (include column, type, length for output of SELECT).
    --This meta does not perform type casting,
    Meta = '[
  {Name: "_rid",Type: "Int32"},
  {Name: "_pid",Type: "Int32"},
 
  {Name: "CUST_ID",Type: "String",Length: 100},
  {Name: "CUST_CITY",Type: "String",Length: 100},
  
  {Name: "VAL1",Type: "String",Length: 100},
  {Name: "VAL2",Type: "Int32"},
  {Name: "VAL3",Type: "String"},
  {Name: "DESCR",Type: "String",Length: 254}
  ]' --child level1 metadata (length not needed). Datatype used below may perform some type casting ---,
    JOIN1_meta = '[
  {Name: "VAL1",Type: "String"},
  {Name: "VAL2",Type: "Int32"},
  {Name: "VAL3",Type: "String"},
  {Name: "DESCR",Type: "String"}
  ]'
  )

/*--------- URL JOIN Download files to local disk ---------*/
select
  a.url,
  b.data,
  file_write_binary(
    base64_to_bytes(b.data),
    'c:\temp\' || name  ) as bytes_written
from $
WITH ( 
  SRC=' https: / / zappysys.com / downloads / files / test / imagelist.xml ',
  FILTER=' $.doc.list [*] ',
  ALIAS=' a ',
  
  JOIN1_SRC=' [$a.url$] ',
  JOIN1_ALIAS=' b ',
  JOIN1_SaveContentAsBinary=' true '  
)

/*--------- URL JOIN Query API with Status Check Loop (Wait until value found) ---------*/
SELECT * FROM [root.element] WITH 
(
SRC='https://zappysys.com/downloads/files/test/cust-1.csv', 
EnableStatusCheck='True', 
StatucCheckMaxWaitSeconds=7,
StatucCheckIterationWaitSeconds=3,
--StatusCheckForFailedValue = 'True',
--StatusFailedValue = 'Failed|Cancelled',
--StatusFieldFilterExpr='-START-(.*)-END-', --Use XPath, JSonPath or Regex to narrow down to single property / content under which you like to search 
--StatusFieldFilterType='Regex', --or use Csv, Xml, Regex, None
StatusSuccessValue='C1' --Wait until C1 or C2 or C3 found in response (Use Regular expression syntax)
)

/*
--- See real world example of status check pattern here (Use of XML Driver)... https://zappysys.com/blog/import-bing-ads-data-sql-server-performance-reports/ 

SELECT c.* FROM $
WITH(
	--///////////////////////////////////
	--Step-1: submit report request for XML format
	--///////////////////////////////////
	 alias='a' 
	,Src='https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc'
	,Filter='$.s:Envelope.s:Body.SubmitGenerateReportResponse'
	,RequestData='@C:\Requests\BingAds\sql_body1.xml'  --Get Body from File for clean Look (Read blog post above to see actual body)
	,IsMultiPart='True'
	,RequestContentTypeCode='TextXml'
	,Header='SOAPAction: "SubmitGenerateReport"'
	,RequestMethod = 'POST'
	,EnableBodyPlaceholderForMultiPart = 'True'
	,Meta = '[{ "Name": "ReportRequestId","Type": "Int64"}]'

    --///////////////////////////////////
    --///Step-2: keep checking status until report is ready - returns final url when done
    --///////////////////////////////////
	,join1_alias = 'b'
	,join1_Filter = '$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus'
	,join1_RequestData = '@C:\Requests\BingAds\sql_body2.xml'
	,join1_IsMultiPart = 'True'
	,join1_Src = 'https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc'
	,join1_RequestContentTypeCode = 'TextXml'
	,join1_Header = 'SOAPAction: "PollGenerateReport"'
	,join1_RequestMethod = 'POST'
	,join1_EnableBodyPlaceholderForMultiPart = 'True'
	,join1_EnableStatusCheck = 'True', 
	,join1_StatucCheckMaxWaitSeconds = 300, --wait max 5 mins ?
	,join1_StatucCheckIterationWaitSeconds = 5, --check every 5 sec ?
	,join1_StatusSuccessValue = 'Success', --look for success word in response
      --, join1_Meta = '[{ "Name": "ReportDownloadUrl","Type": "String",Length: "500"},{ "Name": "Status","Type": "String",Length: "20"}]'

   --///////////////////////////////////
   --Step-3: Download report(Zip file) and Parse rows
   --///////////////////////////////////
	,join2_alias = 'c'
	,join2_DataConnectionType = 'Default'
	,join2_Src = '[$b.ReportDownloadUrl$]'
	,join2_Filter = '$.Report.Table.Row[*]'
	,join2_ElementsToTreatAsArray = 'Row'
	,join2_RequestMethod = 'GET'
	,join2_IsMultiPart = 'False'
	,join2_FileCompressionType = 'Zip'
)

*/

/*--------- Metadata Query with static Metadata (META option / Override Mode) ---------*/
SELECT
  *
FROM
  $ WITH (
    DATA = 'id,checknumber,name
1,0001,AAA
2,0002,BBB
3,0003,CCC' --//Method-1: >>>DIRECT META (compact mode)<<<,
    META = 'id:int;checknumber:string(10);name:string(10)' -- Add: @OverrideMode:1 in the column list to override meta for only specific column(s) - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='@OverrideMode:1;id:int;checknumber:string(10)'  

--//Method-2: >>>DIRECT META (csv mode)<<<
--,META='[{Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10},{Name:"name",Type:"String",Length:10}]'
-- Add: @OverrideMode entry in the column list to override meta for only specific column(s). No need to set Type for that entry - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='[{Name:"@OverrideMode"}, {Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10}]'

--//Method-3: >>>META from File <<<
--,META='c:\data\meta.csv'

--//Method-4: >>>META from Internal Storage - Just refer entry by name <<<
--,META='MY_META_STORAGE_NAME'
  )

/*--------- API POST (Send Data) Simple POST (embedded string) ---------*/
SELECT
  *
FROM
  $
limit
  3 WITH (
    METHOD = 'POST',
    HEADER = 'Content-Type: text/plain || x-hdr1: AAA',
    SRC = 'http://httpbin.org/post',
    BODY = '
	{
    id:1,
    notes:"Line1\r\nLine2"
}'
)

/*--------- API POST (Send Data) Simple POST with Static Output Metadata ---------*/
SELECT
  *
FROM
  $ WITH (
    METHOD = 'POST',
    HEADER = 'Content-Type: application/xml || x-hdr1: SomeValue',
    SRC = 'https://zappysys.com/downloads/files/test/rest-getdata.aspx?postsame=1',
    BODY ='
col1,col2
1,AAA
2,BBB',
    Filter = '$.root.row[*]' --define output metadata using static value like below or from file,
    META = '[{Name: "id",Type: "Int32"},{Name: "name",Type: "String",Length: 10}]' --,META='id=Int32;name=String(10)'  --compact meta format
	--,META='c:\query_meta.csv'
  )


/*--------- API POST (Send Data) File Upload data from local file ---------*/
SELECT
  *
FROM
  $
limit
  3 WITH (
    METHOD = 'POST',
    HEADER = 'Content-Type:text/plain||x-hdr1:AAA',
    SRC = 'http://httpbin.org/post',
    BODY = '@c:\files\dump.csv',
    IsMultiPart = 'True'
  )

REST / SOAP API Call Settings

Our drivers come with easy to use API query builder with many advanced options to connect to virtually any JSON, XML or CSV based data format from local files or API URL.

ZappySys ODBC Driver UI - setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

ZappySys Driver UI – setting to read from File or API URL. Read from JSON REST API / XML SOAP Web Service / CSV Format.

SQL Query Editor / Live Preview / Examples

Our drivers come with easy to use Query editor so you can test any API

ODBC Driver - SQL Query Editor / Live Preview

ODBC Driver – SQL Query Editor / Live Preview

Security Standards (OAuth, JWT, Basic Auth, X509)

Our ODBC Drivers tested with wide variety of security standards such as OAuth 1.0, OAuth 2.0, Basic Auth, JWT, X509 Certificate. We also support latest encryption standards for secure http (such as SSL/TLS 1.2)

Here is an example of our easy to use OAuth Connection Manager which can be used to connect to any API with OAuth 1.0 or OAuth 2.0 Standards.

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp...)

OAuth Connection for API Providers (Such as Facebook, Google, Twitter, Linkedin, Amazon, Hubspot, MailChimp…)

Step-By-Step Video Tutorial (6 Parts Video Series)

Here is full length tutorial if you like to get started with ZappySys Drivers. Below tutorial shows usecase of REST API integration in Excel and Power BI. It covers many topics such as below. We used JSON Driver as an example but many concepts mentioned in below tutorial can be applicable to all other ZappySys drivers.

  • Configure ODBC DSN
  • Making HTTP GET Calls
  • Authenticating REST API requests
  • Pagination in REST API
  • Building simple and advanced SQL Queries
  • POST data to REST API
  • Excel Example – Import REST API in Excel
  • Power BI Example – Import REST API in Power BI