Included in ODBC PowerPack

ODBC CSV / REST API Driver

Query CSV and flat files from disk or HTTP endpoints using SQL.

Read local CSV / TSV files or REST API responses in CSV with plain SQL. Handle OAuth, JWT and pagination, read large files fast, and query the data from Power BI, Excel, SSIS, Python or SQL Server, all without writing code.

Included in the Standard edition and above

ZappySys ODBC Driver - Create CSV Driver
Driver UI, SQL query and live preview

Trusted by data teams worldwide

MicrosoftIBMOracleDell TechnologiesHPCiscoAT&TVerizonDHLFedExUPSWalmart

Also rated on Capterra★★★★★ , Trustpilot★★★★★ and BBBA+ — see why teams trust ZappySys

At a glance

What the CSV / REST API Driver can do

Query CSV and flat files from disk or HTTP endpoints using SQL.

Familiar SQL

Use WHERE, ORDER BY, GROUP BY and built-in math, string, date and JSON functions on top of the data source.

  • Standard SELECT syntax
  • Custom functions in the SQL dialect
  • Driver options in a WITH clause

Query examples

CSV & flat files

Read delimited files from disk or an HTTP endpoint and query them as tables.

  • Delimiter, header and encoding options
  • Local files and URLs
  • Wildcard patterns

See the SQL example

REST API downloads

Call an API that returns CSV, with custom headers, body and pagination.

  • GET and POST
  • Pagination options
  • OAuth and Basic authentication

See the screenshots

Works with the tools you have

Any ODBC-compliant tool can use the DSN: Power BI, Tableau, Qlik, SSRS, Informatica, Excel and SSIS.

  • Create a DSN once, reuse it everywhere
  • 32-bit and 64-bit ODBC
  • Stored in the standard ODBC Data Source Administrator

Setup guide

Use it from code

Connect from Java, C#, Python, PowerShell and more with an ODBC connection string that names the DSN.

  • Standard ODBC connection strings
  • Parameterized queries
  • Same SQL as in the UI

Python example

SQL Server via Data Gateway

Query the driver from SQL Server (linked server / OPENQUERY) without installing it on the SQL Server machine, through the Data Gateway.

  • No driver install on the server
  • Works with SQL Server 2012 through 2025 (32/64 bit)
  • Same DSN, same SQL

About Data Gateway

See all features
  • 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, 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)
  • 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
Runs in your environment ·

SQL Server 2012–2025

through the Data Gateway · Read the FAQ

Ways to use it

Pick how you reach your data.

Configure the DSN once, then query it from any ODBC tool or from SQL Server.

Query CSV files with SQL.

Point SRC at a file or wildcard.

  • Delimiter and header options
  • Wildcard patterns
  • Local files and URLs
ZappySys ODBC Driver UI: read from a file or API URL (JSON REST API, XML SOAP web service, CSV)

Inside the UI

The driver UI, step by step.

Real screens from the ZappySys ODBC driver configuration.

Create the DSN

Add a user or system DSN and pick the CSV / REST API Driver.

SQL example

Write SQL. Get a flat table.

The driver reads local files or Web API responses, flattens the CSV and lets you use WHERE, ORDER BY, GROUP BY and functions on top. Below: a sample document, the SQL query and the rows it returns.

  1. Sample CSV data

    A small CSV file on disk. Point SRC at a URL instead and the same query reads a Web API response.

    customers.csv

    CustomerID,CompanyName,City
    C1,Company 1,Atlanta
    C2,Company 2,Boston
    C3,Company 3,Atlanta
  2. Run the SQL query

    SQL QuerySELECT CustomerID, CompanyName FROM $ WHERE City = 'Atlanta' ORDER BY CompanyName WITH ( SRC = @'C:\Data\customers.csv' )

    Paste the statement into the driver’s Query Editor, or run it from any ODBC tool or language.

    WITH (…) clause carries driver options, so a single DSN can read many sources.

  3. Rows returned to your tool

    Illustrative output rows from the sample input
    CustomerID CompanyName
    C1 Company 1
    C3 Company 3

    Connect Power BI, Excel, SSIS, Python or SQL Server to the DSN to get the same rows.

