How to Create a Bulk Data Loader

Overview

This article will detail the process needed to create a bulk data loader. A bulk data loader is needed before you can mass upload data into the application. The function of the bulk data loader is to map the fields in your delimited data to fields within the application.

Data can be provided in delimited format. The most common of which is CSV (comma separated values), although you may use any type of delimiter, which will be defined in later steps. The data required will differ depending on the entity in which you are loading data into. Please see the following sections to see how the data requirements will change. 

Parent Entity

A parent entity is an entity that does not belong to another entity. Records within this entity do not belong to another record. An example of this would be an account, an account does not belong to any other record (unless we are dealing with nested accounts). Loading into a parent entity can be done without reliance on other records. As far as the data needed, you will need to ensure all required fields are present, as well as all additional data you wish to load.

Child Entity

Loading into a child entity means you will need a reference to a parent record. An example of this would be a billing profile. In order to load a billing profile the system would need to know the account in which it belongs to. Because of this you will need to include references to parent records in the data you wish to upload. This will be explained in a later step, the important part here is ensuring your data is compiled with relations to parent records. Each entity will contain at least one field that is marked as an External Key, this field is unique and can be used to identify records. The data in the child entity should be tied to the parent record using the external key. For example, pricing plan data should include a tie to the account it belongs to. If we look at the fields for account, two fields are designated as external keys, name, and account ID, both of which must be unique. One of these fields must be present in the pricing plan data.

Types of Bulk Data Loaders

There are three types of Bulk Data Loaders you may create, each defined below.

Insert

Insert type data loaders are used to put new information into the system, if a record exists in the data that has already been loaded, it will be inserted again given unique field restrictions are not broken.

Update

Update type loaders will look for existing records with matching external keys, and if found, will update the existing record with any new data provided in the upload data.

Upsert

Upsert type loaders are a combination of insert and update types. Upsert will look for an existing record with a matching external key, if found the record will be updated. If the record is not found, it will insert a new record using the data provided in the upload data.

Configuring the Bulk Data Loader

Step 1. Select the Entity in Which to Load

Locate the Bulk Data Loader under the Setup tab, under the Data Management category. Select the new button to begin the process. The first step will ask which entity you wish to load data into. Select the appropriate entity. The form will look like below: 



Step 2. Select the Operation

The next step is to select the correct operation depending on which type of load you need to perform. More information on the types of data loaders can be found above. Select a radio button to make your selection. Select next to continue. The options will be presented as below:

Step 3. Upload Sample Data

Use the data compiled for the upload and select the file here. This data will be used to compile the headers so you may map them in the next step. The data does not need to be complete, but the format should be correct. The form will look like below:

 

Step 4. Configure the Bulk Data Loader

The Bulk Data Loader setup page will look like below. Please use the table beneath the image to complete the configuration.

Bulk Data Loader Configuration

Field Description
Bulk Operation This is a static field that will show you the type of bulk loader you are creating.
Name Provide a descriptive name that will help you identify the correct loader. This must be unique.
Status

Provide a status for the data loader. Available options include:

  • Deactivated - Deactivate the data loader.
  • Deleted - Delete the data loader.
  • Production - Describes a current and active data loader.
  • Test - Used to test data loader functions when using ftp to collect data. When using test mode, system will not reach out to ftp location at set intervals until switched from Test to Production.
Delimiter Provide the delimiter used in your data. The most common is a comma (,) that is used in CSV (comma separated values) type files.
Text Qualifier This is the character that will surround text strings in your data, the most common being quotation marks (").
Upsert Field

This is only required if performing an update or upsert type data load. The field allows you to tell the data loader which field to use to match existing records, to new data within your file. The only fields available here will be fields marked as external keys during the field setup. The field must be unique. 

When performing an update, the system will look for the value of the field defined in the upsert field, then if it finds the exact same file in the data file you upload, it will update the current information with the information in the data you upload.

When performing an upsert, the system will look for a matching record with the value of the upsert field provided, if it finds the record, it will update new information. If the record is not found, then it will insert a new record with the data within the file you upload.

Update to Null This field only pertains to update and upsert type data loaders. When updating, if checked, fields not provided/populated in the source data, will be updated to null values. If unchecked, then fields not provided or populated will be left alone.
Object Field Map Use this grid to map your fields from the source data to the field in the entity. System fields will be in the left column and not alterable. Use the fields in the right column to map the fields from your uploaded data. If this is the first time you are configuring the loader, then the field will be a drop down type, populated with the fields grabbed from the previous step (uploading the sample). Use the drop down to map the column to the appropriate system field. If this is a future update to the loader, you will need to type the column name exactly as it appears in your data.
Parent Object Field Map Some fields in a form may be reliant on other data in the system. An example of this is an invoice template in the billing profile information. An invoice template in the system is stored as an ID, but the UI will show a user friendly label. Using the parent object mapping, you may still provide the user friendly label in the data you will upload, it will look for an exact match, and fill in the appropriate ID automatically. Map the parent object fields using the right hand column.
FTP Host URL If you wish for the system to pull data files from an FTP location, define the host URL here.
FTP Host Username Provide the username for the system to log in to your FTP host here.
FTP Host Password Provide the password for the system to log in to your FTP host here.
FTP Directory Provide the correct directory where the system will find the files to upload.
FTP File Pattern Provide the pattern in which the files to be uploaded will be names. An example: myFile*.csv. Asterisks are wildcards, so in this example, the system would pickup both myFile1.csv and myFile2.csv, but not THISfile.csv or myFile1.dat.
Start Date If you wish for the system to pickup files automatically at an FTP location, provide the date for the system to being looking for the files.
Schedule Interval

This tells the system how often to looks for new files, options include:

  • Every Minute
  • Every Hour
  • Once per Day
  • Once per Week

 

Step 5. Save and Submit a File

Select the Submit button to save your Data Loader. If you have setup FTP data in the loader, all that is left to do is wait for the interval to start. If you want to manually upload a file, continue on to the next tutorial, How To Upload a Bulk Data Loader File.

Have more questions? Submit a request

Comments

Powered by Zendesk