Setting up an automated Import/Export Process using an SFTP/FTP Server

Setting up an SFTP/FTP Process is easy, however you should read our Pre-Requisites Guide and Examples Guide before you begin setting up this functionality.

Before you can configure an SFTP/FTP process, you need to install a Plugin to your Workbooks database. Click Start > Configuration > Automation > Plugins. Then click Add Plugin. Select Script Library > SFTP FTP as shown, then click Install.

On the first page, simply click “Continue”. When completed, a success message is shown. At which point, you can close the Plugin window. This installation only needs to be completed once. After installation, System Admins can access a “SFTP/FTP Settings” option from Start > Configuration > Other Integrations where you can configure or change settings for your processes.

Setup of an Automated Import/Export Process

Navigate to Start > Configuration > Other Integrations > SFTP/FTP Settings.

If this is the first process that you are configuring, the page that appears will allow you to enter the settings (amongst others) that should have been completed as part of our SFTP/FTP Pre-Requisites guide. 

Process Configuration: Import vs Export Setup

The following settings are required, below is a description of what each setting does:

• Process Name: This is the name of the process and should be something you can easily identify later. For example, avoid generic names like “Import Process.” Consider naming it “Import Updated People from XXX System.”
• Process Type: Can be one of the following:
  • Import: Retrieve a file from the SFTP/FTP Server and import it into Workbooks.
  • Export: Export a Workbooks Report and generate a CSV file written to the SFTP/FTP Server.
• File Name: The name of the file created if using an export process. For import processes, this is the name of the file created by the 3rd party and retrieved by this process.

• Import Template (for import processes only): If you’ve chosen “Import” in the “Process Type” picklist, configure an Import Template selectable in this list.
• Report Name (for export processes only): If you’ve chosen “Export” in the “Process Type” picklist, configure a report showing the data to export and store on the SFTP/FTP Server.
  • Consider the report criteria carefully. For example, if this process runs hourly, you wouldn’t want it exporting every Person record each time — add a criterion limiting results to records updated in the last hour.
  • You must set the Report Category value to “Automation” to see the report in this list.
  • If your report doesn’t appear, verify setup and click the Refresh icon to update picklist values.

Server Access and Authentication Settings

The following settings are server configuration settings and should have been provided by your IT team when the server was provisioned. Please see our Pre-Requisites guide for further detail.

• Server Protocol: This can be one of “FTP” or “SFTP” and depends on how your server is configured.
• Server IP/Web Address: The public IP or web address where Workbooks can access your server.
• Server Port: The port that Workbooks should use to connect to your server. This setting defaults to port 22 and is the most common port used for SFTP/FTP, however your server may be configured differently.
• Server Username: The name of the user that has been configured on the server and one that Workbooks can log into on your behalf to retrieve files (for import processes) or write files (for export processes).
• Authentication Type: This can be one of “Password” or “Public/Private Key” and will depend on how your server has been configured. Whilst a username and password combination is easier to configure, a Public/Private Key pair is more secure.
• Server Password: If you have selected “Password” for your “Authentication Type” then this option is available. Enter the password for the Server Username entered above.
• Server Public Key: If you have selected “Public/Private Key” for your “Authentication Type” then this option is available. Enter the Public Key as generated by your IT team and is stored in your “authorized_keys” file on the server.Your IT team will be able to help you with generating a key pair and installing it in the appropriate location on the server if this is the chosen method of authentication. Please see our Pre-Requisities guide for more information.
• Server Private Key: If you have selected “Public/Private Key” for your “Authentication Type” then this option is available. Enter the Private Key as generated by your IT team that is verified by the Public Key stored in your “authorized_keys” file on the server.

File/Folder Directory Configuration Settings

The following settings are further server configuration settings and should have been provided by your IT team when the server was provisioned. Please see our Pre-Requisites guide for further detail.

• Server Home Directory: When you log in as the supplied user to your server, you will be automatically navigated to the user’s default Home Directory. Enter this folder directory into this setting.
• Import Folder name (for import processes only): Enter the folder path and name of the folder that Workbooks will monitor for new files to process e.g. Workbooks/Import Files.
• Export File to Directory/Folder (for export processes only): Enter the folder path and name of the folder that Workbooks will export new files to, which will be picked up from a 3rd party e.g. Workbooks/Export Files.
• Move or Delete Processed Files (for import processes only): This determines what happens to a file once it’s been imported. You can choose to move the file to a separate folder or delete it entirely.
• Process Folder Name (for import processes only): This option only appears if you have selected to move files after they have been processed in the above option. This setting should be set to the folder path and folder name where Workbooks will move any files that it automatically imports once they have been processed, e.g. Workbooks/Processed Files.

Advanced Settings

• Reportable Import Results (for import processes only): Select “Yes” to enable this process or “No” if you do not require this functionality.
  • When a user carries out an import manually in Workbooks, it can be reviewed and approved or rejected. It is possible to review Errors & Warnings before making that decision.
  • With an automated import, the job is automatically approved to reduce manual intervention; however, warnings and errors are ignored. It is possible to create a separate process so the results of automated imports are stored and made reportable. See our separate forum post
    here for setup details and capabilities.
• Store a Marker File: Some 3rd party systems cannot detect new files without notification. This is typically used when updating the same file with new records rather than creating a new file each time. This setup is supported by the SFTP/FTP script but is uncommon. Enable only if necessary and with care.
• Marker File Name: If you enabled “Store a Marker File” above, specify the name of the marker file to be created. This file name is expected by both the 3rd party system and Workbooks for the process to function.
• File Name DateTime Format: When a file is processed, the date and time of processing are appended to the file name.

• Export Batch Size Files (for export processes only): When a file is exported, choose the maximum number of rows in one file. If there are more than this number of records, the script will create multiple files.
• CSV Delimiter Character: CSV file columns are separated by commas by default (Comma Separated Value file). However, in some instances the file may have been created differently. You can change this setting to the appropriate character as required or leave it as the default (recommended).
• CSV Enclosure Character: The data in a CSV file column can contain double quotes in the data to represent certain text which may cause your file to be processed incorrectly. Specify the character that is used to enclose data and represents a complete string or column of data. You can change this setting to the appropriate character as required or leave it as the default (recommended).
• CSV Escape Character: The data in a CSV file column can contain double quotes in the data and needs to be escaped to be treated as a string, rather than a separate column. By default, CSV files are escaped by a backslash. However, in some instances the file may have been created differently. You can change this setting to the appropriate character as required or leave it as the default (recommended).

Changing Existing Process Settings

If you have existing processes then you will see a list of the existing processes, with the option to create a “New SFTP/FTP Process” or select an existing process from the list, which will allow you to change its settings:

Once you have amended any relevant settings, click “Update Settings” to change the SFTP/FTP processes configuration.