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

  User Guides
     Sharding
     Sharding Overview

     SharePoint

   Adapters
    All Adapters
    ActiveCampaign
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Bing Search
    ConstantContact
    Couchbase
    CSV
    DB (RDBMS)
    Dropbox
    Files
    Flight Aware
    FreshBooks
    FTP
    Geonames
    GoogleDrive
    GoogleSearch
    GoogleSheet
    GreenMile
    GpsInsight
    HttpEndpoint (REST)
    Intacct
    Insteon
    Loggly
    MailChimp
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    OneDrive
    OneDrive365 (Business)
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    Slack
    SQL Server
    SQLX
    Sudoku
    Teams
    Threading
    Twilio
    Twitter
    VellemanDMX
    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



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.


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



Documentation


Configuration Settings



This adapter does not use configuration settings.



Handlers



CreateDefinition
Definitions
Create a definition for loading rows from text files details 

Arguments

Name Description Default
name R
description
columnDelimiter ,
firstRowNames False
referenceFile
columnNames
columnWidths
trimWhiteSpace
quotedText False
commentToken #
defaultPath
defaultSubDir
defaultFilePattern
userId
password
filePassword
defaultMaxRows -1
defaultStartingRow -1
defaultMaxErrors 100
defaultTimeout -1
defaultFilter
accountId

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
Definitions
Delete a definition details 

Arguments

Name Description Default
name R
accountId

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

SQL Call

EXEC CSV.GetVar 'DefaultColumnDelimiters'


Http Call

/bsc/csv/getvar

Output Columns

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

Arguments

Name Description Default
pattern
accountId

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
DateTime RowCreated
DateTime RowUpdated
ListSupportedColumnTypes
SupportedColumnTypes
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
loopbackout R
sqlread R
sqlwrite R
batchCount 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.
ReadCSV
Data
Read a text file using the passed definition; determines the type of file based on the connection type details 

Arguments

Name Description Default
definitionName
path
filePattern
filter
maxRows
startingRow
maxErrors

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.
SetVar
Sets a session variable details 

Arguments

Name Description Default
name R
value

SQL Call

EXEC CSV.SetVar 'DefaultColumnDelimiters', ','


Http Call

/bsc/csv/setvar

Output Columns

This call does not return data.

UpdateDefinition
Definitions
Update a definition for loading rows from text files details 

Arguments

Name Description Default
name R
description
columnDelimiter ,
firstRowNames False
referenceFile
columnNames
columnWidths
trimWhiteSpace
quotedText False
commentToken #
defaultPath
defaultSubDir
defaultFilePattern
userId
password
filePassword
defaultMaxRows -1
defaultStartingRow -1
defaultMaxErrors 100
defaultTimeout -1
defaultFilter
accountId

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.

WriteCSV
write
Writes SQL output content to a text file details 

Arguments

Name Description Default
definitionName
sqlread R
path
overwrite False
sourceConnection
delimiter
positions
quotedText False
firstRowNames False
showOutput False
flattenText False
sourceTimeout 3000

SQL Call

exec bsc.csv.WriteCSV null, 'SELECT * FROM SharePoint.Lists', 'c:\myfile.txt' 
exec bsc.csv.WriteCSV 'config', 'SELECT * FROM SharePoint.Lists'
exec bsc.csv.WriteCSV 'config', 'SELECT * FROM SharePoint.Lists', 'dropbox://folder/output.txt', 1
exec bsc.csv.WriteCSV 'spconfig,dropboxconfig', 'SELECT * FROM SharePoint.Lists', 'dropbox://folder/output.txt', 1


Http Call

/bsc/csv/writecsv

Output Columns

Int64 rowCount
Double elapsedSec
String fileName
String output