CSV Adapter








The CSV adapter allows you to access data from flat files; this adapter uses the Zip, Files, Azure Storage and other adapters to access remote files directly.




Features



  • Read flat files, including CSV and fixed length format using SQL
  • Use SQL commands from SSMS directly
  • Use ADO.NET to read/write CSV data
  • Export data from any source into a CSV file
  • Import CSV data into SQL Server quickly
  • Cache CSV data locally in Enzo for faster queries
  • Access CSV data using simplified REST commands

Sample SQL Commands



select * from CSV.read@csvConfig1 WHERE path='dropbox://folder1/myfile.txt'
SELECT * FROM CSV.[data@labdata-noheaders] 
WHERE path='C:\Tmp\labdata\Completed\labdata.zip' AND Quality < 30
EXEC CSV.WriteCSV 'generic', 'SELECT * FROM SharePoint.Lists', 'ftp://Personal/AllLists.txt',1 
EXEC CSV.WriteCSV 'generic', 'SELECT * FROM SQLServer.Table@mytable', 'sharepoint@mydoclib://mytable.txt'



Advanced Security



Enzo offers advanced security options for Enterprise class organizations, including advanced auditing and an encypted Vault mechanism for connection strings. None of the settings are stored on the client workstations/machines; this means that applications and users can connect using different credentials than the actual API secrets.

  • SSL Encryption: All communications to and from Enzo can be encrypted using SSL technology, including the SQL Server and HTTP/REST endpoints of Enzo
  • Configuration Settings: All configuration settings, including passwords and database connection strings are encrypted centrally in Enzo
  • Active Directory Integration: Enzo offers AD integration allowing end-users to use their network credentials to access remote systems
  • ACL: All methods exposed by Enzo are secured by a fine-grained Access Control List (ACL)
  • Auditing: All data access is recorded and can be stored externally to Enzo for auditing purposes


Primary Use Cases




Replication

With SQL Data Sync you can easily replicate data to SQL Server (or Azure SQL Database) one time, or on a schedule with automatic refresh. The Data Sync technology can automatically apply Upsert (Insert/Update) and Cascading Delete logic so that your database table is always synchronized with your source data.

Data Marts

Enzo Data Sync allows you to quickly build or enhance existing SQL Server Data Marts and Data Warehouses by importing and automatically refreshing data. Once your data is replicated, you can build reports with any reporting tool such as PowerBI, Tableau or Cognos, and build applications that use a replicated data set instead of accessing real-time data for reduced latency.

Triggers for Event-Driven Integration

Enzo Data Sync only updates records that have changed and optionally deletes records that are no longer found in the source system. This allows you to create INSERT, UPDATE and DELETE triggers on your SQL Server tables, creating an event-based mechanism to react to changes in your source data.

If you can SELECT it, you can SYNC it!


Query from SQL Server Management Studio

You can query your data directly from SQL Server Management Studio (SSMS), in real-time, without the need to install drivers. Use SSMS to connect to Enzo directly, and start executing SQL commands.

Data Exploration

Each adapter allows you to explore your remote systems directly, without requiring the use of ODBC drivers. Enzo exposes Stored Procedures (EXEC command) and in some cases supports SELECT, UPDATE, DELETE and INSERT operations. For example, you can select from a SharePoint list directly through Enzo.

Linked Server Support

In most cases you can also create a Linked Server to Enzo, and call any adapter through a Linked Server call (for example from Stored Procedures, Functions, SQL Jobs, or even Triggers). This capability provides deep integration with SQL Server databases and any remote system.


Query from Excel/PowerBI

Use Excel and PowerQuery to query your data through Enzo directly. Build queries directly against any adapter, and make the queries available to Excel and PowerBI.

PowerBI and Excel PowerQuery

With Enzo, you can create powerful dashboards and Excel spreadsheets that tap directly into any adapter loaded by Enzo, without the need for ODBC drivers. When connecting to Enzo directly, your reports access data directly from the source system, in real-time. For example, you can build a PowerBI dashboard that shows Twitter mentions.


Import/Export Flat Files

Import and export flat file data quickly, using the target cloud store of your choice. Enzo offers a unique target URI syntax allowing you to directly import and export data to your preferred target cloud drive.

CSV and Fixed-Length

Enzo allows you to read and write CSV and fixed-length files directly, regardless of the target location, even if stored inside Zipped documents. This capability eliminates complex programming tools and allows you to manage import/export of flat files directly using the power of SQL commands.

Any Cloud Target

Enzo supports creating CSV files directly to dropbox, SharePoint Documents, S3, Azure Blobs, FTP, Egnyte, OneDrive (Personal and Business), GoogleDrive and local files system.


Sample SQL Command

For a complete list of options, and destinations available, see Explore Import and Export CSV Files with SQL Commands

Sample SQL command to export all items from a SharePoint list called Customers into dropbox directly.

EXEC CSV.WriteCSV 
      'generic', 
      'SELECT * FROM SharePoint.list@Customers', 
      'dropbox://customers/export.txt'

Sample SQL command to read the previously exported data.
SELECT * FROM CSV.Read@generic WHERE path='dropbox://customers/export.txt'


Build Applications

Build applications quickly using the .NET language, Python, PowerShell, C++ or any other programming language, on any platform (including RaspberryPi devices) using the native REST interface. Enzo supports both the SQL Server protocol and HTTP REST commands out-of-the-box.

ADO.NET Support

With the .NET language, you have the option of using REST calls or ADO.NET directly, using SQL commands without the need to install an ODBC driver. When using ADO.NET, just use the SqlConnection class as if you were connecting to SQL Server directly, but instead connect to Enzo. Enzo is wire-compatible with the SQL Server protocol. This capacbility allows you to build or enhance existing dashboards very quickly.

IoT Development

Virtually all available commands in Enzo are also exposed as HTTP/S endpoints, allowing you to communicate with any supported remote system through simple REST calls. This allows you to build applications on any platform, including Raspberry Pi devices running the Linux operating systems (Raspbian O/S) and any device able to establish an Internet connection (for example using a an MXChip device).

Protocol Abstraction

Enzo presents any supported API and Protocol as a common REST interface, making it trivial to communicate with cloud APIs, enterprise systems, and communication protocols, including messaging protocols such as AMQP.