SFTP Integration – Route4Me SFTP Data Sync
Table of Contents
Route4Me’s secure SFTP service permits systems external to Route4Me to upload data to Route4Me’s platform using CSV files without spending time on API/SDK integration with Route4Me’s core platform. Valid files uploaded to the secure SFTP server should contain a list of addresses that need to be optimally resequenced. The secure SFTP system retrieves uploaded files, processes them through the Route4Me optimization system, and then uploads optimized route files back into the secure SFTP server so that external systems can download the processed data.
Route4Me SFTP Sync Processes Overview
|1||Obtain SFTP username and password. To get the corresponding credentials, please contact Route4Me’s Customer Success Department.|
|2||Connect to the SFTP server using the corresponding credentials.|
|3||Create a custom configuration file.|
|4||Upload the configuration file.|
|5||Upload a route data file.|
|6||Route4Me’s system asynchronously and concurrently downloads all uploaded files.|
|7||Uploaded files are parsed and validated.|
|8||If necessary, geocoding is executed on the uploaded file.|
|9||The route data and route geocodes are sent to the Route4Me optimization engine.|
|10||One or more routes are optimally sequenced by the Route4Me system.|
|11||Routes appear inside the Route4Me website.|
|12||Optimized routes are uploaded into the SFTP server.|
|13||Original and Optimized route files are automatically erased from the SFTP server after the expiration period.|
Connecting to the SFTP Server (Through FileZilla)
Route4Me has a proprietary secure SFTP server that is hosted on Google’s Cloud Environment and uses SSH2 (SFTP) protocol during data transit. Data at rest is also encrypted using AES-256.
In this guide, FileZilla® is used as an example (you may use any other software of your choice). First, use your credentials for logging in. To get your username and password, please contact Route4Me’s Customer Success Department. For connecting to the SFTP server, use SSH port 22.
For more advanced log-in options, click on the server icon in the top left corner of the application.
Then, fill out the corresponding fields and click “Connect“ respectively.
Default Route Settings
Route settings are inherited from the account settings associated with the API key being used. If a route date is not provided, the route date will be set approximately to the time of the SFTP upload request in UTC. The route date is only relevant if the user wants to use the Route4Me platform for dispatching and tracking. If the SFTP server is being used for automated route optimization, the optimized route file will be suffixed with the timestamp of when the execution of the route optimization process was completed.
Configuring Route Settings
Route settings for all optimized routes are inherited from the default settings in the account of the API key being used. However, certain configurations can be further customized using a configuration file, which is currently a key/value JSON file. The file should be named “params.json” and placed in the root directory of the SFTP server. You can find the configuration file specifications on GitHub.
The recommended minimum default JSON file contents are the following:
Uploading Files & File Specifications
The file specifications are identical to the format of Route4Me’s Spreadsheets Requirements. Thus, the uploaded spreadsheet can contain such columns as Cube, Weight, Alias, etc. Additionally, you can add the “driver_id” column, and the planned routes will be automatically assigned to the users with the respective specified ID.
The key differentiator with automated SFTP route planning and the Route4Me website is the “route_group” column, which uniquely identifies the existing route structure within an optimized file. If the “route_group” and “seq” columns are not provided, then you will get the following error in the “logs” folder: “Error creating routes: Error file doesn’t have the route_group column required from file”.
|Delete Protected/Prohibited||Folder Name||Description|
|Yes||Original||Contains newly uploaded route data files.|
|Yes||Optimized||Contains the output of successfully optimized route optimizations.|
|Yes||Logs||Contains positive and negative log files of all transactions and activities done within the scope of the SFTP account.|
Once you uploaded the configuration file to the root directory, you can start uploading route data files for optimization. To do so, first, upload a CSV file with route data into the “Original” folder. The name of the route data file should end with “.original” (i.e., “YYYYMMDD_BATCHNAME.csv” → “YYYYMMDD_BATCHNAME.original.csv”).
To optimize the route data file, into the “Original” folder, upload an empty CSV file with the same file name syntax as in the route data file plus “.completed” at the end of the file’s name. For example, if you upload a route data file that is named “YYYYMMDD_BATCHNAME.original.csv”, then you need to upload an empty CSV file named “YYYYMMDD_BATCHNAME.original.completed.csv” to optimize the corresponding route data file.
All optimized routes are re-uploaded into the “Optimized” folder preserving their originally inputted file name syntax. If the original input file is named “YYYYMMDD_BATCHNAME.original.csv” then the optimized output file will be “YYYYMMDD_BATCHNAME.optimized.csv”.
Errors and Troubleshooting
Files that fail to be optimized are displayed in the “Optimized“ or “Original“ folders preserving their originally inputted file name syntax suffixed with “error“ respectively.
Log files in the SFTP server typically cover many data issues, data formatting issues, missing columns, etc. From time to time, unexpected edge cases may occur that do not have any known error codes.
- Attempt workarounds using different cases (lowercase and uppercase): in column headers or file names;
- Look for additional or missing delimiters, such as underscores;
- Look for additional or missing enclosure characters, such as double quotes;
- Cross system-compatibility: Linux – Mac – PC, be aware of newlines and carriage returns;
- Locale: Ensure that ASCII or UTF8 is used for file transmissions and filenames. Ensure that dates and formats are in a format consistent with the requirements.
Working with Custom Data Columns
Route4Me supports passthrough column names so that users can upload almost any number of arbitrarily named columns, and the column data is preserved and transmitted back with optimized files, as well as maintained/stored in the Route4Me website. For example, if there are unique identifiers in columns A, B, C, after route processing, the output file will also have columns A, B, C, with the exact same values that were originally inputted.
Processing & Execution Durations
Typical route processing times are less than 10 seconds for a 100 stop route/route group, with several hundred stops per route consuming about 45-60 seconds.
Concurrency & Rate Limiting
Currently, there are no limits on the number of routes or files that can be uploaded into the SFTP account, however, account-level limits for geocoding and route concurrency are inherited and enforced from the master Route4Me account. By default, the system will optimize one route at a time.
Server Data Expiration Policy
All uploaded files and generated route files are automatically erased after 72 hours. This value cannot be modified at this time but may become customizable in the future.
Visit Route4Me's Marketplace to Check out Associated Modules:
SFTP Data Sync
Advanced Team Management Add-On