Importing Data Into PeopleSoft Enterprise CRM

This chapter provides an overview of the Data Import Application Engine process (RBIMPORT) and discusses how to:

• Set up data import global settings.

• Define data import templates.

• Run the Data Import process.

• View the Data Import process status.

Understanding the Data Import Application Engine Process (RBIMPORT)

This section lists prerequisites and provides an overview of the Data Import process.

Prerequisites

Before you can import data into PeopleSoft Enterprise Customer Relationship Management (PeopleSoft Enterprise CRM), you must ensure that:

• The Extract Transformation Load (Data Transformer) is set up correctly in the environment.

  See PeopleSoft Data Transformer for CRM 8.9 PeopleBook

• The PeopleSoft super user ID has CREATE TABLE privileges for dynamic table creates.

• The FTP server is defined so it can store import file attachments.

  To do this, modify the RB_IMP_ATTACH URL identifier by using the URLs - URL Maintenance page in PeopleTools.

  See Enterprise PeopleTools 8.45 PeopleBook: Server Tools

• You define Data Import global settings.

Data Import Process

The Data Import process enables you to:

• Batch-load external data from other systems (in flat file format) into the PeopleSoft Enterprise CRM database.

  This is mainly used to load companies, contacts and consumers. You also can generate marketing lists from the data you load.

• Update existing individual and organization records to supplement profile data for better targeting and personalization of marketing efforts.

Data Import process flowchart

To import data into PeopleSoft Enterprise CRM:

1. Create a flat file with one of the accepted delimiters (tab, comma, or semicolon) for the import.

2. Define an import template by using the Define Import Template component.

   1. Define import template information (name, role to associate with imported data, and audience to associate with template, if applicable).

   2. Define field mapping requirements.

   3. Define matching criteria.

3. (Optional) Run the Data Import process by using the Run Data import page.

   To validate the template against the data file contents, select the File Validation Only check box to perform validation tests of the data.

   The file validation step checks the file contents and structure and writes errors to an error file, which you can use to correct problems prior to running the Data Import process.

   Examples of validations that are performed are: length of fields in the import file compared to field lengths that are in the database, compatibility of data types, and number of delimiters in each row.

4. View the status of the Data Import process by using the View Import Status page.

   You can view import dates, times, and statuses, such as: inprocess, queued, and successful.

5. View the results of the Data Import process by using the View Import Statistics page.

   You can view the number of inserted, matched, updated, and in-error import rows.

6. Correct any data errors that don't successfully load in the process and perform the Data Import process again.

Setting Up Data Import Global Settings

To set up data import global settings, use the Data Import Global Settings (RB_IMP_GLB_SETUP) component.

This section discusses how to define data import global settings.

Page Used to Set Up Data Import Global Settings

Defining Data Import Global Settings

Access the Data Import Global Setup page.

Default SetID	Enter the default setID. The Data Import process enters the default setID on any target table that requires a set ID while it processes import data. Examples of tables which require setIDs are: RD_PERSON and RD_COMPANY.
Currency Code	Enter the default currency code, which the Data Import process uses for target tables that require a currency code.
Last Object Id Sequence	Enter a starting integer. Use this field when you create object IDs that are not system-generated. The format of generated IDs is sequence number:counter. For example, if my starting sequence number is 1, and you import a file with 100 companies, the format of the generated IDs is 1:1 through 1:100. The subsequent load of 50 company objects generates IDs 2:1 through 2:50.

Stage Table Generation

Defining Data Import Templates

To define data import templates, use the Data Import (RB_IMP_DATA_CONFSR_GBL) component.

This section discusses how to:

• Access data import templates.

• Define data import templates.

• Define import files.

• Map source fields to target fields.

• Define matching criteria.

• Verify template creation.

Pages Used to Define Data Import Templates

Accessing Data Import Templates

Access the Data Import page.

The appearance, behavior, and personalization options for this page are controlled by the search definition for the Data Import Template component.

Defining Data Import Templates

Access the Data Import - Step 1: Define Import Template page.

