Name

Enter a name for this file collector.

Auto Configuration

This tick box is available for a new file collector that has not been configured. It is ticked by default.

 Tick to load an Excel spreadsheet and automatically configure the file collector and table properties using the data in the spreadsheet; see Easy Loading for Excel Spreadsheets, below.

 Untick to switch off auto-configuration. More file collector properties become available so that you can specify details about the file(s) you want to load. You also need to add the table and pipe to the model.

Enabled

 Tick to indicate you have completed the properties and the file collector is ready to use.

Source Type

Select whether or not the file is already on the PhixFlow server.

Specified Directory

Use for files that are already stored on the PhixFlow server. By default, PhixFlow assumes files are in the System Configuration → System Directories → Import File Location, or a subdirectory of it. You must also specify the relative path to the file(s) in the Input Directory Expression.

If your file is not in the Import File Location, select Specified directory and tick Ignore base directory. In this case you must also specify the full path to the file(s) in the Input Directory Expression.

See also Input Directory Expression and Ignore base directory, below.

Managed File

Use for files that you have on your local machine or the network. 
 PhixFlow loads the file to its System Configuration → System Directories→ File Upload Directory. You can optionally specify a sub-directory of the File Upload Directory using the Tag property.

For a managed file, you must load the file before running the file collector.

If you have selected Auto Configuration, PhixFlow automatically starts the process for loading a managed file; see Easy Loading for Excel Spreadsheets, below.

Number of Header Lines

Enter the number of lines in the header of the file. These are ignored when reading the file. (This option is not available for Binary File or XML file types).

File Type

Select one of:

• 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.

For information about the PhixFlow internal variables you can use to specify the attributes you want, see File Collector Attributes, below.

Next Sequence

Available when File Location Strategy is All Files in Folder.

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

Allow Non-Scheduled Collection

 Tick to run the file collector as part of any analysis run that requires this data. 

 Untick to only run the file collector as part of a scheduled task.

FTP Site

Specify the FTP Site on which the import file is stored. If no site is specified then the file is assumed to be on the local machine. If a site is specified then all directory paths specified on this form should be the full path to the file since the base directory specified in system configuration is ignored (since the base directory is specific to the local machine).

File Location Strategy

Select one of:

• All Files in Folder: read all files matching the pattern specified in File Pattern Expression.

• Read File Paths: read in file path names from a collector or table.

This input database collector or table must be attached to the file collector by a lookup pipe with no index set.
The attribute of the input table or file collector that contains the file path names is specified in File Name Attribute. The value entered into this field should be plain text and not in quotes, for example myFilePaths.

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. Compressed files are interpreted as directories. On Linux and Windows platforms, the directory separator must be forward-slash /  not  backslash \ . Do not include a leading forward-slash /, for example: 'abc.csv' or 'dir1/dir2.zip/abc.csv'

• Read Names: This option is deprecated.

Read in file locations from a collector or table. This input collector or table must be attached to the file collector by a pipe. The attribute of the input table or collector which contains the file locations is specified in File Name Attribute.

Tag 

Available when Source Type field is Managed File.

Specify a sub-directory of System Configuration → System Directories →  File Upload Directory using string literals only. Do not use PhixFlow variables.

PhixFlow loads files into a directory whose full path is a combination of:

• File Upload Directory, for example  C:\LoadedManagedFiles

• Tag, for example CVFiles

•  Input Directory - this is automatically set to in for managed files.

For the example paths above, the file collector looks in the directory C:\LoadedManagedFiles\CVFiles\in

If you are creating a file collector to load email messages and/or attached files, you can specify a tag here if one has been provided in the subject line of the incoming emails. See Reading Data From an Email Account for further details.

Ignore Base Directory 

Available when Source Type is Specified Directory.

The base directory is set in System Configuration → System Directories → Import File Location.

 Untick to automatically prepend the Import File Location directory path to the directories specified in the file collector properties.

 Tick so that PhixFlow reads directories specified in the file collector properties as the full path to the file.

Input Directory
Expression 

Specify the location of the files you want to load.

When Source Type is Managed File

This field contains a read-only value of in.
PhixFlow reads files from the combined path: File Upload Directory + Tag +in

When Source Type is Specified Directory

Specify a path:
  - either to a directory relative to the Import File Location. PhixFlow reads files from the directory whose full path is a combination of Import File Location and the path specified here.
  - or, if Ignore Base Directory is ticked, the full path to the file(s).

"C:/data/address/input/accountValues"

"C:/data/address/input/" + _inputMultiplier

If File Location Strategy is:

•  All Files in Folder, PhixFlow looks in this directory to find files matching the pattern specified in File Pattern Expression.

