Connection String Attributes
PreviousNext

Introduction

This page lists all possible attributes you can use in ConnectionString or WITH clause of SQL Query. If you are not sure how to write correct connection string then perform following steps.
  1. Search for odbcad32 in the start menu. Click to launch it.
  2. Configure desired properties in the grid and check Query Preview to make sure everything is correct.
  3. Click on [Copy Connection String] button to generate full connection string.

Connection String Examples

Syntax: (Direct Connection string without DSN)
Driver={ZappySys Azure Blob CSV Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;] 
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]

Connection String Attributes

AttributeLabelDescription
AccessMode
(Setting of CsvConfigurationParams)
AccessModeDefines how to read data from file path/URL or direct string

Available Options

Option Description
DirectPath DirectPath
DirectValue DirectValue
ConnectionStringFile
(Setting of CsvConfigurationParams)
ConnectionStringFile
Data
(Setting of CsvConfigurationParams)
DirectValueDirect String you like to use as a datasource. This attribute always takes precedence if both Data and Src defined (i.e. File/Url). You can also use AccessMode=1 along with Data='...' in WITH clause to use Direct String rather than URL/File.
EnableCustomReplace
(Setting of CsvConfigurationParams)
EnableCustomReplaceEnables custom search / replace in the document text after its read from the file/url or direct string. This replace operation happens before its parsed. This option can be useful for custom escape sequence in source document which is causing issue in the parser. You can replace such unwanted characters fore parser starts parsing the text.
IgnoreContentLengthHeaderForGzipResponse
(Setting of CsvConfigurationParams)
IgnoreContentLengthHeaderForGzipResponse
ReplaceWith
(Setting of CsvConfigurationParams)
ReplaceWithString you like to replace with (Only valid when EnableCustomReplace option is turned on). If you added --regex or --regex-ic at the end of your SearchFor string then ReplaceWith can use special placeholders (i.e. $1, $2...) based on regular expression groups. For example you SearchFor=(\w+)(@\w+.com) to search for emails then to mask emails you can something like this for ReplaceWith = ****$2 (where $2 is domain part and $1 is before @)
SearchFor
(Setting of CsvConfigurationParams)
SearchForString you like to search for (Only valid when EnableCustomReplace option is turned on). If you want to enable Regular Expression pattern search then add --regex or --regex-ic (for case-insensitive search)  at the end of your search string (e.g. ORDER-\d+--regex OR ORDER-\d+--regex-ic (case-insensitive search) )
Cache Settings
CacheEntryTtl
(Setting of CsvConfigurationParams)
Cache entry lifetime, secThis property indicates how many seconds data is kept in cache (in case caching is enabled)
CacheFileLocation
(Setting of CsvConfigurationParams)
Cache file locationThis property allows you to set cache file location (you have to set Cache persistence mode to Storage)
CacheStorage
(Setting of CsvConfigurationParams)
Cache storageSpecifies cache storage - in-memory only or local data file

Available Options

Option Description
Memory Memory
File File
CachingMode
(Setting of CsvConfigurationParams)
Cache modeEnables or disables cache usage for metadata / data

Available Options

