Objective
After completing this lesson, you will be able to Transfer data with the Express Data Loader.
Another method for loading and extracting data to and from Territory and Quota is the Express Data Loader (XDL). XDL is a cloud-based tool that allows you to securely import and export bulk data to and from the system without the need for coding.
XDL can be used to import data from source systems, and to extract quotas into Incentive Management. Here, the quotas can be used to calculate commissions and other incentives from sales transactions.
XDL data is loaded using import files with very specific naming and formatting requirements. Therefore, the easiest way to format the import files is to download the collection of templates. The full collection of import file templates is downloaded as a single .ZIP file from the XDL online help page. This page also contains a table with the template name, file type code, and description for each template. The image below shows the download link in the online help.
Once you have downloaded the .ZIP file to your local system, you can extract the files to a folder.
As we go through this topic, we will look at a scenario in which Bikes In Motion would like to use XDL to import new customer accounts each month.
The table on the Express Data Loader Templates page shows that accounts are imported using the file TQAC_Template, which has a file type code of TQAC.
File naming requirements for XDL are very precise. Each template has a tab outlining the specific requirements for that file type, but as a general rule the file should be named with this format:
1[Tenant ID]_[File Type Code]_<DEV or TST or PRD>_<yyyymmdd>_<hhmiss>_<Content Tag>.<file-suffix>If we put these all together, the file name might be
BIKE_TQAC_PRD_20220101_130000_JanAccounts.xlsx
In addition to the name, the file also has the following formatting requirements:
The next step is to configure file requirements. This includes three steps: global settings, inbound file type settings, and outbound file type settings.
Global settings are default settings that apply to both inbound and outbound file transfers. Global settings include such options as the default time zone, character encoding, compression type, and the e-mail recipient for success and failure notifications.
Inbound file type settings include the standard settings and metadata of each file that is imported. A file type must be configured for each template file, which means that every template must have an associated file type. Settings include the file type, import options, and target staging table. You can also override the default character set and delimiter by setting these in the file type settings.
Outbound file type settings include the standard settings and metadata of each file that is exported, such as accounts or quotas. Settings include the outbound file type, source stored procedure, and compression mode. As with inbound file type settings, you can override the default character set and delimiter by setting these in the file type settings.
Let’s look at how we can configure both global and inbound settings to match the import file we created in the previous topic.
From the application picker, select Express Data Loader.
Select Configuration → Global Settings.
Set the compression mode. The default is Gzip, but in our example, we will not use a compression mode.
Set the time zone.
This is used for the timestamp on the job.
Select a character encoding attribute (Charset). The default is UTF-8.
Select a delimiter. The default is tabdelimited.
Select a retention period for error logs and jobs. The defaults are 30 days and 90 days, respectively.
Enter an email for notification of success and failure emails.
Activate the Autorun switch. Indicate whether the import should run automatically once the file upload is executed.
Select an escape delimiter.
This setting specifies if comma separated files, which have a delimiter as part of its data, can be loaded. When this option is enabled, you can use double quotation marks for importing values which have a delimiter as part of the field ("").
Select Save.
Select Configuration → File Type Setup.
Scroll or search for the file type. The example below shows the setup for Territory and Quota Accounts: TQAC.
Select the file type to configure. In this case we will select TQAC.
Select the pencil icon to edit the file type setup.
Leave the Charset (character encoding attribute) set to Default. This will default to the UTF-8 setting we selected in the global setup.
Leave the delimiter at the default.
This will default to the tab delimiter we selected in the global setup.
Set the import type to Validate and Transfer. This will automatically transfer the records from the staging table to production after the import is complete.
Select the switch to proceed with an import if errors are found in validation.
Select the Header switch.
Select Save.
Now we are ready to use Express Data Loader to load the new accounts into Territory and Quota.
Select the Jobs menu in the left navigator.
Select the Upload icon (
) on the toolbar.
Navigate to the location of the tab-separated text file and select Open.
Wait for the job to complete.
Once the job is complete, you can navigate back to Territory and Quota to verify the records have been loaded in the workspace.
When extracting data from Territory and Quota, a different type of template is used. The outbound template is a UTF-8 encoded text file that contains a SQL SELECT statement. A set of sample files can be downloaded using this link (note, this is a direct download). This creates a .zip file that contains a collection of text (.txt) files.
Extract the .zip file to a folder. The following table contains a list of the available extract files.
| Data Type | Extract File Name |
| TerritoryAccount | TERRITORYACCEXT |
| TerritoryGeography | TERRITORYGEOEXT |
| TerritoryProduct | TERRITORYPRODEXT |
| TerritoryQuota | TERRITORYQUOTAEXT |
| Account | ACCOUNTEXT |
| Geography | GEOGRAPHYEXT |
| Products | PRODUCTEXT |
The SELECT statement lists each column to be extracted separated by an @ sign, followed by a WHERE clause designated by a comma.
Let’s look at two examples of extract files and the syntax used to select columns and filters:
To extract accounts, we would use the ACCOUNTEXT file. The following contents would extract the Account ID, Revenue, Address, City, State, Postal Code, and Effective Start Date from all accounts in which the state is equal to California (CA):
1accountId@Revenue@ADDRESS1@CITY@STATE@POSTALCODE@EFFECTIVESTARTDATE,@STATE = "CA"It is also possible to include custom fields if needed.
To extract quotas in a territory program, we would use the TERRITORYQUOTAEXT file. The following contents would extract quotas from a territory, including the territory program name, territory, quota value, and final quota value, if the effective start date is on or after January 1, 2010, the territory program is "NPR TP", and the target type is "revenue":
1Territory Program@Territory@QuotaValue@finalQuotaValue,@EFFECTIVESTARTDATE >= "20100101" and @TERRITORY PROGRAM = "NPR TP" and @Target Type = "revenue"The extract file will be saved with the naming convention
1<tenantid>_<outboundname>_<optionalstring>.txt. Using our two examples, our Accounts extract file would be named BIKE_ACCOUNTEXT_2022Accounts.txt, and the Quota extract file would be named BIKE_TERRITORYQUOTAEXT_2022Quotas.txt.
Note
The last part of these file names can be any string.Log in to Express Data Loader (XDL).
Select the Jobs menu in the navigator in the left panel.
Select the Upload icon (
) on the toolbar.
Log in to track your progress & complete quizzes