• Specified Directory: This option will cause the file collector to use the Import File Location (specified in System Configuration on the System Directories tab) as the root input directory when looking for files to load.
• Managed File: This option will cause the file collector to use the File Upload Directory (specified in System Configuration on the System Directories tab) as the root input directory when looking for files to load.

• Comma Separated Values: fields are delimited by a comma, (or other character).
• Fixed Length Records: fields have a fixed column width.
• Binary File: Data is extracted from the file using a Binary File Grammar (in XML) specified in the File Format Description tab.
• File Details Only: Only attribute details about the file itself will be available.
• Excel Spreadsheet: Data is extracted from the an excel spreadsheet supporting a .xls or .xlsx extension.

  .xlsx type excel files containing in excess of 10,000 rows are not supported by PhixFlow and should not be imported using a File Collector.

• XML File: Data is extracted from an XML file
• HTML File: Data is extracted from an HTML file

The next sequence number expected to be found within the name of the file being imported.

This field is only available if File Location Strategy = All Files in Folder.

• All Files in Folder: read all files matching the pattern specified in File Pattern Expression.
• Read Paths: read in file path names from a collector or stream.

  This input collector or stream must be attached to the file collector by a pipe. The attribute of the input stream or collector which contains the file path names is specified in the field: File Name Attribute.

  Each file path name is interpreted as a pathname relative to the Import Directory. A path name may be a simple file name, or it may have multiple levels of directory, including compressed files (which will be interpreted as directories). The directory separator must be '/' (forward-slash), and not '\' (back-slash), even on a Windows platform.

  E.g. 'abc.csv', 'dir1/dir2.zip/abc.csv'

• Read Names: read in file locations from a collector or stream. This input collector or stream must be attached to the file collector by a pipe. The attribute of the input stream or collector which contains the file locations is specified in the field: File Name Attribute.
  Read Names is deprecated.

If the Source Type is Specified Directory the result of evaluating the Input Directory Expression is appended to the directory specified in the System Configuration File Upload Location to give the input directory from which files are read.

If the Source Type is Managed file then this will contain a non editable value of "in"
This will be appended to the combined path of System Configuration File Upload Directory and Tag to give the input directory from which files are read.

If File Location Strategy = All Files in Folder PhixFlow will look in this directory to find files matching the pattern specified in File Pattern Expression.

If File Location Strategy = Read Names this is added to the start of the file location read from the file name attribute.

Note that because this is an expression field, if you supply a simple directory definition in plain text it must be surrounded by quotes. Also, directory separators must be / and not \, even if the file is being read from a Windows platform.

E.g. "C:/data/address/input/".

This field is an expression used to identify valid sub-directories of the input directory. This expression must itself resolve to a "pattern matching" Regular Expression.

If a Directory Pattern Expression is provided then PhixFlow will not only check the Input Directory for files but will also check all sub-directories of the Input Directory. Each file found will then not only have its name checked against the File Pattern Expression but will also have the relative path from the Input Directory to the file (referred to as the sub-directory path) checked against the Directory Pattern Expression.

For example, suppose the Input Directory has the sub-directories: 'region1/teamA'; 'region1/teamB'; 'region2/teamA'. If you want all the files across all regions for teamA, but not teamB, then you could use the following Directory Pattern Expression to pick out just the files for teamA:

".*/teamA/"

Alternatively, if you wanted all the files for all teams in region 1 only, you could use the following Directory Pattern Expression:

"region1/.*"

Regular expression rules are used to perform this match rather than the sort of pattern matching rules you might be used to when listing files. For example:

• To match any string of characters, you must use ".*" and not "*"
• To match a "." you must use "\\." and not "." (which means any character)
• You must use forward slashes "/" instead of backslashes "\" for directory separators

A number of internal variables are available in these expressions:

• _fromDate: the start date of the period of the stream being processed.
• _toDate: the end date of the period of the the stream being processed.

Note that there are also a number of predefined compressed file expressions that will always be checked to determine if a file within a valid sub directory is actually a compressed file. If so then this file will assumed to be a valid compressed file and hence will be recursed into as if it was a standard matching directory. Please see Compressed Files for a list of valid compressed file expressions.

This field is an expression that can be used to exclude certain sub-directories found by the Directory Pattern Expression. As with the Directory Pattern Expression, this expression must itself resolve to a Regular Expression.

For example, suppose the Input Directory has the sub-directories: 'region1/teamA'; 'region1/teamB'; 'region2/teamA'. If you want all the files across all regions for teamA, but not teamB, then you could use the following Directory Pattern Expression to find all files:

".*"

combined with the following Exclude Dir. Pattern Expr to exclude those for teamB:

".*/teamB/"

Regular expression rules are used to perform this match rather than the sort of pattern matching rules you might be used to when listing files. For example:

• To match any string of characters, you must use ".*" and not "*"
• To match a "." you must use "\\." and not "." (which means any character)
• You must use forward slashes "/" instead of backslashes "\" for directory separators

A number of internal variables are available in these expressions:

• _fromDate: the start date of the period of the stream being processed.
• _toDate: the end date of the period of the the stream being processed.