Import with Role	Select the role to associate with the import template. All records that are in the file are imported into the BORM with the selected role. Values are: Company: Select for company or partner company records. Company/Contact: Select for individual records for persons who function as contacts for companies or any type of organization. Consumers: Select for persons who do not function as contacts for companies. Note. The role that you select here determines the objects that you can map to. For example, if you select Consumer, the only fields that are available to map to are those that are defined for the Consumer role.
Source	(Optional) Enter the source of the information that is contained in the flat file. This field is associated with the template itself as opposed to individual records that are within the file. To associate a source with each record, create a text profile field that is called Source and use the Data Import - Step 3: Mapping page to map a constant value into that field.
Import Status	Displays the import status that the system assigns when you begin to create an import template. Initially, the status is In Process. It stays as such until you complete the steps to define an import template, at which time it changes to Active.
SetID and Audience	Enter an existing setID and audience of type Internal using Import to bring the import data into the PeopleSoft Enterprise CRM database as a discrete audience. Or, to create a new audience, click the Transfer to Audience button to access the Audiences - Audience Details page and identify the name, audience ID, and owner. Note. If you import the records as an Internal by using Import audience, you can use the audience immediately after import. However, you can't further segment the audience in the Audience Builder. If you want to do further segmentation rather than create an audience at the time of import, create a text profile field called Audience Source, and use the Mapping step to map a constant value in that field for all records. After import, create an internal audience by using the Audience Builder with the first condition being that all records that are equal to the constant value that is mapped into the profile field called Audience Source. See Creating and Managing Audiences.
Next	Click to access the Data Import - Step 2: Define Import File page and proceed to the next page in the import definition process.

Defining Import Files

Access the Data Import - Step 2: Define Import File page.

On initial access to access this page when you are defining a new template, the Attach File button is available; the rest of the fields are empty. The Delete File and View File buttons are not available until you attach a file.

To define an import file:

1. Attach the flat file to the import template.

   1. Click the Attach File button; a dialog box appears.

   2. Click the Browse button on the dialog box and select the desired flat file.

   3. Click the Upload button.

      The system attaches the flat file to the import template definition, and the Data Import - Step 2: Define Import File page appears.

2. (Optional) Click the View File button to view the rows of information that are contained in the flat file.

3. Define flat file information.

   • Select the flat file delimiter (Comma, Semicolon, or Tab).

   • Define header row information.

     If the flat file contains a header row, select the Headeroption and also enter the header row location in the Header Row field. If you do not enter a header row number, the system assumes that it is 1.

     If the flat file doesn't contain a header row, select the No Header option.

   • Identify blank rows in the flat file.

     If you have blank rows at the top of the flat file, enter the number of blank rows that you want the system to skip in the Skip Lines field.

4. Attach the flat file fields to the import template.

   Click the List Fields From File button. The system attaches the flat file fields to the import template.

   If the flat file contains a header row, the system populates the Field Name column with the exact names of the fields (for example, lname and fname) that are contained in the header row of the flat file's header row. The system also populates the Field Label fields with the same information as shown in the field name fields.

   If the flat file doesn't contain a header row, the system populates these columns with FIELD1, FIELD2, and so on until all of the flat file fields are represented.

5. (Optional) Change the field labels to represent more meaningful and understandable names to the flat file fields.

   For example, the field name lname may need a field label of Last Name to assist others who may review the template for reuse.

6. Click the Next button to access the Data Import - Step 3: Mapping page and proceed to the next step in the import template definition process.

Deleting Import Files

To delete an import file:

1. Display an existing import template definition that already has an import file attached on the Data Import - Step 2: Define Import File page.

2. Click the Delete File button.

   When you click this button, the system deletes the flat file from the import template definition as well as all field and mapping definitions that are from the import template. At that time, you can select another flat file.

Mapping Source Fields to Target Fields

Access the Data Import - Step 3: Mapping page.

When you access this page, the source field labels appear in the Step 3: Mapping scroll area, and the Advanced scroll area is collapsed.

You must map at least one source field that is contained in the flat file to a target field in the PeopleSoft Enterprise CRM database. The fields that you must map depend on the target fields that you select. A message appears if you do not map all required fields (required fields have an asterisk at the end of the field name in the folder tree view).

If you can perform one-to-one mapping of the source fields to the target fields, you only need to use the Mapping scroll area.

If you must use a default constant in a target field, concatenate source fields into one target field, or enter a source field into multiple target fields, you must use both the Mapping and Advanced scroll areas.

Mapping Source Fields to Target Fields

To map the source flat file fields to target fields:

1. Select the target field for each source field.

   1. Click the Select Target link next to a source field label to access the Select Target Field page, which displays the target fields that are in the PeopleSoft Enterprise CRM database in a tree format. The tree format depends on the role that you select when you define the import template. An asterisk appears next to the fields that PeopleSoft Enterprise CRM requires. At each folder level, there are required fields. For example, at the highest folder, Individual-Consumer, the required field is Last Name. Down one level, in the Phone folder, Contact Method Purpose ID and Telephone both have an asterisk; while Telephone is not required for an import. If the folder is mapped, then you must map any required fields in that folder or in the folders above it. In this example, you must map both Contact Method Purpose ID from the Phone folder and Last Name from the higher-level Individual-Consumer folder.

   2. Find and select the target field by expanding the folders and viewing the fields on the Select Target Field page.

      When you click the field in the tree, the Data Import - Step 3: Mapping page appears with the field description in the Select Target field for this particular field.

