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


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Best Practices
    Developer Guide
      Handler Columns
      Handler Options
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables



CSV Adapter








The CSV Adapter helps you read and export flat files using SQL commands and REST requests.


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



CreateDefinition
Definitions
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
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

/csv/createdefinition

Output Columns

Boolean ResultTrue/false indicator of creation success
DeleteDefinition
Definitions
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

/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

/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 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

/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

/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 CentralConnectionString pointing to a database or an Enzo server that will be used to fetch data.
loopbackout R The name of the CentralConnectionString pointing to a database or an Enzo server where the SQLWrite command will be executed.
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 (column replacement is case-sensitive). Ex: INSERT INTO table (id,name) VALUES ({id}, '{lastname}')
batchCount Number of rows to send at a time to the sqlWrite command 1

SQL Call

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


Http Call

/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 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

/csv/readcsv

Output Columns

Int32 RowNumRow number in file. File columns follow.
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

/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 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
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

/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 Optional name of source configuration setting to use for CSV layout (such as the FTP adapter); accepts destination config name using a comma separator (ex: config1,config2)
sqlread R The SQL command to fetch data from the source connection
path UNC, FTP or URI path of TXT file to write to; if left blank, a random file name will be generated; accepts adapter path notation (ex: azureStorage:$root/myfile.txt, or googledrive://folder1/file.txt )
overwrite When 1 the file will be overriden if it exists False
sourceConnection The name of the CentralConnectionString pointing to a database or an Enzo server that will be used to fetch data; if not provided, will use the current Enzo connection
delimiter Field separator for variable length output; overrides the configuration setting value
positions Comma-separated list of integers used to position values for fixed length output; overrides the configuration setting value
quotedText When true specifies that values should be quoted; overrides the configuration setting value False
firstRowNames When true adds a header to the file; overrides the configuration setting value False
showOutput Return the CSV output to the caller in the output column False
flattenText Removes carriage returns from the output to force a single line output False
sourceTimeout Timeout for reading data from source system when sourceConnection is provided 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

/csv/writecsv

Output Columns

Int64 rowCount
Double elapsedSec
String fileName
String output