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 Handling Compressed Files, below.

You can also use a file collector to import email messages and their attached files; see Reading Data From an Email Account for details.

To add a new file collector object to an analysis model:

1. Go to the model's toolbar → Create group.
2. Click

   Insert excerpt
   _file _file nopanel true

    to expand the menu.
3. Drag a 

   Insert excerpt
   _file_collector _file_collector nopanel true

    onto the analysis model.

To add an existing file collector object to an analysis model:

1. Go to the model toolbar → List group.
2. Click

   Insert excerpt
   _file _file nopanel true

    to expand the menu.
3. Click 

   Insert excerpt
   _file_collector _file_collector nopanel true

    to open the list of available file collectors.
4. Drag a file collector into the analysis model.

To open a file collector's property tab, double-click on:

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

Uploading Files

Before running a model, you must upload files that do not already exist on the PhixFlow server. These are called managed files. For example you may have CSV or Microsoft Excel files saved to your computer or the network.

1. In the property tab, set the Input Directory Expression.
2. In the model, right-click on the file collector icon to open the context toolbar.
3. Click 

   Insert excerpt
   _upload_file _upload_file nopanel true

   .
4. PhixFlow opens a file browser. Find and select the file.
5. Click 

   Insert excerpt
   _upload_file _upload_file nopanel true

   . 

PhixFlow uploads the managed file to its database in the following location:

directory/tag/date/id/

where

• directory is configured in System Configuration → File Upload Directory
• tag
 is
•  is a sub-directory, configured in the file collector → Tag property
  This allows you to group related files.
• date is the current date when you run the model
• id is a unique import identifier automatically generated by PhixFlow.

Archive and Error Folders

When you specify archive and error folder locations, after running the model, PhixFlow moves a managed file:

• either to the archive directory, after successful processing:
    directory/tag/archive/date/id/
where directory, tag, date and id are defined above, and
  archive is configured in the file collector → Archive Folder expression.
  
  
• or to the error directory, if PhixFlow cannot process the file:
    directory/tag/error/date/id/
where directory, tag, date and id are defined above, and
  error is configured in the file collector → Error Folder expression.

If you do not specify the archive or error folder, PhixFlow leaves the file in the original folder.

Insert excerpt
_property_tabs _property_tabs name basic-h nopanel true

Basic SettingsSettings

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

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

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
  _attribute_populate _attribute_populate 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
  _table_list _table_list nopanel true

   Open  Open a repository tab listing all the tablesthe tables.

• Insert excerpt
  _file_collector _file_collector nopanel true

   Open  Open a repository tab listing all the file collectors.

To add attributes, click

Advanced

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

XML Namespaces

This section is available when File Type is XML. It has a toolbar with standard options. The grid contains a list of the namespaces defined in an XML response.

To add a namespace to the list, click 

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.

Error Handling

Anchor
fileCollectorErrorHandlingSummary fileCollectorErrorHandlingSummary

All the properties that support error handling are documented individually above; this is a summary of these features, and what to consider when designing the error handling needed for the files you are loading.

Ignore Missing Columns

If you tick Advanced → Ignore Missing Columns  you will still import rows which are missing columns. The missing columns will give you blank values in the table for the missing fields.

However, PhixFlow starts lining columns up from the left, based on the idea that if there are missing columns, they will be missing from the right hand side. This means if columns are missing from somewhere in the middle or at the beginning of the line, the line may fail to import if the data types don't line up. However, this is not an error trap,  it is arbitrary - if you think lines might be missing values in the middle or at the beginning of lines you should be careful about using this option – in any case, you will need further validation.

Ignore Extra Columns

If you tick Advanced → Ignore Extra Columns - again, this assumes that extra columns will appear on the right. If they appear in the middle or at the beginning of the row, the row may or may not import depending on how the data types of the resulting record line up with a standard record. Again, if you think this may occur, you need further validation.

Errors Before Rollback

If you set a number in Advanced → Errors Before Rollback – providing that, across the run (not individual files), the error count is less than the threshold you have set - this will load all records that are not in error. You will get a warning message about any that fail in the console – with the file name, and details of the line that failed. But these are essentially discarded from the import. These files will then be placed in the archive directory.

