Introduction

Enzo Server
  Installation
  Quick Start

  Core Features
    Async Calls
    ConnectionStrings
    Edge Cache
    HTTP Access
    Scheduling
    Views

  Advanced Capabilities
    Change Data Capture

  Administration
    Configuration Settings
    Logins & ACL
    Auditing
    Current Executions
    Linked Server
    SSL

  Adapters
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Couchbase
    CSV
    DB (RDBMS)
    Files
    Flight Aware
    FTP
    Geonames
    Insteon
    Loggly
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    SQL Server
    Threading
    Twilio
    Twitter
    VellemanDMX
    Weather (Underground)
    Whiparound
    WMI
    Xls (Excel)
    ZIP


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Handler Columns

    Advanced SDK Concepts
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables

/adapters/csv




CSV


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.

This adapter uses a custom definition for fields; as a result the Enzo Manager interface looks different than other adapters.


Configuration Settings



This adapter does not use configuration settings.


Handlers



ClearLoopBack Removes a loopback connection string from this adapter. details 

Arguments

Name Description Default
name R The name of the loopback

SQL Call

EXEC Csv.DeleteLoopBack 'localenzo'


Http Call

/bsc/csv/clearloopback

Output Columns

This call does not return data.

CreateDefinition Create a definition for loading rows from text files details 

Arguments

Name Description Default
name R Definition name
description Description for definition
columnDelimiter Column delimiter token ,
firstRowNames Indicates if first row of files contains the column names False
referenceFile Path to a reference file to read first row for column names
columnNames Optional comma separated list of column names and Enzo types
columnWidths For fixed width column files. Column widths separated by commas, e.g. "15,25,25,10".
trimWhiteSpace Trim the trailing whitespace from strings
quotedText Indicates whether text columns are quoted False
commentToken The token to use for commented out rows #
defaultPath Default path, UNC, URL, or URI to use if none is supplied
defaultSubDir Default ZIP subdirectory to use if none is supplied and using a ZIP file
defaultFilePattern Default search string to match against the file-system entries in a ZIP file. ONLY the first matching file is selected. Wildcard specifiers are: *: Zero or more characters, ?: Exactly one character
userId Default User ID for login
password Default password for login
filePassword Default password for decrypting files, e.g. ZIP.
defaultMaxRows Default number of rows to read if none is supplied. Default is all rows. -1
defaultStartingRow Default first row to begin reading if none is supplied. Default is the first row. -1
defaultMaxErrors Default maximum number of errors before aborting if none is supplied. 100
defaultTimeout Default timeout in seconds. Default is infinity (-1) -1
defaultAuthType Default authentication security if none is supplied. NULL: None, B: Basic, W: Windows, K: Kerberos
defaultConnectionType Default FTP connection type if none is supplied: null, FTP or FTPS
defaultPort Default port to use for connection. Default is the that of the protocol
defaultFtpIsSsl Default FTP connection uses SSL if none is specified. Only applies if an FTP defaultConnectionType is specified. False
defaultIsPassive Default FTP passive state to use when performing download if none is specified. Only applies if an FTP defaultConnectionType is specified. False
defaultFilter Default WHERE clause to filter results if none is specified
accountId The account id of the user (send null for current user)

SQL Call

exec csv.CreateDefinition 
	'name',	-- Definition name

	-- Description for definition
	'description',

	',',			-- Column delimiter token
	0,				-- Indicates if first row of files contains the column names
	null,			-- Path to a reference file to read first row for column names

	-- Optional comma separated list of column names with SQL types
	'col1 varchar(max), col2 int',
	
	-- For fixed width column files. Column widths separated by commas, e.g. '15,25,25,10'.
	null,

	0,				-- Trim the trailing whitespace from strings
	1,				-- Indicates whether text columns are quoted
	null,			-- The token to use for commented out rows

	-- Path, UNC, URL, or URI to use if none is passed in as an argument
	'ftp://somesite.com/path/myfile.csv',

	null,			-- Default ZIP subdirectory to use if none is supplied and using a ZIP file
	null,			-- Search string to match against the file-system entries in a ZIP 
					--	file. ONLY the first matching file is selected. Wildcard specifiers 
					--	are: *: Zero or more characters, ?: Exactly one character
	'anonymous',	-- Default User ID for login
	'myname@mysite.com', -- Default password for login
	null,			-- Password for decrypting files, e.g. ZIP.
	null,			-- Number of rows to read.  Default is all rows.
	null, 				-- First row to begin reading. Default is the first row.
	null, 			-- Maximum number of errors before aborting. Default is 100 errors.
	null, 			-- Connection timeout in seconds. Default is infinity (-1)
	null,			-- Authentication security
					--		NULL: None, B: Basic, W: Windows, K: Kerberos
	'FTP', 			-- Connection type. The following protocols are
					--	supported: FILE, FTP, HTTP (includes https), IMAP, SMTP, SFTP, 
					--	TFTP (Trival FTP)
	null, 			-- Port to use for connection. Default is the that of the protocol.
	null, 			-- FTP connection uses SSL (FTP only)
	1, 				-- FTP passive state to use when performing download. (FTP only)
	null			-- WHERE clause to filter results if none is specified