2. Select the import action for each field:

   Ignore	Select to ignore this field and not import it when you perform the import process. This is useful for when you reuse import templates and want to import only a subset of the premapped fields.
   Import	Select to import this field when you perform the import process.

3. Define required fields for the flat file import process.

   Select the Required Flag check box if this field must have a value in the flat file before the data is imported into PeopleSoft Enterprise CRM.

   For example, if Name is defined as a required field on the flat file and you attempt to import a flat file that does not contain name information, those rows of data on the flat file are not imported into PeopleSoft Enterprise CRM; they are written to the error file.

4. (Optional) Select the Optional tab in the Step 3: Mapping page region to define fields to group together.

   Enter the same number for every field that in the group in the Sequence field. For example, suppose that you have a flat file that contains this information on one row, as indicated in the Source Field column:

   Source Field	Sequence	Target Field
   First Name	1	Consumer.First Name
   Last Name	1	Consumer.Last Name
   Email Address	1	Email.Email Address
   First Name	2	Consumer.First Name
   Last Name	2	Consumer.Last Name
   Email Address	2	Email.Email Address

   In this case, the same type of information is repeated on one row. For the system to understand what fields should go together when they are inserted into the database, you must assign a sequence number. In this example, the first three fields are grouped together and are inserted into the database as a group, and the last three fields are grouped together and are inserted into the database as a group. You must always map a consistent number of objects with a file in this format. For example, if you map two instances of First Name, you must also map two instances of Last Name and two instances of Email Address.

   If you create a flat file that has multiple rows with the information instead of one row, you don't need to use the Sequence field. By virtue of the information being on separate rows, the system knows what fields to group together.

5. Click Next to access the Data Import - Step 4: Matching page and proceed to the next step in the import process.

Note. You perform the functions in the remainder of this section in the Advanced region of the page.

Mapping Concatenated Source Fields to Target Fields

To map concatenated source fields to a target field:

1. Expand the Advanced region and select a mapping action of Concatenate.

   The system displays the Specify Concatenation column with a Specify Concatenation link.

2. Select the target field.

   • Click the Select Target link next to the row in the Select Target column to access the Select Target Field page and view the target fields that are in the PeopleSoft Enterprise CRM database in a folder tree view.

     The folders and fields that appear are dependant on the role that you select when you define the import template. An asterisk appears next to the fields that PeopleSoft Enterprise CRM requires. At each folder level, there are required fields; if a field in a folder is mapped, then you must also map any required fields in that folder or in the folders above it.

   • Open the folders to find the target field that you want, and click the field to access the Data Import - Step 3: Mapping page, which displays the field description in the Select Target field for this particular row.

3. Enter the fields or values that you want concatenated.

   • Click the Specify Concatenation link next to the row to access the Specify Concatenation page.

   • Enter the target fields for concatenation, and select a concatenation type.

     If you select a concatenation type of Value, enter a value in the Value field.

     If you select a concatenation type of Field, select the field that you want to concatenate in the Field Label column.

   • Click OK to access the Data Import - Step 3: Mapping page, which displays the concatenation criteria in the Specify Concatenation column for this row.

Mapping Constants to Target Fields

To map a constant value to a target field:

1. Select a mapping action of Constant next to a row in the Advanced scroll area.

   This makes the Value field available.

2. Select the target field.

   • Click the Select Target link next to the row that is in the Select Target column to access the Select Target Field page, which displays the target fields in the PeopleSoft Enterprise CRM database in a folder tree view.

     The folders and fields are dependent on the role that you select when you define the import template. In addition, an asterisk appears next to the fields that PeopleSoft Enterprise CRM requires. At each folder level, there are required fields; if a field in a folder is mapped, then you must also map any required fields that are in that folder or in the folders that are above it.

   • Find the target field in the tree on the Select Target Field page, and click the node of the tree for that field to access the Data Import - Step 3: Mapping page, which displays the field description that you enter in the Select Target field for this particular row.

3. Enter the value that you want to appear by default in the target field in the Value field.

   To enter data, time, or date values, please consult the Enterprise PeopleTools PeopleBooks to determine the correct formats.

Mapping Already-Mapped Source Fields to Other Target Fields

To map an already mapped source field to another target field:

1. Expand the Advanced scroll area and select a mapping action of Field next to a row.

   This activates the Field Label field.