• Read Names, this is added to the start of the file location read from the file name attribute.

See also System Configuration for File Upload Directory and Import File Location.

Directory
Pattern Expression

Enter a regular expression to specify one or more relative paths from the Input Directory to its sub-directories. PhixFlow looks in the input directory and its sub-directories for files whose names match the File Pattern Expression.

PhixFlow can recognise some compressed file types; see Handling Compressed Files, below. PhixFlow treats these compressed files as a matching sub-directory.

Internal Variables

You can use the following PhixFlow internal variables in this expression:

• _fromDate: the start date of the period of the table being processed.

• _toDate: the end date of the period of the the table being processed.

File
Pattern Expression 

Available when File Location Strategy is All Files in Folder.

Enter a regular expression that will match one or more files in the Input Directory, in order to generate a list of files to load.

PhixFlow uses the File Pattern Expression combined with the fields that specify the directories in which to look. This means PhixFlow:

• looks in directories specified in the Input Directory

• and looks in sub-directories specified the Directory Pattern Expression

• but ignores sub-directories that have been excluded by the Exclude Dir Pattern Expr.

If the specified directories contain no files that match the regular expression, the file collector will not load any files.

Internal Variables

You can use the following PhixFlow internal variables in this expression:

• _fromDate: the start date of the period of the table being processed.

• _toDate: the end date of the period of the the table being processed.

• %SEQ%: the current sequence number.

Exclude Dir.
Pattern Expr.

If the Directory Pattern Expression includes some sub-directories that you do not want to search, enter a regular expression that will exclude those sub-directories.

Internal Variables

You can use the following PhixFlow internal variables in this expression:

• _fromDate: the start date of the period of the table being processed.

• _toDate: the end date of the period of the the table being processed.

Archive Directory Expression

Optionally, enter an expression for a directory path. The expression must resolve to a Regular Expression.

The Archive Directory Expression is the location to which all files processed by the file collector will be written.

The Error Directory Expression is the location to which any files that cause an error during processing will be written.

Error Directory
Expression

Local Archive Directory

Available when FTP Site is specified.

Specify whether the archive directory is on the PhixFlow server (local) or on the original server.

Local Error Directory

Available when FTP Site is specified.

Specify whether the error directory is on the PhixFlow server (local) or on the original server.

Name

Enter the name of the column, which can contain any combination of letters, numbers and _ the underscore character.

If you use   Populate Attributes, PhixFlow uses data in the first row of a file to generate column names. Any invalid characters are stripped out. 

PhixFlow always uses this name to refer to this attribute.

Order

Enter a number that matches the column number in the input file. For example, if you want to extract the third, first and fifth column of data from a file, the three rows in this grid will have the order:

• 3

• 1

• 5

Type

Enter one of the data types:

• String

• Integer

• Float

• Date - see Supported Date/Datetime Format Patterns, below

• Datetime - see  Supported Date/Datetime Format Patterns, below

• TrueFalse -  see Supported TrueFalse Values, below

If you use   Populate Attributes , PhixFlow uses the type it recognises from the data, or String.

Length

Enter the maximum length of the field in the input file

• For String, the maximum length of the string

• For Integer, the maximum number of digits.

This is:

• required when File Type is Fixed Length Records

• optional when File Type is Comma Separated Values or Excel Spreadsheet, as only the field sizes of the fields in the table that this collector will write to are important.

If you use   Populate Attributes , PhixFlow uses the length of the longest record that PhixFlow has seen. As PhixFlow does not check all the records, longer fields may exist in the CSV file.

Maximum Files

Enter the maximum number of files that PhixFlow will process when the file collector runs.

Minimum Files

Enter the minimum number of files that PhixFlow expects to find when the file collector runs. If fewer files are found then this is treated as an error.

Max Records Per File

Enter the maximum number of records that PhixFlow will read from each file.

Errors Before Rollback

The maximum number of errors PhixFlow will permit during the processing of a file before. Once this number has been exceeded, PhixFlow will abandon the attempted file load; see Error Handling below.

Parallel Readers

Enter the number of files to process in parallel. If blank, this defaults to 1. If the file collector is configured to read files in sequence, this field is ignored and a single file reader is used.

Unreadable Directories

Select what PhixFlow will do if there are unreadable directories when it is searching a directory hierarchy for files to import.

• Error: the search fails and the unreadable directories are reported in the log.

• Warning: the search continues and unreadable directories are reported in the log.

• Ignore: the search continues and unreadable directories are ignored.

XPath Expression

Available when File Type is XML File.

Enter valid XPath syntax. For information about how to use XPath expressions and how to use the returned data in the corresponding stream attribute expressions, see XPath Examples.

Character Set

Select the character encoding to be used:

• utf-8
• utf-16
• ascii
• other - see Other Character Set, below
• cp1252
• iso-8859-1

Column Separator

Select a value from the drop-down list.

• Comma
• Space
• Tab
• Other -  see Separator Character, below

Separator Character

Available when Column Separator is Other. Enter a custom column separator or a sequence of multiple characters.

Quote Style

Select a value from the drop-down list.

• Double Quote
• Single Quote
• Back Tick
• Other - see Quote Character below
• None

Quote Character

Available when Quote Style is Other. Enter a custom quote style character.

Ignore Extra Columns

Available when File Type is Comma Separated Values.

 Tick PhixFlow will not report an error if the record being read contains more columns than expected.

 Untick PhixFlow report will report an error if there are too many columns; see Error Handling below.

Ignore Missing Columns

Available when File Type is Comma Separated Values.

 Tick PhixFlow will not report an error if the record being read contains fewer columns than expected.

 Untick PhixFlow will report an error if there are too few columns; see Error Handling below.

Import Rows Matching

Enter an expression that PhixFlow compares to each line in the file against the Expressions and PhixScripts. Only lines that match are imported.

Replace Text Matching

In each imported line, find all occurrences of the expression that you enter in Replace Text Matching and replace it with the expression that you enter in With; see Expressions and PhixScripts.

With

Excel Data Range Expression

Available when File Type = Excel Spreadsheet.

Leave this field blank or enter an expression for the spreadsheet data range that PhixFlow will look in. The data that PhixFlow extracts from the range is defined in the File Columns section, below. The expression can specify:

• blank: PhixFlow looks at the whole first worksheet in the Excel file, even if this is a hidden sheet. 

• columns on the first worksheet, for example, "A:C"

• columns and rows on the first worksheet, for example, "A1:G10"

• the worksheet and data range, with the format "WorksheetName!TopLeftCell:BottomRightCell", for example, "DailyCalls!A1:G100"

You cannot specify:

• only a worksheet.

• columns for a specified worksheet.

If the worksheet name contains:

• spaces, you must enclose the name in single quotes, for example My Sheet is expressed as "'My Sheet'!A1:G10"

• single quotes, you must
  

  • enclose the whole name in single quotes

  • and use two single quotes (apostrophes).
    For example, Gary'sSheet is expressed as "'Gary''s Sheet'!A1:G100"

do (
    $rangeList = [],
    forEach($sheet, _worksheets,
        if ( listContains(["sheet A", "sheet'B"], $sheet),
            do(
                $safeSheet = "'" + replaceAll($sheet, "'", "''") + "'",
                $range = $safeSheet + "!A1:B10",
                addElement($rangeList, $range)
            )
        )
    ),
    $rangeList
)

Ignore Undefined Values

Available when File Type is Excel Spreadsheet. When importing the file:

 Tick PhixFlow ignores all unsupported Excel values such as #N/A, #REF! #DIV/0 and replaces them with null values. When the import is complete, PhixFlow displays a single warning message stating the number of unsupported cell values with details about the first unsupported cell value.

 Untick PhixFlow attempts to import all the values that it finds. PhixFlow reports individual warning or errors in the console for any unsupported Excel values. If the import exceeds the maximum number of errors/warnings, PhixFlow ends the process and the import will fail.

Maximum Excel File Size (MB)

Specify the maximum file size, in megabytes, to process. This prevents upload attempts on excessively large files from slowing down the server.

The default is 0, which means no restriction. Specify a number greater than 0, for example 500, to prevent the file collector from processing files larger than this size.

File Password

Available if File Type is Excel Spreadsheet.

If you are reading a spreadsheet which is password protected, enter the password here so that the file can be unlocked.

Confirm Password

Available when File Type is Excel Spreadsheet.

If you are reading a spreadsheet which is password protected, confirm the password here so that the file can be unlocked.

Log Traffic

You can set this option when system logging → Allow Logging is ticked; see System Logging Configuration.

• Log File Collector Traffic : when ticked, PhixFlow always logs the number of records read from files by file collectors, whatever is set here.

To change this property, you must have the Modify System Logging Configuration privilege.

 Untick to prevent logging. Ticking this box has no effect when the system logging option is ticked.

 Tick to log details of communication. Ticking this box has no effect when the system configuration option Allow Logging is not ticked.

Validate File Format

Tick to indicate you have completed the properties and the XML description matches the file format.

Stream Item Node

(Stream item is the old term for a record in a table.)

Enter an expression for a string or list of strings. Each string is a node name in the binary file that will generate an output record.

File Format Description

Enter an expression, using XML Binary File Grammar, describing the format of data in the file.