Http Call

/bsc/csv/createdefinition

Output Columns

Boolean ResultTrue/false indicator of creation success
DeleteDefinition Delete a definition details 

Arguments

Name Description Default
name R Definition name
accountId The account id of the user (send null for current user)

SQL Call

exec Files.DeleteDefinition 'myDefName'


Http Call

/bsc/csv/deletedefinition

Output Columns

This call does not return data.

GetVar Gets a session variable details 

Arguments

Name Description Default
name R Session variable name

SQL Call

EXEC CSV.GetVar 'DefaultColumnDelimiters'


Http Call

/bsc/csv/getvar

Output Columns

String valueValue of the session variable
ListDefinitions List the definitions using an optional pattern (LIKE) details 

Arguments

Name Description Default
pattern Definition name pattern (use SQL % and _) to search
accountId The account id of the user (send null for current user)

SQL Call

exec Files.ListDefinitions '%test%'
select * from Files.Definitions


Http Call

/bsc/csv/listdefinitions

Output Columns

Int32 id
String Name
String Description
String ColumnDelimiters
Boolean FirstRowNames
String ColumnNames
String ColumnWidths
Boolean QuotedText
Boolean TrimWhiteSpace
String CommentTokens
String defaultPath
String defaultSubDir
String defaultFilePattern
String defaultFilter
String defaultUserId
String defaultPassword
String defaultFilePassword
Int32 defaultMaxRows
Int32 defaultStartingRow
Int32 defaultMaxErrors
Int32 defaultTimeout
String defaultAuthType
String defaultConnectionType
Int32 defaultPort
Boolean defaultFtpIsSsl
Boolean defaultFtpIsPassive
DateTime RowCreated
DateTime RowUpdated
ListSupportedColumnTypes Returns the SQL types supported by CSV column definitions details 

Arguments


SQL Call

exec bsc.Files.ListSupportedColumnTypes
select * from bsc.csv.SupportedColumnTypes


Http Call

/bsc/csv/listsupportedcolumntypes

Output Columns

String SqlTypeSQL type
String DotnetTypeIntermediate .Net data type
Pump Pumps the content of a data source into another using a loopback connection details 

Arguments

Name Description Default
loopbackin R The name of the loopback pointing to a database or an Enzo server that will be used to fetch data. Send an empty string to use the current Enzo.
loopbackout R The name of the loopback pointing to a database or an Enzo server where the data will be inserted. Send an empty string to use the current Enzo.
sqlread R The SQL command to fetch data from the loopbackin connection.
sqlwrite R The SQL command to write data into the loopbackout connection. Use {col1} to specify the name of a column. Ex: INSERT INTO table (id,name) VALUES ({id}, '{lastname}')
batchCount Number of rows to send at a time 1

SQL Call

exec bsc.csv.Pump 'enzoloopback', 'loopbackout', 'exec bsc.Files.ReadZip ''myDef'', ''\\myserver\myshare\myfile.zip'', null, ''file.ext'' '


Http Call

/bsc/csv/pump

Output Columns

Int64 readNumber of rows read.
Int64 writtenNumber of rows written.
PumpBulk Pumps the content of a data source into a SQL Server table (BulkImport) using a loopback connection details 

Arguments

Name Description Default
loopbackin R The name of the loopback pointing to a database or an Enzo server that will be used to fetch data. Send an empty string to use the current Enzo.
loopbackout R The name of the loopback pointing to a database or an Enzo server where the data will be inserted. Send an empty string to use the current Enzo.
sqlread R The SQL command to fetch data from the loopbackin connection.
tableName R The name of the table to bulk import the data into (three part name: database.schema.table)
batchCount Number of rows to send at a time 1

SQL Call

exec bsc.csv.PumpBulk 'enzoloopback', 'loopbackout', 'exec bsc.Files.ReadZip ''myDef'', ''\\myserver\myshare\myfile.zip'', null, ''file.ext'' '


Http Call

/bsc/csv/pumpbulk

Output Columns

Int64 readNumber of rows read.
Int64 writtenNumber of rows written.
ReadCSV Read a text file using the passed definition; determines the type of file based on the connection type details 

Arguments

Name Description Default
definitionName Name of previously stored file definition, or a dynamic Json definition. Can include a secondary definition name when using protocol adapters (like FTP): config,secondaryconfig
path UNC, FTP or URI path of TXT file to search
filePattern The search string to match against the file-system entries in the ZIP file. Wildcard specifiers are: *: Zero or more characters, ?: Exactly one character
filter Optional WHERE clause to filter results
maxRows Maximum number of rows to read. Default is all.
startingRow First row to begin reading. Default is first.
maxErrors Maximum number of errors before aborting. Default is 100.

SQL Call

