File Collectors

Overview

DataBlend supports a variety of collectors which receive information from SFTP locations. Users can conveniently receive information from a local or remote SFTP. Excel (.xlsx), delimited (e.g. csv), Flat, and JSON files can be uploaded directly to a DataBlend stream. How the data is processed depends on the schema type and upload settings.

For more information about streams and their relationship to schemas and data sources, see Data Sources, Schemas, and Streams.

Configuration

Field	Required / Optional	Comments
Name	Required	Descriptive free-text name for the collector.
Data Source	Required	Select a pre-configured data source from the drop-down or click Create New to create a new data source.
File Collector Type	Required	Select Local SFTP, Remote SFTP, S3, or Web Query File.
Credential	Required	Select a pre-configured credential from the drop-down.
Decrypt Files	Optional	Enable if the collected file is encrypted and needs to be decrypted.
Path	Required	Enter an exact filename or a subdirectory and filename. Do not start with `/`.
Use Regular Expression	Optional	If `false`, the directory/filename in Path is used exactly as entered. If `true`, the value of Path is treated as a regular expression: `.\.csv` — matches any file ending in .csv `.\.xlsx` — matches any file ending in .xlsx `/GC.*\.csv` — matches any file starting with GC and ending in .csv
Trigger Match Mode	Optional	Only shown when Use Regular Expression is ON. Determines what the regular expression is matched against (Path or File).
Match Limit	Optional	Only shown when Use Regular Expression is ON. Limits the number of files collected per run. Enter `0` for no limit — all files matching the Path expression will be collected. Enter any positive integer to collect up to that number of matching files per run. For example, set to `1` if a workflow is triggered by file drops and you want to process one file at a time. To ensure additional files dropped at the same time are not skipped, configure the workflow to Run in Sequence under its Advanced tab.
Is Zip File	Optional	Enable if 'Path' points to a zip file.
Zip File Name	Optional	Only shown when Is Zip File is ON. Enter the name of the file to extract from within the zip archive. Enable Use Regex to match the file name using a regular expression instead of an exact match.
After Collection	Required	Controls what happens to the source file after collection. Select one of the following: Keep file in original location — file is left in place (default). Copy file to archive folder — file is copied to the Archive Folder; the original remains in place. Move file to archive folder — file is moved to the Archive Folder; the original is removed. Delete file — file is deleted after collection.
Archive Folder	Required when After Collection is Copy or Move	The path where archived files will be stored.
Archive File Name	Required when After Collection is Copy or Move	Important: It is strongly recommended to include a timestamp in the archive filename. If a file with the same name already exists in the Archive Folder, the move or copy will fail and the original file will be collected again on the next run. Leave empty to use the same file name as the collected file, or click Add Timestamp. DataBlend automatically appends a timestamp (including seconds) when one is not present. To add one manually, use a timestamp variable in the path, for example: `/archive/invoices_{timestamp}.csv`
Use Bookmark	Optional	When enabled, this value is updated each time the collection runs. Only files uploaded after the bookmarked time will be processed, preventing double-processing of files that were not deleted. To reprocess files, clear the bookmark.
File Definition	Required	Specifies how DataBlend should parse the collected file. Select from the drop-down: Delimited - For comma-separated or other character-delimited files (e.g. CSV, TSV, pipe-delimited). A Delimiter field will appear; if left blank, comma is assumed. For tab-delimited files enter `/t` or `TAB`. Excel - For `.xlsx` or `.xls` files. Schema name must match the tab name in the Excel file, or use the Use Sheet Index toggle to collect by tab position (0 = first tab, 1 = second tab, etc.). Flat - For fixed-width files where columns are defined by character position rather than a delimiter. JSON - For JSON-formatted files. An Is Array field will appear if the JSON contains an array. Raw - Collects the file as raw, unprocessed content.
Fields shown when File Definition = Delimited
Delimiter	Optional	Character used to separate fields. If left blank, comma is assumed. For tab-delimited files enter `/t` or `TAB`.
Is Header Row Present	Optional	Indicates whether the first row of the file contains column headers.
Ignore Missing Fields	Optional	When enabled, rows with fewer fields than expected are processed instead of causing a collection error.
Ignore Quotes	Optional	When enabled, quote characters within field values are treated as literal characters rather than field wrappers/delimiters.
Skip Rows	Optional	Enter the number of rows to skip from the top of the file before DataBlend begins reading data. For example, enter `1` to skip a single header row, or `3` to skip three rows. A single whole number is expected — do not enter a range (e.g. `1-3`) or a list (e.g. `1, 2, 3`).
Allow UTF-8 Column Names	Optional	Enable to allow column headers containing UTF-8 characters to be used as-is.
Schema Name	Required	Name of the schema DataBlend will create or use for the collected data.
Columns table	Optional	Use Add Column to manually define expected column names and types; leave empty to let DataBlend infer columns automatically from the collected file. Search filters the list of defined columns.
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
Fields shown when File Definition = Excel
Allow UTF-8 Column Names	Optional	Enable to allow column headers containing UTF-8 characters to be used as-is.
Is Header Row Present	Optional	Indicates whether the first row of each collected sheet contains column headers, unless overridden for a specific sheet under Schemas.
Skip Rows	Optional	Enter the number of rows to skip from the top of each sheet before DataBlend begins reading data, unless overridden for a specific sheet under Schemas.
Merge Sheets	Optional	When enabled, combines data from matching sheets into a single schema instead of collecting them separately.
Header Date Format	Optional	Specifies the date format used when parsing header values, if applicable, unless overridden for a specific sheet under Schemas.
Schemas	Optional	Add schemas to collect specific tabs. If no schemas are added, all tabs will be collected into schemas named after each tab.
Name (within a Schemas entry)	Required	Must match Excel's tab name.
Is Header Row Present (within a Schemas entry)	Optional	Not Set / True / False. Overrides the Excel-level Is Header Row Present setting for this specific sheet.
Skip Rows (within a Schemas entry)	Optional	Overrides the Excel-level Skip Rows setting for this specific sheet.
Use Sheet Index (within a Schemas entry)	Optional	Allows using a schema name different from Excel's tab name.
Sheet Index (within a Schemas entry)	Optional	Only shown when Use Sheet Index is ON. Set to `0` to collect the first tab, `1` for the second tab, etc. Number field, minimum value `0`.
Header Date Format (within a Schemas entry)	Optional	Overrides the Excel-level Header Date Format setting for this specific sheet.
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
Add Sheet Name To Schema	Optional	When enabled, adds a column containing the source sheet name to the collected schema.
Fields shown when File Definition = Flat
Flat File Type	Required	Select the fixed-width file layout used to determine column positions within each row.
Schema Name	Required	Name of the schema DataBlend will create or use for the collected data.
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
(additional fields)	—	A column-position definition table (similar to Delimited/JSON) may appear below these fields but was not captured in the provided screenshot.
Fields shown when File Definition = IIF
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
(possible additional fields)	—	The screenshot shows only these two fields for IIF, with no Schema Name visible. Please confirm whether IIF truly has no other required fields, or whether the screenshot was cropped before showing more (e.g. a Schema Name field above).
Fields shown when File Definition = JSON
Is Array	Optional	Enable if the JSON file's top-level structure is an array rather than a single object.
Allow UTF-8 Column Names	Optional	Enable to allow column headers containing UTF-8 characters to be used as-is.
Schema Name	Required	Name of the schema DataBlend will create or use for the collected data.
Columns table	Optional	Use Add Column to manually define expected column names and types; leave empty to let DataBlend infer columns automatically from the collected file. Search filters the list of defined columns.
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
Fields shown when File Definition = JSONPath
JSON Path	Required	Enter a JSONPath expression used to extract records from the JSON file, e.g. `$[*].item.name` to get all item names. Use the linked evaluator to test expressions before saving.
Allow UTF-8 Column Names	Optional	Enable to allow column headers containing UTF-8 characters to be used as-is.
Schema Name	Required	Name of the schema DataBlend will create or use for the collected data.
Columns table	Optional	Use Add Column to manually define expected column names and types; leave empty to let DataBlend infer columns automatically from the collected file. Search filters the list of defined columns.
Add File Name To Schema	Optional	When enabled, adds a column containing the source file name to the collected schema.
Add Row Number To Schema	Optional	When enabled, adds a column containing each record's row number to the collected schema.
Fields shown when File Definition = Raw
Schema Name	Required	Name of the schema DataBlend will create or use for the collected raw content.