• _fromDate: the start date of the period of the stream being processed.
• _toDate: the end date of the period of the the stream being processed.
• %SEQ%: the current sequence number.

Examples:

"inputRecords.txt"

will read files called "inputRecords.txt" from the input directories.

".*"

will read all files in the input directories.

".*\\.txt"

will read all files in the input directories with the extension ".txt"

"teamA.*"

will read all files in the input directories starting with "teamA."

"record_" + toString(now(),"yyyy-MM-dd") + "\\.txt"

will read files in the input directories with the format "record_yyyy-MM-dd.txt", where yyyy-MM-dd is the current date. E.g. "record_2013-03-26.txt".

"("+listToString(_context.f,"|")+")"

will read files with name contained in the list of files uploaded by the Stream Action which caused the File Collector to run, but only if a Context Value called 'f' is set in the Action and its value expression is '_files'

If the collector is configured to read files in sequence, this field is ignored and a single file reader is used.

• Error: unreadable directories will be reported, and if any are found, the file search will fail.
• Warning: unreadable directories will be reported, but the file search will continue unaffected.
• Ignore: unreadable directories will be silently ignored

If this flag is set then PhixFlow will not throw an error if the record being read contains fewer columns than expected. If this flag is not set then an error will be reported if there are too few columns.

If this flag is set then PhixFlow will not throw an error if the record being read contains more columns than expected. If this flag is not set then an error will be reported if there are too many columns.

This field is only available if File Type = Excel Spreadsheet.

This field is an expression that must evaluate to a list of ranges with the format "WorksheetName!TopLeftCell:BottomRightCell"

If this field is left blank, PhixFlow will read the first worksheet it finds in the excel file (even if this is a hidden sheet) with a range covering the whole sheet.

E.g. if just a single range is needed: "DailyCallsSheet!A1:G100"

E.g. if a list of ranges is required: ["DailyCallsSheet!A1:G100", "A1:B20", "Calls!A1:C100"]

Remember that in all cases PhixFlow will only read the columns that have been defined in the File Columns tab.

Because this field is an expression, the resulting list can be generated with any valid PhixFlow expression. You can also use the internal variable _worksheets which gives you the list of worksheets that PhixFlow found in the file. See the example below for how you might use this.

Examples

• All rows and columns in the default 1st worksheet:- leave this field empty as this is the default behaviour
• Specified columns only on the default 1st worksheet:- "A:C"
• Specified cell range only on the default 1st worksheet:- "B1:G10"
• Specified cell range on a specified worksheet:- "DailyCallsSheet!A2:F20"
• List of specified cell ranges on multiple worksheets:- ["DailyCallsSheet!A2:F20", "Calls!A1:C400", "Accounts!A5:F50"]
• Examine the list of worksheets that have been found and specify ranges for only certain sheets, if they exist:

do (    $rangeList = [],

    forEach($sheet, _worksheets,

        if ( listContains(["sheetA", "sheetB"], $sheet),

            addElement($rangeList, $sheet + "!A1:B10")

        )

    ),

    $rangeList

)

This expression will evaluate to the list of a maximum of two ranges, if both worksheets sheetA and sheetB exist - in this case, this is equivalent to ["sheetA!A1:B10", "sheetB!A1:B10"]. Crucially, if a sheet is not found, the range will not be included. This is important for error handling. If you specify a range that is not in the excel file PhixFlow will error. So if you are not sure that a worksheet will always be included, write an expression like this to check, and only specify the range when the sheet is found.

Related internal variables

See notes for the internal variables _worksheet and _range below. These can be used in stream attribute expressions to record the source worksheet and range for data you have loaded into PhixFlow.

Constraints

Note that if a worksheet is specified, then the full cell range must also be specified. Hence it is not possible to select a 'worksheet only' or 'columns only for a specified worksheet'. e.g DailyCallsSheet or DailyCallsSheet!A:C are not supported.

This checkbox should be ticked if all unsupported excel values such as #N/A, #REF! #DIV/0 etc should be ignored and replaced with null values during processing. In this case a single warning message will be displayed to the user once processing has completed stating the number of unsupported cell values found during the processing and a detailed message about the first unsupported cell value.

If this checkbox is unticked then each unsupported excel value will be reported as an individual warning/error in the console and processing will be terminated if the maximum number of errors/warnings is exceeded.

This field should be populated according to valid XPath syntax. Please see XPath Examples for how to use XPath expressions and how the returned data can be used and evaluated in the corresponding stream attribute expressions.

A list of target node names - that is, the names of nodes that will generate an output record. This field is an expression that must resolve to a single string, or a list of strings.

Example of a single target:

"DATA_RECORD"

Example of a list of targets:

["DATA_RECORD1","DATA_RECORD2","DATA_RECORD3"]

The _lineNumber attribute is not available for File Collectors of Type File Details Only

The last modified time of a single file residing within a .gz or a .tgz container can not be determined by phixflow, instead the datetime of when the corresponding gz/tgz container was created will be returned.

The size of a single file residing within a .gz or a .tgz container can not be determined by phixflow, instead a size of -1 will be returned.

The excel range expression that was used. The _range attribute is not available if the file type is not 'excel'.