exec bsc.csv.ReadCSV 'myDef', '\\myserver\myshare\myfile.txt'
exec bsc.csv.ReadCSV '{"FirstRowNames":true, "Delimiter":"\\t", "QuotedText":false}', '\\\\myserver\\myshare\\myfile.txt'
select * from bsc.Csv.Data@myDef


Http Call

/bsc/csv/readcsv

Output Columns

Int32 RowNumRow number in file. File columns follow.
SetLoopBack Sets a connection string pointing to this Enzo, or another Enzo, or a database server. details 

Arguments

Name Description Default
name R The name of the loopback definition
connection R The connection string to Enzo used for loopback calls

SQL Call

EXEC Csv.SetLoopBack 'localenzo', 'server=localhost,9550;uid=sa;pwd=password;initial catalog=bsc'


Http Call

/bsc/csv/setloopback

Output Columns

This call does not return data.

SetVar Sets a session variable details 

Arguments

Name Description Default
name R Session variable name
value New value for the session variable

SQL Call

EXEC CSV.SetVar 'DefaultColumnDelimiters', ','


Http Call

/bsc/csv/setvar

Output Columns

This call does not return data.

UpdateDefinition Update a definition for loading rows from text files details 

Arguments

Name Description Default
name R Definition name
description Purpose of the definition
columnDelimiter Column delimiter token ,
firstRowNames Indicates if first row of files contains the column names False
referenceFile Path to a reference file to read first row for column names
columnNames Optional comma separated list of column names and Enzo types
columnWidths For fixed width column files. Column widths separated by commas, e.g. "15,25,25,10".
trimWhiteSpace Trim the trailing whitespace from strings
quotedText Indicates whether text columns are quoted False
commentToken The token to use for commented out rows #
defaultPath Default path, UNC, URL, or URI to use if none is supplied
defaultSubDir Default ZIP subdirectory to use if none is supplied and using a ZIP file
defaultFilePattern Default search string to match against the file-system entries in a ZIP file. ONLY the first matching file is selected. Wildcard specifiers are: *: Zero or more characters, ?: Exactly one character
userId Default User ID for login
password Default password for login
filePassword Default password for decrypting files, e.g. ZIP.
defaultMaxRows Default number of rows to read if none is supplied. Default is all rows. -1
defaultStartingRow Default first row to begin reading if none is supplied. Default is the first row. -1
defaultMaxErrors Default maximum number of errors before aborting if none is supplied. 100
defaultTimeout Default timeout in seconds. Default is infinity (-1) -1
defaultAuthType Default authentication security if none is supplied. NULL: None, B: Basic, W: Windows, K: Kerberos
defaultConnectionType Default connection type if none is supplied: null, FTP or FTPS
defaultPort Default port to use for connection. Default is the that of the protocol
defaultFtpIsSsl Default FTP connection uses SSL if none is specified. Only applies if an FTP defaultConnectionType is specified. False
defaultIsPassive Default FTP passive state to use when performing download if none is specified. Only applies if an FTP defaultConnectionType is specified. False
defaultFilter Default WHERE clause to filter results if none is specified
accountId The account id of the user (send null for current user)

SQL Call

exec csv.UpdateDefinition 
	'name',	-- Definition name

	-- Description for definition
	'description',

	',',			-- Column delimiter token
	0,				-- Indicates if first row of files contains the column names
	null,			-- Path to a reference file to read first row for column names

	-- Optional comma separated list of column names with SQL types
	'col1 varchar(max), col2 int',
	
	-- For fixed width column files. Column widths separated by commas, e.g. '15,25,25,10'.
	null,

	0,				-- Trim the trailing whitespace from strings
	1,				-- Indicates whether text columns are quoted
	null,			-- The token to use for commented out rows

	-- Path, UNC, URL, or URI to use if none is passed in as an argument
	'ftp://somesite.com/path/myfile.csv',

	null,			-- Default ZIP subdirectory to use if none is supplied and using a ZIP file
	null,			-- Search string to match against the file-system entries in a ZIP 
					--	file. ONLY the first matching file is selected. Wildcard specifiers 
					--	are: *: Zero or more characters, ?: Exactly one character
	'anonymous',	-- Default User ID for login
	'myname@mysite.com', -- Default password for login
	null,			-- Password for decrypting files, e.g. ZIP.
	null,			-- Number of rows to read.  Default is all rows.
	null, 				-- First row to begin reading. Default is the first row.
	null, 			-- Maximum number of errors before aborting. Default is 100 errors.
	null, 			-- Connection timeout in seconds. Default is infinity (-1)
	null,			-- Authentication security
					--		NULL: None, B: Basic, W: Windows, K: Kerberos
	'FTP', 			-- Connection type. The following protocols are
					--	supported: FILE, FTP, HTTP (includes https), IMAP, SMTP, SFTP, 
					--	TFTP (Trival FTP)
	null, 			-- Port to use for connection. Default is the that of the protocol.
	null, 			-- FTP connection uses SSL (FTP only)
	1, 				-- FTP passive state to use when performing download. (FTP only)
	null			-- WHERE clause to filter results if none is specified


Http Call

/bsc/csv/updatedefinition

Output Columns

This call does not return data.