How to upload a file

To begin a file upload, browse to the schema that you wish to update.

A file can only be uploaded to an open stream from the stream details page.

Select a Stream

Look at the stream section for any open streams. (An open stream will not have a Closed date.)

If there is an existing open stream, decide if you want to upload to the existing stream or if you wish to create a new one.

If Schema Type Is…	… Then Upload to Existing Open Stream Will…
Default	Add contents of file to existing contents of stream.
Realtime	Add contents of file to existing contents of stream.
Single-file	Overwrite existing contents of stream with contents of the file.

If you do not wish to add the contents of the file to an existing stream, then close the open stream and create a new one.

If there is not an existing open stream, create a new one.

Upload a File

A stream which can be written to will have a file upload box.

Drop the file on the upload box (or use the file selector to find it) and the upload settings box will open.

Option	Description
File Type	DataBlend will identify the file type where possible, but this setting can be changed if necessary.
Schema Update Type	Add New Columns (formerly Add Only): Existing schema columns are preserved regardless of whether they exist in the first record collected. New columns identified in the first record collected are added to the schema. If the collection returns no records, the existing schema is unchanged. Recreate Columns (formerly Auto): Default. Schema is recreated from the first record collected. If the collection returns no records, the existing schema is unchanged. Preserve Columns (formerly Manual): No changes are made to the schema during collection. If the schema is changed by the file upload, data in previous streams may no longer be available. (Columns may “go missing”.) When in doubt, set to Add New Columns.
	Leave set to false if: the schema type is single-file and you will edit the uploaded data manually. the schema type is realtime or default and there are additional files to be uploaded. Set to true if: the schema type is default and you wish to query the data immediately. you want to know that no further additions or modifications can be made to this dataset.