Option Description
Disabled Caching disabled
Metadata Cache metadata
All Cache all
CSV Settings
AllowComment
(Setting of CsvConfigurationParams)
AllowCommentAllow comment lines which can be skipped by parser. When comment line found row is skipped. See CommentCharacter to configure first character for commented line.
ColumnDelimiter
(Setting of CsvConfigurationParams)
ColumnDelimiterColumn delimiter for data you like to parse. To use custom delimiter enter 4-digit hex string starting with \x (e.g. you can enter \x0009 for Tab character). For multiple characters repeat group. e.g. \x00090009 if you need two tabs.
CommentCharacter
(Setting of CsvConfigurationParams)
CommentCharacterAllow lines with comment. When comment line found row is skipped. See LineCommentCharacter property to configure first character for commented line.
HasColumnHeaderRow
(Setting of CsvConfigurationParams)
HasColumnHeaderRowColumn delimiter for data you like to parse.
IgnoreBlankLines
(Setting of CsvConfigurationParams)
IgnoreBlankLines
IgnoreQuotes
(Setting of CsvConfigurationParams)
IgnoreQuotesIgnore quote character and consider it part of actual value
QuoteCharacter
(Setting of CsvConfigurationParams)
QuoteCharacterQuote character for quoted values.
SkipEmptyRecords
(Setting of CsvConfigurationParams)
SkipEmptyRecordsWhen this option is enabled, any row with empty values in all fields is skipped (e.g. , , , , ).
SkipHeaderCommentRows
(Setting of CsvConfigurationParams)
SkipHeaderCommentRowsTotal rows you like to skip before header row. If its header less file then skip initial N rows (before any data row).
SkipRows
(Setting of CsvConfigurationParams)
SkipRowsTotal data rows you like to skip (after header row)
ThrowErrorOnColumnCountMismatch
(Setting of CsvConfigurationParams)
ThrowErrorOnColumnCountMismatchThrow error if record has different number of columns than actual columns detected based on first row
ThrowErrorOnNoRecordFound
(Setting of CsvConfigurationParams)
ThrowErrorOnNoRecordFoundThrow error if no record found or file is blank
TreatBlankAsNull
(Setting of CsvConfigurationParams)
TreatBlankAsNull
TrimFields
(Setting of CsvConfigurationParams)
TrimFieldsTrim value for each field if whitespace found before or after
TrimHeaders
(Setting of CsvConfigurationParams)
TrimHeadersTrim column names if whitespace found before or after name
Encoding / Culture Settings
CharacterSet
(Setting of CsvConfigurationParams)
CharacterSetCharacter set for text (e.g. utf-8 )
Culture
(Setting of CsvConfigurationParams)
CultureCulture code (e.g. pt-BT). This helps to parse culture specific number formats (e.g. In some culture you may have comma rather than decimal points 0.1 can be 0,1)
Encoding
(Setting of CsvConfigurationParams)
EncodingEncoding of source file

Available Options

Option Description
Default Default
ASCII ASCII
UTF8 UTF-8
UTF16 UTF-16 LE (i.e. Unicode Little Endian)
UTF32 UTF-32
UTF8WithoutBOM UTF-8 Without BOM
UTF32WithoutBOM UTF-32 Without BOM
UTF7 UTF-7
UTF7WithoutBOM UTF-7 Without BOM
UTF16WithoutBOM UTF-16 Without BOM
BigEndian UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM UTF-16 BE Without BOM
Error Handling Settings
ConvErrorHandlingMode
(Setting of CsvConfigurationParams)
Error handling for data conversionDetermines whether some data conversion errors are ignored or stop query processing.

Available Options

Option Description
ThrowError Stop execution on data conversion error
Ignore Ignore data conversion errors
Log Settings
LoggingLevel
(Setting of CsvConfigurationParams)
Log levelSets level of messages logged

Available Options

Option Description
None None
Error Error
Debug Debug
Info Info
LogPath
(Setting of CsvConfigurationParams)
Log file locationSets location for the log file (empty value means no log written)
LogSize
(Setting of CsvConfigurationParams)
Maximum log file size (Mb)Sets limit for log file size (0 means no limit, old log entries will be discarded if size limit is reached)
GroupName
(Setting of ColumnsMetadata)
Group name
OverrideMode
(Setting of ColumnsMetadata)
OverrideMode
Metadata Settings
MetaScanMode
(Setting of CsvConfigurationParams)
Metadata Scan Mode / MultiplierMetadata guess mode / multiplier to detect length by scanning rows defined in

Available Options

Option Description
Auto Auto
Strict Strict - Exact length
Guess2x Guess2x - 2 times bigger
Guess3x Guess3x - 3 times bigger
Guess4x Guess4x - 4 times bigger
TreatAsUnicodeString Set all columns as string
Guess10x Guess10x - 10 times bigger
TreatStringAsMaxLength Set string columns with MAX Length - i.e. DT_WSTR(4000)
TreatStringAsBlob Set string columns as BLOB - i.e. DT_NTEXT
MetaTreatStringAsAscii
(Setting of CsvConfigurationParams)
Treat string as ASCIIWhen this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)
RowsToScanForMetadata
(Setting of CsvConfigurationParams)
Number of rows scanned to collect metadataIf metadata isn't defined for query the driver will try to scan some rows to guess fields parameters
TreatBlankBoolAsNull
(Setting of CsvConfigurationParams)
Treat blank BOOLEAN value as NULL
TreatBlankDateAsNull
(Setting of CsvConfigurationParams)
Treat blank DATE/TIME value as NULL
TreatBlankNumberAsNull
(Setting of CsvConfigurationParams)
Treat blank NUMERIC value as NULL
Other Settings
DateParseHandling
(Setting of CsvConfigurationParams)
Date data typeSpecifies how dates are parsed.

