Page Comparison

Use a file collector to specify the structure, content, naming patterns and location of files of data. When you run the file collector, PhixFlow imports the data. You can also use a file collector to process files inside compressed file archives such as zip files; see Compressed Files, below.

...

the file collector name in the repository browser
the file collector icon in a model.

The toolbar has the standard icons and an extra button:

If a valid location has been configured in the file collector to locate an existing CSV file, the user can click on a button at the top of the grid to automatically create the file column descriptions in this form.

The column names are taken from the first row of the file. To construct the name all invalid characters are stripped out of the value found in each cell and the result is assumed to be the name of the column.

The remaining rows are examined to try to determine the type and length for each column definition based on the values found in the file. If a type cannot be determined then the column is defined as a string. The length of the field is set to be the length of the longest value found.

The settings tab has the following sections:

Field Description

Basic Settings

Name Enter a name for this file collector.

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

Source Type

You can select

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.

Number of Header Lines 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, XML and HTML file types).

File Type

Can have values:

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.

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

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 If this is turned on, then the collector will run as part of any ad-hoc Analysis Engine run which requires this data. If not, it will only run as part of a scheduled task under the Analysis Engine.

FTP Site 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

Can have values:

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

This input database collector or stream must be attached to the file collector by a lookup pipe with no index set. The attribute of the input stream or collector which contains the file path names is specified in the field: File Name Attribute. The value entered into this field should be plain text, e.g. myFilePaths but not quoted "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, 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. There should be no leading '/'.

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

Please note that once an attempt is made to read a file via the Read Paths method, the stream will not attempt to re-import the contents of that file if instructed to do so during another analysis run, even if the original import failed.
Please also note that the stream containing the file paths will not run automatically in this situation, and so must be added to a taskplan if it is required to run it before the file collector stream.

Read Names: This option is deprecated.

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.

Tag

This field is only available if the Source Type field is set to Managed File.

Specify a directory using string literals only. Do not use PhixFlow variables.

When files are uploaded by PhixFlow they are placed into a directory whose full path is a combination of the root File Upload Directory (specified in System Configuration on the System Directories tab), the tag value specified here and the Input Directory specified below (hard coded to 'in' for Managed files).
For example, if the System Configuration File Upload Directory is set to C:\ManagedFiles and Tag is set to CVFiles then the File Collector will look within C:\ManagedFiles\CVFiles\in for files to process.

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 How To: Read data from an email account for further details.

Ignore Base Directory

This field is only available if Source Type = Specified Directory.
Normally the base directory, specified in the "System Directories" tab of the "System Configuration" screen, is prepended to all directories specified on this form. However, if this flag is ticked then this does not happen and the directories specified on this form alone are used as the full path specifications for the import file.

Input Directory Expression

Source Type = Specified Directory

Specify a directory using string literals only. Do not use PhixFlow variables.

If the Source Type is Specified Directory, files will be read from the directory specified in Input Directory Expression.

Unless the flag Ignore Base Directory is ticked, the path specified in this field will be added to the default input directory root - this is specified in the System Configuration File Upload Location. If the flag Ignore Base Directory is ticked, the full path for the input directory must be specified.

In fact, this field is an expression. This must evaluate to a plain text string. In the simple case, this will be text surrounded by quotes, e.g.

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

Also, because this is an expression, you must always use / rather than \, even on windows platforms.

You can include PhixFlow variables in this expression, e.g.:

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

If you need to include wildcards or some other variable element in the resulting path, you must use the Directory Pattern Expression.

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.

Source Type = Managed File

If the Source Type is Managed file, 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 that files will be read from.

Directory Pattern Expression

This field is used to identify valid sub-directories of the input directory.

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.

Exclude Dir. Pattern Expr.

This field can be used to exclude certain sub-directories found by 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 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.

File Pattern Expression

This field is only available if File Location Strategy = All Files in Folder. An expression used to generate a list of files to be read. This expression must itself resolve to a Regular Expression, used to match files in the input directory. Note that regular expression rules are used to perform this match, not the shell replacement style rules used in many file systems. E.g. to match all files, you must use ".*" and not "*". 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.
%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'

Archive Directory Expression If set, processed files will be written to this directory. This field is an expression that must resolve to a Regular Expression. 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 moved to a directory on a Windows platform. E.g. "C:/data/address/archive/".

Error Directory Expression If set, files that error during processing will be written to this directory. This field is an expression that must resolve to a Regular Expression. 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 moved to a directory on a Windows platform. E.g. "C:/data/address/error/".

Local Archive Directory For files retrieved via FTP, whether the archive directory is on the PhixFlow server (local) or on the original server.

Local Error Directory For files retrieved via FTP, whether the error directory is on the PhixFlow server (local) or on the original server.

Advanced

Maximum Files Specifies Enter the maximum number of files that PhixFlow will be processed whenever process when the file collector runs.

Minimum Files Specifies Enter the minimum number of files that are expected to be found whenever the 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 Specifies Enter the maximum number of records that PhixFlow will be read from each file processed.

Anchor

	fileCollectorErrorsBeforeRollback
	fileCollectorErrorsBeforeRollback

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 summary below.

Parallel Readers

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

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.

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 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 or HTML File.

Enter valid XPath syntax. Please see XPath Examples for how For information about how to use XPath expressions and how to use the returned data can be used and evaluated in the corresponding stream attribute expressions, see XPath Examples.

Character Set

Insert excerpt

	_character_set
	_character_set
nopanel	true

