|
Connection String Attributes
|
 
|
|
Connection String Attributes
|
|
Driver={ZappySys SFTP CSV Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
| Attribute | Label | Description | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AccessMode (Setting of CsvConfigurationParams) | AccessMode | Defines how to read data from file path/URL or direct string
| ||||||||||||||||||||||||||
| ConnectionStringFile (Setting of CsvConfigurationParams) | ConnectionStringFile | |||||||||||||||||||||||||||
| Data (Setting of CsvConfigurationParams) | DirectValue | Direct 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) | EnableCustomReplace | Enables 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) | ReplaceWith | String 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) | SearchFor | String 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, sec | This property indicates how many seconds data is kept in cache (in case caching is enabled) | ||||||||||||||||||||||||||
| CacheFileLocation (Setting of CsvConfigurationParams) | Cache file location | This property allows you to set cache file location (you have to set Cache persistence mode to Storage) | ||||||||||||||||||||||||||
| CacheStorage (Setting of CsvConfigurationParams) | Cache storage | Specifies cache storage - in-memory only or local data file
| ||||||||||||||||||||||||||
| CachingMode (Setting of CsvConfigurationParams) | Cache mode | Enables or disables cache usage for metadata / data
| ||||||||||||||||||||||||||
| CSV Settings | ||||||||||||||||||||||||||||
| AllowComment (Setting of CsvConfigurationParams) | AllowComment | Allow 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) | ColumnDelimiter | Column 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) | CommentCharacter | Allow lines with comment. When comment line found row is skipped. See LineCommentCharacter property to configure first character for commented line. | ||||||||||||||||||||||||||
| HasColumnHeaderRow (Setting of CsvConfigurationParams) | HasColumnHeaderRow | Column delimiter for data you like to parse. | ||||||||||||||||||||||||||
| IgnoreBlankLines (Setting of CsvConfigurationParams) | IgnoreBlankLines | |||||||||||||||||||||||||||
| IgnoreQuotes (Setting of CsvConfigurationParams) | IgnoreQuotes | Ignore quote character and consider it part of actual value | ||||||||||||||||||||||||||
| QuoteCharacter (Setting of CsvConfigurationParams) | QuoteCharacter | Quote character for quoted values. | ||||||||||||||||||||||||||
| SkipEmptyRecords (Setting of CsvConfigurationParams) | SkipEmptyRecords | When this option is enabled, any row with empty values in all fields is skipped (e.g. , , , , ). | ||||||||||||||||||||||||||
| SkipHeaderCommentRows (Setting of CsvConfigurationParams) | SkipHeaderCommentRows | Total 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) | SkipRows | Total data rows you like to skip (after header row) | ||||||||||||||||||||||||||
| ThrowErrorOnColumnCountMismatch (Setting of CsvConfigurationParams) | ThrowErrorOnColumnCountMismatch | Throw error if record has different number of columns than actual columns detected based on first row | ||||||||||||||||||||||||||
| ThrowErrorOnNoRecordFound (Setting of CsvConfigurationParams) | ThrowErrorOnNoRecordFound | Throw error if no record found or file is blank | ||||||||||||||||||||||||||
| TreatBlankAsNull (Setting of CsvConfigurationParams) | TreatBlankAsNull | |||||||||||||||||||||||||||
| TrimFields (Setting of CsvConfigurationParams) | TrimFields | Trim value for each field if whitespace found before or after | ||||||||||||||||||||||||||
| TrimHeaders (Setting of CsvConfigurationParams) | TrimHeaders | Trim column names if whitespace found before or after name | ||||||||||||||||||||||||||
| Encoding / Culture Settings | ||||||||||||||||||||||||||||
| CharacterSet (Setting of CsvConfigurationParams) | CharacterSet | Character set for text (e.g. utf-8 ) | ||||||||||||||||||||||||||
| Culture (Setting of CsvConfigurationParams) | Culture | Culture 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) | Encoding | Encoding of source file
| ||||||||||||||||||||||||||
| Error Handling Settings | ||||||||||||||||||||||||||||
| ConvErrorHandlingMode (Setting of CsvConfigurationParams) | Error handling for data conversion | Determines whether some data conversion errors are ignored or stop query processing.
| ||||||||||||||||||||||||||
| Log Settings | ||||||||||||||||||||||||||||
| LoggingLevel (Setting of CsvConfigurationParams) | Log level | Sets level of messages logged
| ||||||||||||||||||||||||||
| LogPath (Setting of CsvConfigurationParams) | Log file location | Sets 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 / Multiplier | Metadata guess mode / multiplier to detect length by scanning rows defined in
| ||||||||||||||||||||||||||
| MetaTreatStringAsAscii (Setting of CsvConfigurationParams) | Treat string as ASCII | When 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 metadata | If 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 type | Specifies how dates are parsed.
| ||||||||||||||||||||||||||
| EnableBigNumberHandling (Setting of CsvConfigurationParams) | Enable Big Number Handling | When 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) | FloatParseHandling | Specifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.
| ||||||||||||||||||||||||||
| Recursive (Setting of CsvConfigurationParams) | Scan files recursively | Scan files recursively when you read from local files (i.e. include files from sub folders). | ||||||||||||||||||||||||||
| Output Settings | ||||||||||||||||||||||||||||
| DateFormatString (Setting of CsvConfigurationParams) | Date format | Specifies 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 Flattening | Enables 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 Pivot | When 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) | FileCompressionType | Compression format for source file (e.g. gzip, zip)
| ||||||||||||||||||||||||||
| MaxArrayItemsToFlatten (Setting of CsvConfigurationParams) | Max Array Items To Flatten | Maximum 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 |
| ||||||||||||||||||||||||||
| RawOutputFilterExpr (Setting of CsvConfigurationParams) | RawOutputFilterExpr | Use 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 storage | Use [Disk] if number of resulting rows or row data size is large
| ||||||||||||||||||||||||||
| 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) | ||||||||||||||||||||||||||