Available Options

Option Description
None Keep date as string
DateTime Convert to DateTime (Timezone lost)
DateTimeOffset Convert to DateTimeOffset (Preserve Time zone)
EnableBigNumberHandling
(Setting of CsvConfigurationParams)
Enable Big Number HandlingWhen this option is checked, sytem allows to parse large number (Higher than Int64) in JSON document (i.e. { id: 76000009353950433247780 } ). If you do not turn on this option then it may throw error if large number is encountered. This option is SLOW becuase it has to perform additional checks in order to avoid Large number overflow error so do not turn on unless you getting Large number error.
FloatParseHandling
(Setting of CsvConfigurationParams)
FloatParseHandlingSpecifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.

Available Options

Option Description
Double Default (Double [~15-17 digits])
Decimal Decimal (High Precision / Scale [~28-29 digits] )
Recursive
(Setting of CsvConfigurationParams)
Scan files recursivelyScan files recursively when you read from local  files (i.e. include files from sub folders).
Output Settings
DateFormatString
(Setting of CsvConfigurationParams)
Date formatSpecifies date format
DefaultRowLimit
(Setting of CsvConfigurationParams)
Default data row limit (i.e. LIMIT / TOP clause)Use this option if you like to limit number of rows fetched by any query. If you use LIMIT clause in the query then this option is ignored.
EnableArrayFlattening
(Setting of CsvConfigurationParams)
Enable Array FlatteningEnables deep array flattening for selected filtered hierarchy. When you turn on this property it will flatten each property of each array item and expose as column (e.g. If you have Filter set as $.customers[*] and for each customer you have an array of Addresses then you may see output columns like Addresses.1.City, Addresses.1.State, Addresses.2.City, Addresses.2.State .... Addresses.N.City, Addresses.N.State). You can control how many array items you want to flatten by setting MaxArrayItemsToFlatten property.
EnablePivot
(Setting of CsvConfigurationParams)
Enable PivotWhen this property is true then Column is converted to Row. Pivoted names will appear under  Pivot_Name column and values will appear under Pivot_Value field.
EnableRawOutputModeSingleRow
(Setting of CsvConfigurationParams)
Enable Raw Document - Single Row (Disable parser, allow any format)Enable Raw Document Output Mode for any format (i.e. XML, Html, Text, Json). Unlike other option EnableRawOutputMode, this option doesn't invoke parser to extract documents by finding row terminator. It will source string as row value in single row/ single column.
FileCompressionType
(Setting of CsvConfigurationParams)
FileCompressionTypeCompression format for source file (e.g. gzip, zip)

Available Options

Option Description
None None
GZip GZip
Zip Zip
TarGZip TarGZip
MaxArrayItemsToFlatten
(Setting of CsvConfigurationParams)
Max Array Items To FlattenMaximum number of array items to flatten. inner array flattening. Adjust this property to control how many columns being generated. This option is ignored if you set EnableArrayFlattening=false
RawOutputDataRowTemplate
(Setting of CsvConfigurationParams)
RawOutputDataRowTemplate
RawOutputExtractMode
(Setting of CsvConfigurationParams)
RawOutputExtractMode

Available Options

Option Description
None None
Json Json
Xml Xml
Regex Regex
RawOutputFilterExpr
(Setting of CsvConfigurationParams)
RawOutputFilterExprUse this property to define one or multiple expressions (i.e. JsonPath,XPath,Regex) to extract information from source. This property is only valid when EnableRawOutputModeSingleRow=true and RawOutputDataRowTemplate is set.
SaveContentAsBinary
(Setting of CsvConfigurationParams)
SaveContentAsBinary
Query Engine Temp Storage
TempStorageMode
(Setting of CsvConfigurationParams)
Intermediate results storageUse [Disk] if number of resulting rows or row data size is large

Available Options

Option Description
Memory Memory
Disk Disk
Request Settings
DataPath
(Setting of CsvConfigurationParams)
Data Source (URL or File Path)Data source URL or File Path (e.g. http://abc.com/api/get --or-- c:\data\cust01.json  --or-- c:\data\cust*.json ). If data source is file then wildcard pattern can be also used to read multiple files (e.g. c:\data\cust*.json will read all files that match wildcard)


Copyrights reserved. ZappySys LLC.