dd/MM/yy dd/MMM/yy dd/MM/yyyy dd/MMM/yyyy dd-MM-yy dd-MMM-yy dd-MM-yyyy dd-MMM-yyyy MM/dd/yy MMM/dd/yy MM/dd/yyyy MMM/dd/yyyy MM-dd-yy MMM-dd-yy MM-dd-yyyy MMM-dd-yyyy yyyyMMdd

dd/MM/yy HH:mm:ss dd/MMM/yy HH:mm:ss dd/MM/yyyy HH:mm:ss dd/MMM/yyyy HH:mm:ss dd-MM-yy HH:mm:ss dd-MMM-yy HH:mm:ss dd-MM-yyyy HH:mm:ss dd-MMM-yyyy HH:mm:ss MM/dd/yy HH:mm:ss MMM/dd/yy HH:mm:ss MM/dd/yyyy HH:mm:ss MMM/dd/yyyy HH:mm:ss MM-dd-yy HH:mm:ss MMM-dd-yy HH:mm:ss MM-dd-yyyy HH:mm:ss MMM-dd-yyyy HH:mm:ss yyyyMMdd.HHmmss

y

year

year

1996

M

month of year

month

Jul; 07

d

day of month

number

10

H

hour of day (0~23)

number

0

m

minute of hour

number

30

s

second of minute

number

55

true,yes,T,Y,1

false,no,F,N,0

_fileName

The name of the file.

_lineNumber

The line number of the record within the file it was read from.

The _lineNumber attribute is not available for file collectors of File Type is File Details Only.

_modifiedDate

The datetime of when the file was last modified.

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.

_path

The full path to the file which is the result of concatenating the _rootDirectory and the _subDirectory values.

_rootDirectory

The root base directory (if specified) concatenated with the value evaluated in the Collectors 'Input Directory Expr' field.

_size

The size of the file in bytes.

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.

_subDirectory

The sub-directory relative to the _rootDirectory in which the corresponding file resides.

_worksheet

The name of the current worksheet of the Excel file. The _worksheet is only available if the File Type is Excel Spreadsheet.

_range

The Excel range expression that was used. The _range attribute is only available if the File Type is Excel Spreadsheet.

zip

".zip"

A zip archive created by either windows programs such as winzip etc or unix commands such as zip. For example, to create a compressed zip file called dailyCalls.zip that includes a single file called dailyCalls_20120918.csv:

zip dailyCalls.zip dailyCalls_20120918.csv

tar

".tar"

A tar archive created by the unix tar command. For example to create a tar file called dailyCalls.tar that includes a single file called dailyCalls_20120918.csv:

tar -cvf dailyCalls.tar dailyCalls_20120918.csv 

gz

".gz"

A gz archive created by the unix gzip command. For example, to create a compressed gz file called dailyCalls_20120918.csv.gz that includes a single file called dailyCalls_20120918.csv. 

 gzip dailyCalls_20120918.csv

The unix gzip command always assumes the named container has a single file of the same name contained within.

tgz

".tgz"

A joint tarred gz archive created by combining both the tar and gzip commands into a single command. For example, to create a compressed tgz file called dailyCalls.tgz that includes a single file called dailyCalls_20120918.csv:

tar -cvzf dailyCalls.tgz dailyCalls_20120918.csv

DailyCalls10.zip

/DailyCalls10.csv

".*Calls10.*"

".*

DailyCalls10.zip/DailyCalls10.csv

DailyCalls.tar

/subdir1/calls10.csv
/subdir1/calls20.csv
/subdir2/calls100.csv
/subdir2/calls200.csv
/subdir3/calls1000.csv
/subdir3/calls2000.csv

".*calls10.*"

".*

".*subdir2.*

DailyCalls.tar/subdir1/calls10.csv
DailyCalls.tar/subdir3/calls1000.csv

Outer.zip

/subdir1/calls10.csv
/subdir1/calls20.csv
/subdir1/Inner.zip/innerdir/calls100.csv
/subdir1/Inner.zip/subdir2/calls1000.csv

Note that Outer.zip contains a compressed zip file called Inner.zip

".*calls10.*"

".*subdir1.*

".*subdir2.*

Outer.zip/subdir1/calls10.csv
Outer.zip/subdir1/Inner.zip/innerdir/calls100.csv

Outer.tar.gz

/Outer.tar/subdir1/calls10.csv
/Outer.tar/innerdir/calls100.csv
/Outer.tar/subdir1/Inner.zip/innerdir/calls1000.csv

Note that Outer.tar.gz contains a tar container which in turn contains a compressed zip file called Inner.zip

".*calls10.*"

".*subdir1.*innerdir.*

Outer.tar.gz/Outer.tar/subdir1/Inner.zip/innerdir/calls1000.csv