Option

Description

File Type

DataBlend will identify the file type where possible, but this setting can be changed if necessary.

Schema Update Type

Add New Columns (formerly Add Only): Existing schema columns are preserved regardless of whether they exist in the first record collected. New columns identified in the first record collected are added to the schema. If the collection returns no records, the existing schema is unchanged.

Recreate Columns (formerly Auto): Default. Schema is recreated from the first record collected. If the collection returns no records, the existing schema is unchanged.

Preserve Columns (formerly Manual): No changes are made to the schema during collection.

If the schema is changed by the file upload, data in previous streams may no longer be available. (Columns may “go missing”.) When in doubt, set to Add New Columns.

Leave set to false if:

the schema type is single-file and you will edit the uploaded data manually.
the schema type is realtime or default and there are additional files to be uploaded.

Set to true if:

the schema type is default and you wish to query the data immediately.
you want to know that no further additions or modifications can be made to this dataset.

Bookmarks

Bookmarks can be used on certain collectors to set a dynamic value each time the collector runs. A field from the collector source will need to be selected to be associated with the Bookmark function. For example, ‘WHENMODIFIED’ is a field in the Sage Intacct GL Detail object that can be used as a Bookmark:

Once the Bookmark Field is set, every collection will save the value based on the latest run time. In the example above, the Bookmark is stored as a 'WHENMODIFIED' date of '05/17/2022 04:22:10'. The next time the Collector runs, it will only pull values with a different value, and then will update the Current Value field dynamically.

Other Notes about Bookmarks:

Most commonly set as date fields, the Bookmark Field can also be a non-date field.
Filters on the collector can still be applied, so even though a Bookmark may be set, if a filter is present it will still only collect data with both the Bookmark value and filters in mind.
- To accomplish this consolidation in the query, your FROM section will need to leverage ‘All Streams’. This will also carry a risk that if the bookmark’s ‘Current Value’ is cleared or the bookmark is removed, the same data can be included into a new stream and therefore overstate the dataset that is being used in the query. It is important to be mindful of which collectors and queries are relying on the bookmark functionality and avoid modifying the bookmark while in use. To reset the bookmarked data, make sure to clear the ‘Current Value’ and also purge all streams or create a new Schema so that you’re starting from an empty dataset.
  
  Data gets collected into a new stream even when Bookmarks are present, the data does not automatically consolidate into one stream. Consolidation would need to be done in a query.

Details

The details section documents who the Collector was created and updated by and the corresponding times. This allows for easy tracking of multiple Collectors.

Latest Collection

The latest collection section documents the state of the collector, created time, and the status of the query. States include complete and error.

Collections

The Collections section documents when the Collector was created, started, completed and the total amount of data scanned. The status includes information regarding the state of the Collector. This allows for easy tracking of multiple collections.

Upload History

File uploads will be run as jobs in the background. The number of records processed or any error encountered will be displayed in the Uploads history table.