![]() ODBC Excel DriverExcel ODBC driver can read/write data in Microsoft Excel files (*.xls or *.xlsx) and output as a flat table. This driver is using same high performance data processing engine which was originally developed for Excel File Source 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
|
||||
|
View All Drivers | |||
Featured Articles
|
Configure Excel Driver
Open ODBC Data Sources by searching in the start menu
Create a new ODBC DSN
Choose Excel Driver from the list of Drivers
Set excel file path in Path property & set appropriate DSN Name
We can configure DSN in various ways
-
— Using Direct Query —
Click on the Preview Tab, Select Table from the Tables Dropdown and select [value] or change SELECT query as per the requirement and click on the Preview Data button.
-
— Using Stored Procedure —
Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.
Click on the Custom Objects Tab, Click on Add button and select Add Procedure and Enter an appropriate name and Click on OK button to create.
Now Stored Procedure can be created with or without parameters (see example below). If you use parameters then Set default value otherwise it may fail to compilation)
-
— Without Parameters —
-
— With Parameters —
Note : Here you can use Placeholder with Paramters in Stored Procedure. Example : SELECT * FROM $ WHERE OrderID = '<@OrderID, FUN_TRIM>' or CustId = '<@CustId>' and Total >= '<@Total>'
-
-
— Using Virtual Table —
Note : For this you have to Save ODBC Driver configuration and then again reopen to configure same driver. For more information click here.
ZappySys APi Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body.
This way you don’t have to create multiple Data Sources if you like to read data from multiple EndPoints.
However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful.
You can create many Virtual Tables on the same Data Source (e.g. If you have 50 Folders with slight variations you can create virtual tables with just URL as Parameter setting).vt__Customers Path=c:\data\customers.xlsx vt__Orders Path=c:\data\orders.xlsx vt__Products Path=c:\data\products.xlsx
- Click on the Custom Objects Tab, Click on Add button and select Add Table and Enter an appropriate name and Click on OK button to create.
- Click on Preview Tab, Select Virtual Table(prefix with vt__) from Tables Dropdown or write SQL query with Virtual Table name and click Preview
- Click on the Custom Objects Tab, Click on Add button and select Add Table and Enter an appropriate name and Click on OK button to create.
Load data into Excel Sheet – using ODBC DSN
- Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
- Now, select the DSN we have created, you can write SQL Statement if you want, otherwise leave it blank and select Table and lastly, click on OK button.
- Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
- Now, Select Table you want to load data into Microsoft Excel.
- Select New Worksheet for the data should be loaded then click on Load button to load the data.
- Finally, Your data is loaded now, You can verify the data.
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 – Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell
BI / Reporting Tools
IntegrationETL Tools
Integration
Programming Languages
Integration
ODBC Integration Screenshots in various tools
SQL Query Language Examples
ZappySys introduced most innovative way to transform your complex API into Flat table using familiar SQL Query Language. Here are some examples of SQL Queries our drivers understand.
To see all examples check this link (JSON / REST examples) Select correct driver node in help file – e.g. JSON here, XML here, CSV here
Familiar SQL Syntax (Where, Group By, Order By)
/* SQL Language Support to query API or JSON/XML Files */ SELECT Country as Invoice_Country, SUM(UnitPrice * Quantity) Total FROM value WHERE Discount > 0 GROUP BY Country HAVING SUM(UnitPrice * Quantity)>1000 ORDER BY Total DESC WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')
Read from Local File
/* Query JSON/XML File(s) */ SELECT * FROM $ WITH (SRC=@'C:\Data\Customer*_??.json')
GET data from URL (Read Example)
SELECT * FROM value WITH ( METHOD='GET', SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json' )
POST data to URL (Write Example)
/* Submit data to API - POST Request. You can POST data from file content or supply body as direct string.*/ SELECT * FROM $ WITH ( METHOD='POST' ,HEADER='Content-Type:text/plain||x-hdr1:AAA' ,SRC='http://httpbin.org/post' ,BODY='@c:\files\dump.json' ,IsMultiPart='True' )
API Pagination – Paginate based on URL Path (Loop until no record)
This example shows how to paginate API calls where you need to pass page number via URL Path. Driver keeps incrementing page number and calls next URL until last page is detected. There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don’t specify end detection then it will use default (i.e. No records found).
SELECT * FROM $ WITH ( SRC='https://zappysys.com/downloads/files/test/cust-<%page%>.json' ,PagingMode='ByUrlPath' ,PagingByUrlAttributeName='<%page%>' ,PagingByUrlEndStrategy='DetectBasedOnRecordCount' ,IncrementBy=1 )
Advanced URL JOIN – Pass parent URL data to Child URL
This example shows how to perform URL JOIN. Using this pattern you can pass parent URL data to child API URL in one query. You can pass parent data in child URL or Body (for POST). Placeholder syntax is [$parent_alias.parent_col_name$]. Each level must have ALIAS attribute. For root level you dont have to Prefix JOINxxxxxxx but for any other level you must Prefix JOINxxxxx before each attribute name. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092
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.json' ,NextUrlAttributeOrExpr='$.nextpage' ,Filter='$.customers[*]' ,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.json' ,Join1_filter='$.orders[*]' ,Join1_alias='o' ,Join1_NextUrlAttributeOrExpr='$.nextpage' ,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.json' ,Join2_filter='$.items[*]' ,Join2_alias='i' ,Join2_NextUrlAttributeOrExpr='$.nextpage' ,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 Pattern – Download files to local disk / Use functions
This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092
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.json', FILTER='$.list[*]', ALIAS='a', JOIN1_SRC='[$a.url$]', JOIN1_ALIAS='b', JOIN1_SaveContentAsBinary='true' )
Query Large Compressed File (From URL or Local disk)
This example shows how to read very large file from local disk or URL. You can read uncompressed or compressed file (in our example its GZip compressed file). Using –FAST option in your Filter enables Streaming Mode. To use STREAM Mode you must turn off IncludeParentColumns option as per the example.
SELECT * FROM $ --LIMIT 10 WITH( Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files) ,SRC='https://zappysys.com/downloads/files/test/large_file_10k_largearray_prop.json.gz' --,SRC='C:\Data\large_file*.json.gz' ,IncludeParentColumns='False' --//This Must be OFF for STREAM mode (read very large files) ,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files) )
Support for various authentication types
ZappySys Drivers support various authentication types
Option | Description |
---|---|
Notset | Not set |
Windows | Default Windows Credentials – Current User Credentials |
Basic | Basic – UserID/Password |
NTLM | NTLM – UserID/Password/Domain |
Kerberos | Kerberos – UserID/Password/Domain |
SoapWss | SOAP WSS – UserID/Password in SOAP Body |
Token | Static Token / API Key |
TokenDynamic | Dynamic Token – Two Step Auth (i.e. Login to get token) |
TokenJwt | JWT Token (RFC 7519) |
HashSignature | HMAC Signature (i.e. Request Hashing using Key / Password) |
Custom 2 Step Auth – Dynamic Token Example
This example shows how to call REST API Service using Dynamic Token approach. Learn how to authenticate using userid / password to obtain temporary token which can be passed to call API. Also learn how to pass Body, Headers using HTTP POST. For more information about Dynamic Token visit https://zappysys.com/links/?id=10091
SELECT * FROM $ WITH( Src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=json' --,RequestMethod='POST' --,RequestData='{sessionid: "[$token$]"}' ,Filter='$.rows[*]' ,Header='Content-Type: application/json || 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' )
Basic Authentication Example (Userid / password based)
SELECT * FROM $ WITH ( SRC='http://httpbin.org/basic-auth/user1/pass123', DataConnectionType='HTTP', CredentialType='Basic', UserName='user1', Password='pass123' )
OAuth 2.0 Authentication Example
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' )
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 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
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…)
Consume data from virtually any API service
Our ODBC API Drivers for JSON, XML and CSV comes with more than 100+ Advanced options to connect to virtually API. Below are few Data sources you may recognize which we have tested to work with our drivers. If your API is not listed below then feel free to contact our Data Integration Team (via support form) and get your question answered quickly.
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
What people say about ZappySys