2. Select the target field.

   • Click the Select Target link that is next to the row that is in the Select Target column to access the Select Target Field page appears, which displays the target fields that are in the PeopleSoft Enterprise CRM database in a folder tree view.

     The folders and fields are dependant on the role that you select when you define the import template. An asterisk appears next to the fields that PeopleSoft Enterprise CRM requires. At each folder level, there are required fields; if a field in a folder is mapped, then you must also map any required fields that are in that folder or in the folders that are above it.

   • Find the target field that is in the tree on the Select Target Field page and click the field to access the Data Import - Step 3: Mapping page, which displays the field description that is entered in the Select Target field for this particular row.

3. Select the already-mapped field label in the Field Label field.

Defining Matching Criteria

Access the Data Import - Step 4: Matching page.

This page enables you to specify the fields in the PeopleSoft that must match fields in the import file before the database is updated from the import file. If the system doesn't find a match, it inserts the incoming row as a new record in the PeopleSoft Enterprise CRM database. If it finds a match, the system updates the existing record with the new information from the file, unless you clear the Update Matched Records check box at import time. In that case only, the system writes matched records to the error file instead of being updated.

Note. New data never overwrites or updates an existing record's contact method fields (phone, email, and address); the new fields are appended as additional contact methods. All other types of mapped fields are updated or overwritten by the new data for matched records.

Warning! All fields, including email address, are case sensitive for matching. To avoid instances where a match is not found due to case sensitivity, you should convert fields such as email address to lowercase prior to import. For example, convert [email protected] to [email protected] before importing the file.

Enter the matching criteria that you want to associate with the import template. Use the Open Bracket and Close Bracket options appropriately for the nested condition on which to match. For example, it's always a good idea to match on a combination of email address and last name or of any contact method and name information (for example, email address and lastname or phone and lastname). For the Company/Contact import type, it's good to match on at least one field from the Company mapping, and at least one field from the Contact mapping or Contact Method mapping.

Verifying Template Creation

Access the Save Confirmation page.

This page displays:

• A message that indicates if the template is saved successfully.

  The Import status is set to Active.

• The Run Data Import link.

  Click this link to access the Run Data Import page and immediately run the import process.

• The Go Back to Import Definition link.

  Click this link to access the Data Import - Step 1: Define Import Template page and view the existing template and make any necessary changes.

Running the Data Import Application Engine Process (RBIMPORT)

This section discusses how to:

• Access the Raw Data Import page.

• Run the Data Import Process.

Pages Used to Run the Data Import Application Engine Process (RBIMPORT)

Accessing the Run Data Import Page

Access the Data Import search page.

The search definition for the Run Data Import component controls the appearance, behavior, and personalization options for this page.

See Also

Configuring Search Pages

Running the Data Import Application Engine Process (RBIMPORT)

Access the Data Import page.

If you access this page from the menu, you must attach a flat file and define its parameters. When you access this page as the last step when defining an import template, the system populates the flat file information for you.

Note. Attaching a flat file on this page works as on the Data Import - Step 2: Define Import File page .

File Information

File Field List

The File Field List screen region displays the fields that are associated with the import template. This enables you to verify whether the import works in the case where you access the Data Import process directly from the menu and use an existing template.

Import As Audience

Import Status

The Import Status group box appears only after you click the Import Data button.

Other Page Links and Buttons

Viewing the Data Import Process Status

This section discusses how to view the status of the Data Import process.

• View instances of the run date import process.

• View the status of one data import process instance.

• View import statistics.

• View the data import process file error.

Pages Used to View the Data Import Process Status

Viewing Instances of the Run Data Import Process

Access the Data Import page.

The search definition for the View Import Status component controls the appearance, behavior, and personalization options for this page.

See Configuring Search Pages.

Viewing the Status of One Data Import Process Instance

Access the Data Import page.

View File	Click to view the flat file that is used for the Data Import process.
View Error File	Click to download and view the error log file that the Data Import process creates. The error log contains details about the rows that are in error so that you can fix them and rerun the process. If you do not select Update Matched Records on the Data Import page, the error log also contains a listing of the records that match but are not updated.
View Import Statistics	Click to access the View Import Statistics page and view the import statistics, number of rows inserted, updated, and matched, and the total number of records that are processed.

Viewing Import Statistics

Access the View Import Statistics page.

This page displays the import statistics from the Data Import process.

Viewing the Data Import Process Error File

Access the error file.

The error file looks just like the input flat file except that after every row there are messages about what is wrong with the input row. Correct the problems with data on this error file and then rerun the Data Import process by using this file.