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.

Want to try it in Azure? Click here to learn more or click here to create a new Enzo Server RTM 3.1 Azure VM now.

Want to try it in AWS? Click here to learn more or click here to create a new Enzo Server RTM 3.1 AWS VM now.

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 Result

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

Value 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 SqlType	SQL type
String DotnetType	Intermediate .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 read	Number of rows read.
Int64 written	Number 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 RowNum

Row 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

BLOGS AND VIDEOS

IN THE NEWS

OTHER

CSV Adapter

Configuration Settings

Handlers

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

Arguments

SQL Call

Http Call

Output Columns

PLATFORM

SOLUTIONS

RESOURCES

COMPANY