Column Separator

Insert excerpt

	_column_separator
	_column_separator
nopanel	true

Separator Character

Insert excerpt

	_separator_character
	_separator_character
nopanel	true

Quote Style

Insert excerpt

	_quote_style
	_quote_style
nopanel	true

Quote Character

Insert excerpt

	_quote_character
	_quote_character
nopanel	true

Anchor

	fileCollectorIgnoreExtraColumns
	fileCollectorIgnoreExtraColumns

Ignore Extra Columns

Available when File Type is Comma Separated Values.

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

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

Insert excerpt

	_check_box_untick
	_check_box_untick
nopanel	true

PhixFlow report will report an error if there are too many columns.

See error handling summary below.

Anchor

	fileCollectorIgnoreMissingColumns
	fileCollectorIgnoreMissingColumns

Ignore Missing Columns

Available when File Type is Comma Separated Values.

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

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

Insert excerpt

	_check_box_untick
	_check_box_untick
nopanel	true

PhixFlow will report an error if there are too few columns.

See error handling summary below.

Import Rows Matching Enter an expression that PhixFlow compares to each line in the file against the expression. 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.

With

Anchor

	excelRangeExpression
	excelRangeExpression

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"

Tip
Use the internal variable _worksheets to list all worksheets that PhixFlow found in the file.

Expand

title	Simple Examples

To specify the Daily Calls worksheet, columns A to G, rows 1 to 10:

"'Daily Calls'!A1:G10"

To specify different areas from the same worksheet, or multiple worksheets:

["'Daily Calls'!A1:G100", "A1:B20", "'Monthly Totals'!A1:C100"]

To specify John's Data worksheet, columns A to G, rows 1 to 10, escaping the space and apostrophe:

"'John''s Data'!A1:G10"

Expand

title	Example using _worksheets

To examine the list of worksheets that have been found and specify ranges for only certain sheets, if they exist. The worksheet names contain spaces and/or single quotes.

Code Block

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

This expression evaluates to the list of a maximum of two ranges, if both worksheets sheet A and sheet'B exist. If a sheet is not found, the range is not be included.

This is important for error handling. If you specify a range that is not in the Excel file, PhixFlow will error. 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.

To ensure PhixFlow recognises the generated ranges as valid, you must use $safeSheet, as this doubles any embedded single quotes and wraps the result in single quotes.

Ignore Undefined Values

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

Insert excerpt

	_check_box_ticked
	_check_box_ticked
nopanel	true

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.

Insert excerpt

	_check_box_untick
	_check_box_untick
nopanel	true

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.

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.

File Columns
Anchor
file_columns
file_columns

Available when File Type is Comma Separated Values, Fixed Length Records and Excel Spreadsheet. Enter the attributes of the data columns that you want to extract from the input file. The grid has the standard toolbar and the extra buttons:

Insert excerpt

	_populate_attributes
	_populate_attributes
nopanel	true

Once you have uploaded a file, automatically populate the File Columns grid with the data attributes. PhixFlow samples some rows in the file to determine the values to use.

Insert excerpt

	_show_streams
	_show_streams
nopanel	true

Open a repository browser listing all the streams.

Insert excerpt

	_show_file_collectors
	_show_file_collectors
nopanel	true

Open a repository browser listing all the file collectors.

To add attributes, click

Insert excerpt

	_add_object
	_add_object
nopanel	true

and to edit an attribute double-click on a row in the grid. PhixFlow opens an attribute form.

Name

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

If you use

Insert excerpt

	_populate_attributes
	_populate_attributes
nopanel	true

, 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 Date formats, below
Datetime - see Datetime formats, below
TrueFalse - seeTrueFalse values, below

If you use

Insert excerpt

	_populate_attributes
	_populate_attributes
nopanel	true

, 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 stream that this collector will write to are important.

If you use

Insert excerpt

	_populate_attributes
	_populate_attributes
nopanel	true

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

Xml Namespaces

Available when File Type is XML. Enter details about the XML namespace of the XML input file

Info
PhixFlow needs this information because an XML Namespace ensures the names, elements and attributes in the file are unique. An XML instance may contain element or attribute names from more than one XML vocabulary. If each vocabulary is given a namespace, the ambiguity between identically named elements or attributes can be resolved.

Name

For the XML namespace, enter a name that matches the name you use in XPath expressions to extract data from the XML response.

For example, if a default namespace is xmlns="urn:xmlns:company-com:message" but you use the alias "home" in XPath expressions, you can enter "home" in this field.

Value Enter the XML namespace, for example http://schemas.xmlsoap.org/soap/envelope/.

File Format Description

Available when File Type is Binary File. Enter details about the binary input file format and the data you want to extract.

Validate File Format

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

Stream Item Node

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.

Expand

title	Examples

For a single output record, enter:

"DATA_RECORD_1"

For a list of output records, enter:

["DATA_RECORD_1","DATA_RECORD_2","DATA_RECORD_3"]

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

...

Versions Compared

Old Version 48

New Version 49

Key

Basic Settings

Source Type = Specified Directory

Source Type = Managed File

Advanced

File Columns
Anchor
file_columns
file_columns

Xml Namespaces

File Format Description

Page Comparison

Versions Compared

Old Version 48

New Version 49

Key

Basic Settings

Source Type = Specified Directory

Source Type = Managed File

Advanced

File Columns Anchorfile_columnsfile_columns

Xml Namespaces

File Format Description

File Columns
Anchor
file_columns
file_columns