Getting Started with File Drop Bulk Data Management

GoSpotCheck's SFTP File Drop Service allows customers to manage the data within GoSpotCheck using spreadsheets uploaded to a configured folder in an SFTP client. With SFTP File Drop, customers have the flexibility to determine the precise field(s) they'd like to use as the matching key, automate updates from their internal system of record and dictate the precise actions new uploads should perform on the data currently in GoSpotCheck. 

In order to get started with bulk data management via SFTP, we'll first need to configure your SFTP Client and then familiarize ourselves with the service's folder structure and workflows.

Configure your SFTP Client

Uploading files to the GoSpotCheck file server is supported via the Secure File Transfer Protocol (SFTP). In order to connect to the server, you will need an SFTP client. It is possible that your company already provides a SFTP client. If not, there are many options available for free such as Cyberduck, or FileZilla. Configuration of these tools can vary and you may need to work with your company's technology team to get connected. Once you have the SFTP client installed, you can use the credentials provided by your Customer Success Manager to log-in and connect to the file server.

File Drop Folder Structure

Upon successfully connecting to the file server, you will see a standard set of folders:

• catalog_items
• info_urls
• uaps

Additionally, each folder will have two subfolders:

• uploads: this is where you will upload file templates for processing
• results: this is where you will review the results for the job

Folder Configuration

Prior to being able to process your uploaded files, most* folders need to be configured so that GoSpotCheck knows how to interpret the files uploaded, map the updates to the correct GoSpotCheck Data, and what actions to take on that data.

Configuring your folders upload settings is a two-part process:

1. Determine Matching Key(s): The first step in every job is to match the file to existing records in GoSpotCheck. This helps prevent the creation of duplicate records and informs which updates need to be executed. The folder will be configured to match on key field(s) that you specify. The following example illustrates how this is useful:
   
   Example: You export your Places from another database to be imported in GoSpotCheck. You map the ID from the database to the custom_place_id field in GoSpotCheck. When you upload files for place updates you want the file to be matched against custom_place_id.
   
   
   NOTE: Match fields must always be included in the uploaded file otherwise the job will fail. Also, because these fields are the matching key, they cannot be updated by this process. Changes to match fields must be applied in GoSpotCheck.
   
   
2. Determine Update Actions: in addition to specifying how the file is matched to GoSpotCheck, you can specify what should happen once the records have been matched (or unmatched). The folder can be configured to make GoSpotCheck look like your file, or it can allow you to make updates incrementally.
   

Your CSM will work with you to help determine your preferred matching keys and update actions and configure the folder accordingly. Once configured, the matching key(s) and update actions will remain in place for all SFTP uploads to your company instance (from any user) until such time you desire to change them. Changing folder configuration settings once again requires working with your GoSpotCheck CSM and review of a few considerations.

File Types and Limits

The Bulk Data Management service can read and process three types of files:

• Comma-separated values (CSV)
• Microsoft Excel (XLSX) - only the first worksheet will be read
• Pipe-delimited Text (TXT)

Processing Schedule

The file server is checked for new files approximately every 10 minutes. Files are then queued for processing with the smallest files being prioritized first. Actual processing times can vary based on the number of files in the queue as well as the number of records in the file.

Each file processed is limited to 600 records per minute. So a file with 3,000 records, for example, would take approximately 5 minutes to process.

Understanding Results

Once a file has been processed, a few things will happen:

1. A new timestamped subfolder will be created in the results directory.
   1. For example: places/results/2020_01_01_000000/
2. The original file will be moved from the uploads/ folder to the new timestamped folder.
   1. For example: places/uploads/place_updates.xlsx -> places/results/2020_01_01_000000/place_updates.xlsx
3. Results files are added to the timestamped folder, and given the following suffixes:
   • _success.csv: Records in the file that were successfully created, updated, or disabled will written be written to a file with the suffix _success.csv.
     • In the example above, success records would be written to a file called: place_updates_xlsx_success.csv
   • _errors.csv: Similarly, any errors that occurred in the process will be written to a file suffixed _errors.csv.
     • In the example above, the error records would be written to a file called: place_updates_xlsx_errors.csv

Troubleshooting Errors

If you receive an error file, don’t panic, most errors are easily resolved such as ensuring a file has the correct field names. Opening the error file, you will see that a new “Errors” column has been added in front of your data. Review the messages in this column as it will tell you how to resolve the problem.

I didn’t receive any errors. Why is only part of my original file in the _success.csv results? Did the rest of it fail?

Data is only updated if it has changed. If a record in the file already exists in GoSpotCheck and nothing has changed, no action will be taken. When no action is taken, the record is not included in the success results file. The results files reflect actions that were attempted and whether or not they were successful or unsuccessful.