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.

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…)

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