Illustrative input and output. More query samples are in the online help.

SQL query examples

Copy a query, change the source, run it.

19 of the 40 examples from the CSV / REST API Driver help. Open one to see the SQL.

Read files, API responses and objects as rows.

Query CSV API Service

This example shows how to query CSV API service. In order to see table list you must populate required connection parameters on the parameter grid

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

Open this example in the help

Query Single File

This example shows how to query single CSV file (For multiple file use wildcard (i.e. *.csv)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).

SELECT * FROM $ WITH (SRC='C:\Data\cust-1.csv')

Open this example in the help

Query Multiple Files

This example shows how to query multiple CSV files using wildcard (i.e. *.csv). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).

SELECT * FROM $
WITH (
    SRC='C:\Data\cust*-?.csv'
    --,RECURSIVE='True' --Include files from sub folder
)

Open this example in the help

Query direct CSV string (embedded inside query)

This example shows how to query direct CSV string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call).

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='|'
)

Open this example in the help

Query Command line Output (Console Capture / MS DOS / PowerShell)

This example shows how to return data from command line output / batch file. To treat Data as command line simply prefix source command line with cmd:>. Here is more information about streaming data from command line output.

SELECT * FROM $
WITH(
     Data='cmd:>powershell -executionpolicy bypass -Command "[System.IO.StreamReader]::new((Invoke-WebRequest -URI https://zappysys.com/downloads/files/test/invoices.csv).RawContentStream).ReadToEnd()"'
     --Data='cmd:>powershell -executionpolicy bypass -Command "echo col1`,col2`r`n1`,AA`r`n2`,BB|Out-File c:\temp\dump.csv ; more c:\temp\dump.csv"'
	,ColumnDelimiter=','
    --,ColumnDelimiter='
SELECT * FROM $
WITH(
     Data='cmd:>powershell -executionpolicy bypass -Command "[System.IO.StreamReader]::new((Invoke-WebRequest -URI https://zappysys.com/downloads/files/test/invoices.csv).RawContentStream).ReadToEnd()"'
     --Data='cmd:>powershell -executionpolicy bypass -Command "echo col1`,col2`r`n1`,AA`r`n2`,BB|Out-File c:\temp\dump.csv ; more c:\temp\dump.csv"'
	,ColumnDelimiter=','
    --,ColumnDelimiter='\0003' --{TAB} char may not work in some cases if cmd:> mode used in Data='...'
    ,IgnoreBlankLines=1 --//ignore empty lines in Console Output
	,HasColumnHeaderRow='True'
	--,DateFormatString='yyyy-MM-dd HH:mm:ss'
	--,SkipRows='5'
	--,SkipHeaderCommentRows='5'
)
03' --{TAB} char may not work in some cases if cmd:> mode used in Data='...' ,IgnoreBlankLines=1 --//ignore empty lines in Console Output ,HasColumnHeaderRow='True' --,DateFormatString='yyyy-MM-dd HH:mm:ss' --,SkipRows='5' --,SkipHeaderCommentRows='5' )

Open this example in the help

Escape Sequence (String Literals with new lines, tabs)

This example shows how to use escape sequence in String Literals (Quoted values (single quotes) in WITH, SELECT or some other clause if driver supports). You can use @ symbol before string literal to allow special characters (e.g. \r \n \t \v \u0041 \\).

SELECT * FROM $
WITH
(DATA=@'Name|Age|Income|Hiredate\r\nBob\u0041|55|5000.00|2018-01-01\r\nSam|51|2000.00|2016-02-01\r\nJohn|45|3000.00|2014-07-01\r\n'
,ColumnDelimiter='|'
)

Open this example in the help

Query with static Metadata (META option / Override Mode)

This example shows how to supply custom Metadata rather than relying dynamic metadata detection (based on 300 rows scanning). If you run query you can click on [View Metadata] button to generate default metadata.

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'

)