If, across the run, the threshold is reached – all files that have been processed up to that point will be placed in the error directory, including files don’t contain errors (because these are associated with a failed run). The run will be in error - that is, a red line in the console, the resulting data set for the run (the recordset) will be invalid, and therefore not available to the next step in your process. Any files remaining after the error threshold was reached will remain, untouched, in the input directory.

Supported Formats and Values

Supported Date/Datetime Format Patterns

Anchor
validdateformats validdateformats

The following formats are available for use in Date and Datetime type fields:

The symbols used in these formats are explained in the following table

The number of letters used in the pattern determines the format.

• Number: The minimum number of digits. Shorter numbers are zero-padded to this amount.
• Year: Numeric presentation for the year field are handled specially. For example, if the count of 'y' is 2, the year should be displayed as the zero-based year of the century, which is two digits.
• Month: 3 or over, use text, otherwise use number.

Supported TrueFalse Values

Anchor
validtruefalsevalues validtruefalsevalues

The following values are available in upper, lower and mixed cases for use in the TrueFalse type field:

CSV Files 

Anchor
csv csv

A CSV (comma-separated variable) file is a plain text file where each line in the file consists of a number of fields separated by commas ','. If fields may contain spaces, commas or double quotes, they should be enclosed in double quotes '"'. A double quote may be represented by 2 double quotes.

A CSV file is always encoded using a specific character set. On Windows this is typically Cp1282, and on Unix/Linux US7ASCII, though the following are sometimes used: UTF-8, UTF-16, ISO-8859-1.

A CSV file typically has an extension '.csv'.

A CSV file can be created directly in a plain text editor (e.g. Wordpad/Notepad), in Excel (by saving as a CSV), or by some other application.

File Collector Attributes 

Anchor
attributes attributes

You can use the following PhixFlow internal variables to specify the attributes you want to load using a file collector. These attributes are available for all file types.

Handling Compressed Files

Anchor
containers containers

In the majority of cases a compressed file will just contain a single file. For example, simple zip file called DailyCalls.zip would contain a single file named DailyCalls.csv.

However, some compressed files contain directories, sub-directories, files and further compressed files. In such cases the compressed file can be thought of as a directory, and further, any compressed files within the compressed file can be thought of as directories in the directory structure inside the compressed file. Therefore, compressed files are treated like normal directories and obey the same rules when matching the Directory Pattern Expression and the Exclude Dir Pattern Expression. Similarly all directories, sub-directories, and compressed files within a compressed file will also be treated as normal directories when matching the Directory Pattern Expression and the Exclude Dir Pattern Expression. Files contained anywhere inside the directory structure in the compressed file (including files contained in a compressed file within the compressed file) are treated as normal files when matching the File Pattern Expression.

Supported Compressed Files

File Compression Examples

The following table shows how each compressed file found will be treated given the following values for 'File Pattern Expression', 'Directory Pattern Expression' and the 'Exclude Dir Pattern Expression'.

Anchor
autoconf autoconf

Easy Loading for Excel Spreadsheets 

With PhixFlow, you can quickly and easily load all the data from a worksheet in an Excel file, without having to set all the different file collector and table properties. 

1. Drag a file collector into an analysis model.
2. In the file collector properties, enter a name for the collector. Leave Auto Configure ticked.
3. Hover your mouse pointer over the file collector and click 

   Insert excerpt
   _upload_file _upload_file nopanel true

    Managed File.
4. Find and select the Excel spreadsheet you want to load into PhixFlow. Click Open then 

   Insert excerpt
   _upload_button _upload_button nopanel true

   . 
5. For an .xlsx file only, PhixFlow prompts you to specify the worksheet that you want to upload to the file collector.
6. PhixFlow automatically:
   • uploads the Excel data:
     • from the specified worksheet in an .xlsx file.
     • from the first worksheet in an .xls file.
   • sets the file collector properties
   • creates a table
   • sets the table properties, including adding attributes for each data column in the Excel spreadsheet.

You cannot use the Auto Configure option to:

• select specific Excel data columns to load
• always load the latest version of an Excel worksheet when you run analysis on the model
• load a data file that is not an Excel file.