Local SFTP Collectors

Prerequisites

• Create a group for the client.

• Create a Local SFTP Credential for the group and provide the username and password to the client.

• Create a Data Source for the collection, if one does not already exist.

Configuration

Field	Required/ Optional	Comments
Name	Required	Free Text
Data Source	Required	Select from drop-down.
Type	Required	File Collector
File Collector Type	Required	Local SFTP
Credential	Required	Select from drop-down
Path	Required	Enter an exact filename or a subdirectory and filename. do not start with '/'.
Use Regular Expression	Optional	If FALSE, then the directory/filename are used exactly as entered in Path. If TRUE, then 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
Delete Files When Finished	Optional	If TRUE, then any file(s) processed by the collection are deleted when the collection is completed. Note: Files which were processed by previous collections will NOT be deleted.
Bookmark	Optional	This value is updated each time the collection runs. Only files uploaded AFTER this time will be processed. (This prevents double processing of files which were not deleted by previous collections.) To reprocess files, clear the bookmark.
File Definition	Required	Select from drop-down: Delimited Excel Flat JSON
Delimiter (Optional) Only if File Definition is Delimited	Optional	If no delimiter is entered, comma delimited is assumed. For tab-delimited files, enter ‘/t’ or ‘TAB’. Other common delimiters: pipe ‘|', semi-colon ‘:’, tilde '~’
Is Array Only if File Definition is JSON.	Optional	Enter the Array.  Users should only do so if the file definition is JSON.
Name Only if File Definition is Delimited or JSON.	Optional	The Schema name within the Data Source. If this Schema already exists, it will be updated. If it does not exist it will be created. NOTE: If the Name is changed, queries using the old schema name may no longer work.

Collecting Excel files

1. Collecting data from all Tabs into separate schemas. Don’t “Add Schema” when setting up the Collector. Schemas will be named with the Tabs names. If Schema added and then deleted the collection will error.
   Troubleshooting: Switch to another File Definition → Save → Switch back to Excel.

2. Collecting data from one Tab.
   a) For .xlsx file schema’s Name should be the same as the name of the Tab in the Excel file you are collecting data from

b) For .xls - either the same as above or if you don’t add schema and it will be added automatically.

c) If you want to give another name to your schema that is different to the Tab name toggle On "Use Sheet Index" and set it to 0 to collect from the first tab, 1 from the second, etc.

How to Send Files to DataBlend SFTP

SFTP Host

1. Create a Local SFTP Credential

2. Once the Credential has been saved, the username is displayed.

FTP Client

Any FTP Client which supports SFTP can be used to send files to the DataBlend FTP server. If you do not currently have an SFTP Client, DataBlend suggests one of the following:

• FileZilla (Windows, macOS, and Linux)

• BitVise (Windows only)

These clients offer a balance of ease of use, long track record, and are frequently updated for security and bug fixes. DataBlend has historically used BitVise.

Local SFTP Security

DataBlend Local SFTP utilizes AWS Transfer, a managed and secure SFTP service provided by Amazon. What is AWS Transfer Family? - AWS Transfer Family.

1. Protection in Transit - The connection from client to the Transfer server is encrypted with TSL 1.2

2. Protection at Rest - Because Transfer leverages S3 storage, the storage buckets where the files are stored before being processed by DataBlend are also encrypted at rest utilizing customer group specific KMS encryption keys.