Many data services and business flows use FTP (File Transfer Protocol) to securely move data files from one location to another. These processes may be on-demand or on a routine schedule.
Omatic Cloud offers two methods for retrieving and using data files, such as Excel files:
- File Upload - Search a folder on your local machine, or drag-and-drop for on-demand file processing
- Secure FTP - Stay connected to an FTP folder, and set a schedule for automatic file processing
How to use FTP as a Data Source
- Step 1: Creating a Data Source connected to your FTP Server
- Step 2: Creating and Editing your Formula
- Step 3: Running your Formula
Step 1: Creating a Data Source connected to your FTP Server
Before creating a new Data Source for connecting to an FTP Server, you should make sure everything is compatible with our recommendations:
- Omatic does not require or recommend using a specific FTP service. However, Omatic Cloud does require using a Secure FTP protocol. Both SFTP (SSH) and FTPS are supported. Click here learn more about the different FTP protocols.
- If you are unsure about how to access the FTP service that your team uses, you may need to contact your IT professional. If you are hosted by Blackbaud, you can click here to see detailed information on preparing to connect to Blackbaud Hosting Files.
- To minimize errors due to overlapping or conflicting formulas, you must create a unique subfolder for each formula you build. The subfolder should only contain the data files you plan to upload (1 per scheduled run). Prior to building your formula, make sure the subfolder contains a single example file, which will be used to create formula's field map.
- You can create your new FTP Data Source from either the adding a new integration on the Integrations/Formulas page or by starting a new formula wizard on the Data Queue page.
- Enter your main URL. Do not enter the path to your subfolder yet; that will be handled in the formula.
- Username is always required.
- For SFTP: The port number is usually 22, but confirm with your IT professional. Enter your username and either a password or SSH Key.
- Click Save to test the connection. If you do not know what to enter here, contact your IT professional for assistance.
Here's an example that uses ExaVault as the FTP service:
Step 2: Creating and Editing your Formula
- Create a new formula from your Data Queue or Integrations/Formulas pages.
- Select the existing Secure FTP option as your data source. If you do not have an existing FTP source, follow the steps above to create one.
- Insert the path to the correct subfolder.
Tip: You can usually view the info/properties on a file in your subfolder to locate this info. If you don't see a button to display that option, try right-clicking on your example file in the folder to view details. Some FTP services use different roots for different users, so make sure you're logged in as the same username you used to create the Data Source.
Here's an example from one FTP service, ExaVault (the appearance will vary, based on your service):
The subfolder you use for this formula must stay the same, and it should only contain data for this formula. This may require you to adjust other business processes prior to creating or using the formula.
When Omatic Cloud retrieves a file for processing, it will rename the file to add the date and time of retrieval as an extension. This prevents duplicate uploads of the same file, and it helps you to see when a specific file was retrieved. These files can remain in the folder indefinitely. If you want to rerun the file, you can simply remove the timestamp from the file name. If you want to open it and view the contents, you can simply download a copy from your FTP server, and then remove the timestamp from the file name.
- Next, select your formula type, and complete the mapping, matching, triage settings just as you would for any other File Upload formula.
- Finally, set the scheduled time you would like for Omatic Cloud to search for a file. Currently, you can select either daily or weekly pickups. We plan to expand scheduling options in the future, to allow for more frequent processing. If you're unsure about when the file will be available, set the schedule to weekly and review the notes below about running this formula on-demand.
Step 3: Running your Formula
- Each day or week, depending on your formula's schedule, Omatic Cloud will attempt to retrieve a file from the specified FTP subfolder. It will retrieve, rename, and process the file, according to your formula settings.
Note: To fully automate the process, you can adjust your triage settings to move as many records to Ready as possible and automatically send Ready records to the destination.
- Omatic Cloud expects to find a single, eligible file. If no file is present, it will just check again on the next scheduled run time. If more than one file is present, an error will occur. To resolve, open the folder and temporarily remove the extra file(s), then use the Run Once feature on the Integrations/Formulas page to run the formula on-demand. Repeat this process until all files are processed. Alternatively, you can create a matching File Upload formula that would allow you to drop the files one-by-one after downloading them from your FTP server.