Import Lookup Data

You can use Connect to import data records to Lookup Data attribute groups in Responsys Express.

To create an Import Lookup Data job:

  1. Click from any page in Responsys Express.

  2. Use the search to find the Connect option, or click Connect from the menu.

  3. Click Create Job on the Manage Connect page.
  4. Select Import Lookup Data from the drop-down list and provide a name and description of the export job.

    A job name cannot be longer than 100 characters and can include only the following characters: A-Z a-z 0-9 space ! - = @ _ [ ] { }

  5. Click Done.

    The Import Lookup Data page opens. You may complete the steps in any order, and can save your changes and continue at a later time.

    Screenshot of the Import Lookup Data page in Connect.

  6. Complete the steps:
  7. After configuring all the steps, click Activate to make the job active.

    Important: Before you can save or activate the job, you must either set an expiration date, or set the job to never expire. To set an expiration date, click EditAn image of the Edit icon next to Expiration. When the job expires, it is deleted and cannot be recovered. Learn more about managing the expiration date.

After you finish:

  • After you save your job, you can use the Manage Connect page to manage the job. Learn more about managing jobs.
  • When you save your job, Connect may return errors. Click Show Errors to review the errors and quickly jump to the page that needs fixing. You must resolve all errors before you can activate the job.

    An image indicating errors on a Connect job

After an import job runs, the upload file is archived on the server. If the job is successful, the upload file is deleted. If the job fails, the upload file is deleted only in the following circumstances:

  • The file is empty
  • The file has data issues, such as invalid data format
  • The upload file and the count file have different number of records.

Step 1: Source Connectivity

In this step, you provide the location where Connect can retrieve your source file.

Select one of the following options:

  • Oracle File Server: Connect jobs can import data by using your Responsys Express SCP (Secure Copy Protocol) instance file server. This file server includes three directories: upload, download, and archive.

    Important: If not already established, Oracle Support and your IT team must work together to generate an SSH-2 public/private key pair. This ensures secure access to your SCP account via an SSH/SCP client. You may also create your own directories using an SSH (Secure Shell) client.

  • If you select this option, click Change to specify the directory where your files are located.
  • External server by SFTP: If you select this option, provide the following information:
    • Hostname: Select the hostname from the drop-down list.
    • Directory Path: Enter the pathname of the associated directory.
    • Username: Enter the username for accessing your SFTP connection.
    • Authenticator: Depending on the way your server is set up, select either Password or Key.

      If this is your first job using key authorization, click Access or Generate Key Information, and enter the email address to receive the public key and instructions for adding the key to your SFTP account. After installing the public key, click Test Connection to confirm that your SFTP connection configuration is valid.

    Tip: For information about key authentication, see Selecting, Importing, or Generating Public Keys.

Step 2: Source Specification

In this step, you provide information about the file to be imported.

Screenshot of the Source Specification step when setting up an import job.

  • File to upload: The full name and file extension of the file to import. You can add the file creation date as a prefix or suffix.
  • Character set: The character set of the file. If your file contains emojis, you will need to select Unicode (UTF-8) as your character set.
  • Field delimiter: The delimiter that divides the fields (columns) in the file.
  • Field enclosure: Specify whether text columns and values are enclosed in single or double quotation marks.
  • Date format: Select the date format of the import file. For information about supported date formats, see Supported date formats in Connect
  • First line contains column names: Select this check box if the first line in the file contains field names.
  • File is encrypted with PGP/GPG key: Select this check box if the file is encrypted using a key and needs to be decrypted before uploading.
  • File is signed with PGP/GPG key: Select this check box if the file is signed with a key.
  • File to confirm expected record count: Optionally, select this check box and specify a file to use for comparing the record count to the number of imported records. For example, if the expected record count in this file is 300, but your imported file only contains 100 records, a transfer error is noted and the upload process is abandoned

Step 3: Target Definition

In this step, you select the Lookup Data attribute groups that you want to import data to.

