Transferring Data with the Express Data Loader

Objectives

After completing this lesson, you will be able to:

  • Transfer data with the Express Data Loader

Transferring 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.

Downloading the Import File Templates

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 them 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.

XDL File Naming and Format Requirements

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:

[Tenant ID]_[File Type Code]_<DEV or TST or PRD>_<yyyymmdd>_<hhmiss>_<Content Tag>.<file-suffix>

  • The Tenant ID is the four digits at the beginning of the URL. This is a code designated by SAP to uniquely identify the owner of the tenant. For example, the Tenant ID for Bikes In Motion may be BIKE.
  • The File Type Code identifies the type of data being imported. The list of import file templates contains the file type code. For example, the file type code for accounts is TQAC.
  • DEV, TST or PRD identifies the type of tenant: development, test, or production.
  • YYYYMMDD is the date; for example 20220101
  • HHMMSS is the exact time; for example 1:00 PM would be 130000.
  • The Content Tag is a short text string that can contain any value to uniquely identify the batch. In this case we will use the content tag JanAccounts.

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:

  • Save the file as a delimited text file with the suffix .txt.
  • The delimiter must match the one selected in the Inbound File Type Settings in XDL. In this case we will use a tab delimiter.
  • Likewise, the encoding must match the one selected in the Inbound File Type Settings in XDL. In this case the file will use UTF-8 encoding.
  • The file can have an optional header row, but be sure to indicate if a header row is used in the Inbound File Type Settings.

Configuring Express Data Loader

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.

To configure global settings:

  1. From the application picker, select Express Data Loader.
  2. Select Configuration – Global Settings.
  3. Set the compression mode. The default is Gzip, but in our example, we will not use a compression mode.
  4. Set the time zone. This is used for the timestamp on the job.
  5. Select a character encoding attribute (Charset). The default is UTF-8.
  6. Select a delimiter. The default is tabdelimited.
  7. Select a retention period for error logs and jobs. The defaults are 30 days and 90 days, respectively.
  8. Enter an email for notification of success and failure emails.
  9. Activate the Autorun switch. Indicate whether the import should run automatically once the file upload is executed.
  10. 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 ("").
  11. Select Save.

To configure inbound file type settings:

  1. Select Configuration – File Type Setup.
  2. Scroll or search for the file type. The example below shows the setup for Territory and Quota Accounts: TQAC.
  3. Select the file type to configure. In this case we will select TQAC.
  4. Select the pencil icon to edit the file type setup.
  5. Leave the Charset (character encoding attribute) set to Default. This will default to the UTF-8 setting we selected in the global setup.
  6. Leave the delimiter at the default. This will default to the tab delimiter we selected in the global setup.
  7. 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.
  8. Select the switch to proceed with an import if errors are found in validation.
  9. Select the Header switch.
  10. Select Save.

Performing an Inbound File Transfer

Now we are ready to use Express Data Loader to load the new accounts into Territory and Quota.

To upload the import file in Express Data Loader:

  1. Select the Jobs menu in the left navigator.
  2. Select the Upload icon () on the toolbar.
  3. Navigate to the location of the tab-separated text file and select Open.
  4. 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.

Performing an Outbound File Transfer

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 TypeExtract File Name
TerritoryAccountTERRITORYACCEXT
TerritoryGeographyTERRITORYGEOEXT
TerritoryProductTERRITORYPRODEXT
TerritoryQuotaTERRITORYQUOTAEXT
AccountACCOUNTEXT
GeographyGEOGRAPHYEXT
ProductsPRODUCTEXT

Outbound File Format and Naming Requirements

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):

accountId@Revenue@ADDRESS1@CITY@STATE@POSTALCODE@EFFECTIVESTARTDATE,@STATE = "CA"

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":

Territory 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 <tenantid>_<outboundname>_<optionalstring>.txt.

  • The Tenant ID is the four digits at the beginning of the URL. This is a code designated by SAP to uniquely identify the owner of the tenant. This is the same as the Tenant ID used for inbound files.
  • The Outbound Name is the file type name. In the two examples we used above, this would be ACCOUNTEXT or TERRITORYQUOTAEXT.
  • The Optional String is a short text string that can contain any value to uniquely identify the batch.

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.

Uploading the extract file

Now, we can add the extract file in Express Data Loader using the following steps:

  1. Log in to Express Data Loader (XDL).
  2. Select the Jobs menu in the navigator in the left panel.
  3. Select the Upload icon () on the toolbar.

Log in to track your progress & complete quizzes