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)	See also XML File / SOAP API Driver CSV File / REST API Driver Data Gateway
Download Help File Buy	View All Drivers
Featured Articles How to import JSON to Excel (Load File, REST API, SOAP XML) How to import REST API in Power BI (Load JSON / SOAP XML) How to import REST API in SQL Server (Call JSON / XML SOAP Service) How to call REST API in Python (Read JSON / SOAP XML) How to read REST API in SSRS Reports (JSON / XML / Call SOAP Web Service) Read JSON in Informatica – Import REST API / SOAP / JSON File Read Amazon S3 data in Power BI – Call AWS API (JSON / XML) How to Import LinkedIn data in Power BI Calling REST API in C# (Read JSON Data) How to call Salesforce REST API via ODBC driver

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

1. — 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.

   

2. — 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>'
     

     

3. — 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

1. Open Microsoft Excel, In the Data Menu, select New Query, select From Other Sources and select From ODBC.
2. 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.
3. Now, in the Windows Tab, select an appropriate credentials and click on Connect button.
4. Now, Select Table you want to load data into Microsoft Excel.
5. Select New Worksheet for the data should be loaded then click on Load button to load the data.
6. 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 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 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

What people say about ZappySys

We are so glad that we chose ZappySys … We have been using ZappySys for our data warehouse SSIS ETL for a couple of years and we LOVE the product so much. It comes with so many features and functions. Whenever we run into obstacle, ZappySys has a solution for us. Support response is fast and get the right answer all the time. At the very beginning when we were evaluating third party SSIS controls, we also tried CData software which was fast and easy to connect to data sources and destinations. However, features and functions were very limited partly due to data schema was pre-programmed by CData. Since every business is different, we needed something flexible and granularity. We are so glad that we chose ZappySys PowerPack Kevin Chen Berricle Awesome framework to bring a small company into the world of connectivity! Zappysys has been a fantastic way to bring our small company into the world of apis, automation, and systems integration by providing tools within an easy-to-use framework. As a small company we do not have the resources to hire a full time developer. For our company, Zappysys has provided the essential framework needed for us to take everything to the next level by getting us past many of the nuts and bolts hurdles to initial stages of development. Additionally, every time we have worked with their support team they have provided a one-on-one consulting session to solve our problem. And what’s more is they always answer the call quickly so we are never left hanging out to dry. Ben Kingery Oxspring Paul Very resourceful and helpful We have been working with the support team at ZappySys for over a year now and have consistently found them to be extremely helpful in resolving any issues we have encountered. They have proven themselves to be resourceful and creative in offering multiple solutions to overcome any and all challenges we have faced in our day-to day-operations. Either online or by telephone, their personnel are reliable, energetic and sensitive to the urgency of our problem-solving needs. A well-organized team of real go-getters. Senthil Sekar Publicis Groupe Great Product & Great Support The responsiveness of your support staff during the evaluation period really has been excellent. I’ve contacted them a few times with queries specific to the scenario I am working with and they have answered straight away via chat and provide exactly the detailed technical input required to progress. Please convey my sincere gratitude to your support team, they have been a great help. The functionality of the component that is of particular interest (the REST source) is very powerful, and due to constraints regarding access to the environment that I am targeting, it is in fact is the only thing I have found that I am able to use and I have tried a few other components from major vendors also. Jes Kirkup KTC When it comes to integrating data flow between multiple technologies, the ZappySys software and their amazing support are well worth the investment. At Synchronous Customers, we need a way to connect various types of data together quickly. The super handy Zappysys Connectors and great documentation allow us to do this will having to write a lot of the code. The system is stable and reliable and the support team really comes through for us if we get stuck on something. The tools are more than just drag and drop, point and click. They include all of what you need to see what is really going on under the hood and tools for troubleshooting. Thank you ZappySys! We are glad we found you and highly recommend! Joel Conner Synchronous Customers ZappySys SSIS PowerPack is a sensational product, with a great support team. In my book, this makes it stand above the other products in the marketplace. I looked everywhere for a product that could easily integrate to a specific web portal backend api via JSON, and after many attempts the ZappySys product was the only solution that could give me all the options I needed in a quick & clean interface within SSIS. Coupled with some great tech support and customer support, I would definitely recommend ZappySys SSIS PowerPack. Maz Dunic Save the Children Australia Great Support I couldn’t be happier with the support I received this evening. I had been building out a proof of concept and just couldn’t get past one last error. I submitted a support request and had someone logged in and fixing things within 2 hours. Not only did they solve my problem but went on to assist with answers to basic SSIS tasks. I enthusiastically support this product. Barry Wilson

 

DownloadFree 30 days trial Help FileOnline BuySecure Checkout