Note: You'll also need to select target attribute groups while mapping fields in Step 4.

Screenshot of the Target Definition step of the Connect Import Lookup Data page.

To add a target definition:

  1. On the Target Definition step, click Add + and select the Lookup Data attribute group into which to import data.

    Screenshot showing the attribute group selection and configuration.

  2. In the Configuration section, specify the following options:
    • Insert as new records in target table: If your lookup attribute group does not have a primary key, you can select this checkbox to append records to the lookup attribute group as new records.

      Note: This option only displays if your account allows you to create lookup attribute groups without primary keys. If you're interested in creating lookup attribute group without primary keys, please log in to My Oracle Support and create a service request.

    • Remove all existing records and upload: Select this checkbox to replace the existing records with the newly uploaded records.
    • Merge into table using following field: If you are not removing all existing records, select the field for merging new records into existing records.
    • If there is a match, overwrite all mapped fields in existing records: Select this checkbox to replace existing records with new data when a record matches. If you do not select this checkbox, the incoming record is ignored.
    • If there is not a match, import new record: Select this checkbox to import records if there is no match. If you do not select this checkbox, the incoming record is ignored.

Step 4: Field Mapping

In this step, you map incoming fields from the source file being imported to a destination Lookup Data attribute group and attribute in Responsys Express.

You can manually map the columns to the attributes or use an upload file for the mapping.

Screenshot of the Field Mapping step when setting up an Import Lookup Data job.

Note the following:

  • Long field names are truncated to 30 characters.
  • Field names must begin with a letter or number and may contain only letters, digits, and _ (underscore).
  • When created, field names are not case-sensitive, but are later translated to all uppercase.
  • If any changes result in duplicate field names, you will need to manually rename them.
  • All system attribute names (defined and reserved by Oracle Responsys) end with an underscore character, for example EMAIL_ADDRESS_. As a best practice, uploaded attribute names should not end with an underscore character since these are reserved for system attributes.
  • When possible, match like-named incoming fields with existing attributes, for example, match CUST_ID to CUSTOMER_ID_.

To upload a mapping file:

  1. On the Field Mapping step, click Upload File.
  2. Select the mapping file and complete the details.
    • Fields are delimited by: Select the delimiter (typically a tab or comma) that divides the columns in the file.
    • Fields are enclosed by: Specify whether text columns and values are enclosed in single or double quotation marks.
    • First line contains column names: Select this checkbox if the first line contains field names.

To map fields manually:

  1. On the Field Mapping step, click Add +.
  2. Type the name of the incoming field in the space provided.
  3. Select the target destination for that field.

  4. Select the matching attribute in the target.

    If you do not want to match a field, select skip this field.

    An image showing the Incoming Field mapping

  5. Click Update.

Step 5: Post Processing

In this step, any event or campaign you choose to enable will apply to all profiles and not just the ones being brought in via this connect job.

Step 6: Notification

In this step, you specify whether to send email notifications about successful or failed jobs.

Step 7: Schedule

In this step, you schedule the job. The job can run once on a specified date and time, or on a recurring schedule. To run the job on demand, use the Do not schedule option.

Screenshot of the Schedule step in creating a Connect job.

IMPORTANT: To ensure that leap years and months with 31 days do not cause issues, you cannot schedule a recurring monthly run on the 29th, 30th or 31st day of a month. You can schedule the job to run on a day of the week that is the last in the month, for example the last Friday of the month.

When setting the start time of a job, select one of four time slots within the hour: 0-14, 15-29, 30-44, 45-59. For each scheduled job, the system picks a random minute (for example, 12 within the 0-14 segment). This distributes the start times of jobs more evenly.

The job will start at random times within the chosen time slot. If you try to set up a new job in a slot that overlaps with the current time, an error message may be issued that states that the time selected occurs in the past. Therefore, as best practice, choose the time slot that is after the current time slot.

Learn more

Import Contact Data

Export Data

Export Event Feed Data

Manage Connect Jobs