ODBC Excel Driver

Excel 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

Read / write Excel Workbooks using familier SQL Query language
Supported for reading *.XLSX and *.XLS (legacy)
Support for flexible metadata detection mode so you can get accurate datatypes
Support for Password protected workbooks
Integrate insight any ODBC Compliant Reporting / ETL tools (e.g. Power BI, Tableau, Qlik, SSRS, Informatica, Excel, 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)

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
```
1. 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.
2. Click on Preview Tab, Select Virtual Table(prefix with vt__) from Tables Dropdown or write SQL query with Virtual Table name and click Preview

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 for REST API, JSON, XML - Integrate with Excel, Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

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 Integration	ETL Tools Integration	Programming Languages Integration
Microsoft Power BI Tableau SSRS (SQL Reporting Services) QlikView /Qlik Sense MicroStrategy Google Sheet Microsoft Excel Microsoft Access Oracle OBIEE Many more (not in this list)…..	Informatica PowerCenter (Windows) Informatica Cloud SSIS (SQL Integration Services) SQL Server Talend Data Studio Pentaho Kettle Oracle OBIEE Many more (not in this list)…..	Visual Studio C# C++ JAVA Python PHP PowerShell T-SQL (Using Linked Server)

ODBC Integration Screenshots in various tools

Tableau Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Power BI Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
SSRS (SQL Server Reporting Services) Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Visual Studio Integration (WinForm, Grid Data binding using ADO.net) - ODBC Driver connection for REST API / XML / JSON / SOAP / OData
Microsoft Excel Integration - ODBC Driver connection for REST API / XML / JSON / SOAP / OData

SQL Query Examples – Excel Driver


/*--------- DML - UPDATE Example ---------*/
UPDATE
   account
SET
  name = 'New Company Name',
  BillingState = 'GA'
WHERE
  id IN ('0014N00001hTNEEQA4')


/*---------  - SELECT ---------*/
SELECT  *  FROM [sheet1] --with(RangeStartCell='A1')


/*---------  - SELECT WITH RANGE ---------*/
SELECT * FROM [sheet1] WITH(RangeStartCell='A1')


/*--------- SELECT with StartRange and EndRange ---------*/
SELECT  *  FROM [Sheet1] WITH(RangeStartCell='A1', RangeEndCell ='F100')


/*--------- SELECT from data table without columns ---------*/
SELECT  *  FROM [Sheet1]  WITH(RangeStartCell='A1' , Headerless=1)


/*---------  SELECT with skip rows from top ---------*/
SELECT *  FROM [Sheet1] WITH(RangeStartCell='A1' , SkipRows=5)


/*---------  INSERT ---------*/
INSERT INTO [SheetName](Col1, Col2) Values('something',1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)


/*---------  INSERT With Options---------*/
INSERT INTO[SheetName] (Col1, Col2) Values('something',1234) WITH(RangeStartCell='A1'--Use this option to start Insert at a specific location
--, Headerless=0 --Use this option to indicate no columns found (skip writing header line)
--, TreatValueAsFormula=0 --use this option if some value has formula which starts with = sign e.g. =C1+C2
--, ClearTargetSheetBeforeWrite=0 --Clears entire sheet before write
--, RangeToClearBeforeWrite='A2:F10000' --Clears specific range of cells before write
--, PreserveTargetFormat=1 --use this option along with ClearTargetSheetBeforeWrite or RangeToClearBeforeWrite
)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)


/*---------  UPDATE---------*/
UPDATE [SheetName] SET Col1='something', co12=1234 WITH(RangeStartCell='A5')

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(RangeStartCell='A2', TreatValueAsFormula=0, Headerless=0, PreserveTargetFormat=1, ClearTargetSheetBeforeWrite=0, RangeToClearBeforeWrite='A1:F100')

--CONNECTION( Path='C:\temp\data.xlsx'
--        , ExcelVersion='v2013' --Default, v2007, v2010, v2013
--        , OpenPassword='something'
--        , EditPassword='something'
--        , OutputBadDateAsNull=1
--        , CreateFileIfMissing=1
--        , RetainSameConnection=1)


/*---------  Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause---------*/
INSERT INTO My Table
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(RangeStartCell='A1', TreatValueAsFormula=0, ClearTargetSheetBeforeWrite=0, PreserveTargetFormat=0, RangeToClearBeforeWrite='A2:F10000')


/*---------  Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause - SOURCE Clause---------*/
UPDATE [SheetName]
--SET clause is omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match UPDATE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToUpdate.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(RangeStartCell='A1', TreatValueAsFormula=0, ClearTargetSheetBeforeWrite=0, PreserveTargetFormat=0, RangeToClearBeforeWrite='A2:F10000' )


/*---------  Bulk UPDATE (Read from Same Excel file) - SOURCE Clause---------*/
UPDATE [TargetSheetName]
--SET clause is omitted when you use SOURCE clause
SOURCE('select * from [SourceSheetName]')


/*---------  Using Connection Clause---------*/
SELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTIONE(
    ,Path ='c:\test.xlsx'
--  , ExcelVersion = 'v2013' --Default, v2007, v2010, v2013
--  , OpenPassword = 'something'
--  , EditPassword = 'something'
--  , OutputBadDateAsNull  = 1
--  , CreateFileIfMissing = 1
--  , RetainSameConnection  = 1
)


/*--------- DateTime field Compare in WHERE / ORDER BY---------*/
SELECT * FROM [Sheet1] 
WHERE OrderDate <= DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd 
HH:mm:ss.fff' format (where fff is milliseconds)
ORDER BY DATETIME(OrderDate)
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')


/*--------- WITH / CONNECTION Clause---------*/
SELECT * FROM [Customer] 
LIMIT 3
WITH(RangeStartCell='A1')
CONNECTION(Path='c:\test.xlsx');


/*--------- Filter on Date, Group By, Order By, Limit Clause---------*/
SELECT  
 Country AS Invoice_Country
,SUM(UnitPrice * Quantity) AS Invoice_Total
FROM [Sheet1]
WHERE Discount > 0
AND OrderDate <= DATETIME('1997-12-31 00:00:00')
--or use OrderDate<=DATE('1997-12-31') 
--Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY Country
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY 
   Invoice_Total DESC
--,DATETIME(OrderDate) 
LIMIT 3
CONNECTION(Path='c:\test.xlsx');


/*--------- Case Statement---------*/
SELECT 
    CustomerID
 , CompanyName
 , Country
 , Region
 --Pattern#1 - Match by clause
,CASE
   WHEN Country IN('UK','Germany','France') OR Region='BC' THEN 'EU'
  WHEN Country IN('USA','Maxico','Canada') THEN 'NA'
  WHEN Country IN('Brazil','Argentina','Venezuela') THEN 'SA'
 ELSE 'Unknown'
END Pattern1

--Pattern#2 - Match by static value or simple expression
,CASE Country
  WHEN 'U'+'K' THEN 'EU'
  WHEN 'USA' THEN 'EU'
  WHEN 'Brazil' THEN 'SA'
  WHEN 'Argentina' THEN 'SA'
  WHEN 'Venezuela' THEN 'SA'
  ELSE 'Unknown''
END Pattern2 

FROM Customer
CONNECTION(Path='c:\test.xlsx')


/*--------- SELECT INTO / Multiple Statements---------*/
SELECT * INTO #tmpCustomers FROM [Sheet1]
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')

SELECT * INTO #tmpOrders FROM [Sheet2]
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Path='c:\test.xlsx')

--JOIN 2 temporary tables
select c.CompanyName,Count(*) TotalOrders 
FROM #tmpCustomers c 
INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY  c.CompanyName;


/*--------- UNION ALL / UNION Statement---------*/
SELECT * into  #tmpCustomersUK  FROM [Sheet1UK] Where Country='UK'
--WITH (MyParam1='something1', MyParam2='something2')

SELECT * into #tmpCustomersUSA FROM [Sheet1USA] Where Country='USA'
--WITH (MyParam1='something1', MyParam2='something2');

select * from #tmpCustomersUK 
UNION ALL
select * from #tmpCustomersUSA;

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

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.

	REST / OData / SOAP API		XML Files

	Salesforce API		JSON Files

	Amazon AWS API		Google Analytics API

	Google BigQuery API		ElasticSearch API

	Xero API		eBay API

	Zendesk API		MailChimp API

	Google DoubleClick API		HubSpot API

	WorkDay API		Google AdWords API

	Google Sheet API		SurveyMonkey API

	Facebook API		Twitter API

	Office 365 API		ServiceNow API

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

DownloadFree 30 days trial

Help FileOnline

BuySecure Checkout

ODBC Excel Driver

Feature Summary

Featured Articles

Configure Excel Driver

— Using Direct Query —

— Using Stored Procedure —

— Without Parameters —

— With Parameters —

— Using Virtual Table —

Load data into Excel Sheet – using ODBC DSN

Integration Scenarios (Reporting / ETL / BI / Programming)

ODBC Integration Screenshots in various tools

SQL Query Examples – Excel Driver

REST / SOAP API Call Settings

SQL Query Editor / Live Preview / Examples

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

Consume data from virtually any API service

REST / OData / SOAP API

XML Files

Salesforce API

JSON Files

Amazon AWS API

Google Analytics API

Google BigQuery API

ElasticSearch API

Xero API

eBay API

Zendesk API

MailChimp API

Google DoubleClick API

HubSpot API

WorkDay API

Google AdWords API

Google Sheet API

SurveyMonkey API

Facebook API

Twitter API

Office 365 API

ServiceNow API

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