Open this example in the help

See all 40 SQL examples in the help

Compatibility and requirements

Built for Windows, and for your own environment.

  • Windows 11, 10, 8 and 7, and Windows Server 2022, 2019, 2016, 2012 R2 and 2008
  • .NET Framework 4.x. The JDBC Bridge Driver also needs Java 8 or later
  • 32-bit and 64-bit ODBC drivers. SQL Server 2012 through 2025 through the Data Gateway
  • Self-hosted: drivers run in your Windows process, business data does not pass through ZappySys servers, and credentials stay in your DSN
  • Free trial of ODBC PowerPack: test with your own data before you buy

FAQ

Questions about the CSV / REST API Driver

Which tools and languages can use this driver?

Any ODBC-compliant tool, such as Power BI, Tableau, Qlik, SSRS, Informatica, Excel and SSIS. You can also connect from Java, C#, Python, PowerShell and other languages with an ODBC connection string.

Do I need to install the driver on SQL Server?

No. With the Data Gateway option the driver runs on the gateway machine and SQL Server connects to it through a linked server.

Which edition includes the CSV / REST API Driver?

The CSV / REST API Driver is included in the Standard, Professional and Enterprise editions of ODBC PowerPack. See the edition comparison for the full list.

Can I query a CSV file from a URL?

Yes. SRC can be a local path, a wildcard pattern or an HTTP URL.

Can I save a query to reuse it?

Yes. Save a query as a virtual table or a stored procedure (custom objects) in the DSN. Tools then see it like a normal table, and a procedure can take parameters. See custom objects in ODBC drivers.

Is there an SSIS version?

Yes. This driver uses the same data engine as the CSV File Source in SSIS PowerPack.

Which SQL Server and Windows versions are supported?

Support for SQL Server 2025, 2022, 2019, 2017, 2016, 2014 and 2012 (32/64 bit) through the Data Gateway. See the system requirements.

Can I try it before buying?

Yes. Download ODBC PowerPack, create a DSN and test your own data before you buy.

Independent ratings

Trusted by data teams, recognized by the industry

See why teams trust ZappySys (opens in new tab)

What customers say

  • Customer story

    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.

    Ben KingeryOxspring Paul
  • Customer story

    At Synchronous Customers, we need to connect various types of data quickly. The super handy ZappySys connectors and great documentation let us do it without writing a lot of code.

    Joel ConnerSynchronous Customers
  • Customer story

    We have worked with the ZappySys support team for over a year and have consistently found them extremely helpful — resourceful and creative in offering multiple solutions to our challenges.

    Senthil SekarPublicis Groupe
  • Trustpilot

    I’ve been using ZappySys for over 6 years — it makes development faster, and I introduced it to my current company.

    Anna JohnsonTrustpilot review
  • Customer quote

    I submitted a support request and had someone logged in and fixing things within 2 hours.

    Barry Wilson
  • Customer quote

    The REST source is very powerful — the only thing I found I could use after trying components from major vendors.

    Jes KirkupKTC
  • Customer quote

    Whenever we run into an obstacle, ZappySys has a solution for us. We are so glad that we chose ZappySys PowerPack.

    Kevin ChenBerricle

Start your free trial today

Install ODBC PowerPack and add a CSV / REST API Driver DSN.

Preview your data in the driver UI before you connect your tool.

  • One DSN, many toolsPower BI, Excel, SSIS, Python and SQL Server can all use it.
  • Free trialTest the driver with your own data before you buy.
  • Live supportHelp during your trial and after purchase.

New purchases may be eligible for refund review within 30 days. See the refund policy.

Talk to an engineer before or during your trial.

1 / 12