Case IQ Knowledge Base

How can we help?

Configure, Edit, or Test Data Import Settings with the Data Transfer Manager

Data Transfer Manager  Premium Feature 
Reach out to your Customer Success Manager or Account Executive to learn more about this feature.

You can use the Data Transfer Manager to import data from another system into your Case IQ application via a Secure File Transfer Protocol (SFTP) integration. This allows you to efficiently populate records in Case IQ with data from an external system, such as your organization’s human resources information or legacy case management system. You can migrate data into cases, emails, files, notes, parties, profiles, to-dos, user profiles, and any data forms or custom forms in your Case IQ application.

Transferring data into Case IQ involves the following steps:

  1. Collect the data you want to import and organize it into .csv, .txt, or .xlsx files.
  2. Configure the settings for the data import with the Data Transfer Manager, which tells Case IQ how to import the data.
  3. Run the data import or schedule when Case IQ should run it.

This article covers step 2 in this process and explains how to configure a data import's settings for any record type, excluding file attachments. For details on setting up an import for file attachments, see Import File Attachments in Bulk. Make sure your data file is ready before configuring your data import's settings, see Prepare a Data File for Import for Case IQ's data file requirements. If you have already prepared your data file and configured your data import's settings, refer to Run or Schedule a Data Import for details on step 3 in this process. 

1) An example data file. 2) A data import in Case IQ. 3) The Import Data pop-up in Case IQ.

We use several technical terms in this article to explain how a data import works. See a glossary of important terms below:

Term Definition
Source data file A .csv, .txt, or .xlsx file containing the data you want to bring in to Case IQ. Generally, your organization would export this data as a file from your source system.
Data import settings The settings that you configure in Case IQ to tell Case IQ how to import data from your source system.
Import entity or record type One kind of record in your Case IQ application, for example cases, emails, files, notes, parties, profiles, to-dos, users, and any data forms or custom forms configured in your Case IQ system.
Map The process of matching data from your source system to your Case IQ application. This tells Case IQ into which fields to populate data. 
For example, suppose you are preparing to import user accounts into Case IQ and your source system has a field called "Employee email". You can map that source system field to the user profile's "Email" field in Case IQ. When you run the import, the system will add the data from the source system's "Employee email" field to Case IQ's "Email" field.
Secure File Transfer Protocol (SFTP) A secure method of transferring files from one system to another.
Source system The application containing the data you want to bring in to your Case IQ system. For example, your organization's human resources information, ticketing, customer relationship management, or legacy case management system.
Source field A column in your source data file, which reflects a field in your source system. For example, if your source system has a "Department" field, you can migrate the data contained in that field by adding a "Department" column in your source data file.
Target field A field of the import entity into which Case IQ can migrate data. For example, if you are migrating cases, you can select Case IQ's "Date Submitted" field as a target field. This allows you to migrate the date and time that the case was originally added in your source system into Case IQ.
Unique identifier A source field that identifies each record in both your source system and Case IQ, such as an email address or identification number. You choose the unique identifier when configuring the data import's settings (see "Map Fields" tab in the Configure a Data Import section of this article). Each record must have a different value in the unique identifier field.
When running a data import, the system will compare the unique identifier values in each source data file row with records in Case IQ. If a row in the source data file and a record in Case IQ have the same unique identifier, Case IQ will consider those the same record. 
For example, if you choose "Case Number" as the unique identifier for a case data import, the system will look through all Case IQ case numbers to determine if any match the case numbers in the source data file. If a case in Case IQ has the same case number as a row in the source data file, the system will consider those the same case.

Configure a Data Import

You can configure the settings for your import by adding a "data import" in your Case IQ application using the Data Transfer Manager. If you plan to import data into multiple record types, you will need to add a separate data import for each record type. For example, if you are migrating data into cases and parties, you would need configure a data import for parties and another separate data import for cases. 

In your application's Settings, navigate to the Data Transfer Manager page under the System tab. Click the Add Data Import button on the Data Transfer Manager page, which will load the New Data Import form. 

Data Transfer Manager page in Settings under the System tab.

The New Data Import form includes the following sections, so you can configure each aspect of your data import and ensure the Data Transfer Manager properly processes your data: Configure Import, Advanced Options, Overrides, Identify Relationship, and Map Fields. "Identify Relationship" is only applicable when configuring an import for emails, notes, parties, to-dos, or any custom form types. See details on each section in the tabs below.

Delete

Importing File Attachments?

For details on importing file attachments into Case IQ, see Import File Attachments in Bulk.

1. Configure Import

In the Configure Import section, you can enter basic information about the import you are setting up, like the record type you want to import and the file format of your source data files. Complete the following fields: 

  • Name: enter a name to refer to the data import.
  • Import Entity: choose the Case IQ entity, or record type, into which you want to import data. For example, if you want to import records from your source system into Case IQ profiles, choose "Profile" as the "Import Entity". 
    • You can select the following record types in which to migrate data: cases, emails, files, notes, parties, profiles, to-dos, user profiles, and any data forms or custom forms in your Case IQ system.
    • For specific information on importing file attachments, refer to Import File Attachments in Bulk. 
  • File format: select your source data file's format. You can select .csv, .txt, or .xlsx.
  • Delimiter: if you select .csv or .txt as the source data file format, choose the delimiter used in your data files. You can select comma- or pipe-delimited for .csv or .txt files.
  • Update Method: choose how the system will import data into Case IQ records. The system can determine if your source data file includes records that already exist in Case IQ based on the unique identifier you will select in the Map Fields section of the New Data Import form. Select one of the following options:
    • Replace all existing records: the system will delete all import entity records currently in the application, then add all records from the source data file into Case IQ. "Replace all existing records" will only be available when you have selected profiles or data forms as the import entity.
    • Update existing and insert new records: the system will update records with a matching unique identifier in the data file. Then, it will add a record for any rows in the data file that do not have a matching unique identifier in Case IQ. You can use this method to bulk update or delete records in the system, see the Update or Delete Records in Bulk article for details.
    • Only insert new records: the system will add a record for any rows in the source data file that do not have a matching unique identifier in Case IQ. It will not update any existing records in Case IQ.
  • Error Notification Recipients: enter an email to receive notifications when the system processes the data import.
  • User Lookup: choose how the system will search for users in your application to populate in user or user multiple fields. When importing data into a user or user multiple field, the system will look for a matching user profile in Case IQ to associate with the field. For example, if you choose "Email", the system will look for a user profile in Case IQ that has a matching email address. 
  • Default User Role: when you select "User Profile" as the "Import Entity", choose the user role the system should give to imported users as a fall back. You can map the user role field to a source field in the data file, so each user profile has their correct role. However, if a user profile in your data file does not have a valid user role, the system will give the user profile the "Default User Role" you select. 
    • We recommend setting the "Default User Role" to a lower set of permissions, so you do not grant more permissions to users than intended.

Delete

2. Advanced Options and Overrides

In the Advanced Options and Overrides sections, you can configure additional settings for your data import if necessary. The Data Transfer Manager is designed to automatically detect and handle a variety of standard data formats, but, in the event it is unable to parse certain data, you can use the Overrides options to tell the Data Transfer Manager the exact format to use to accurately populate source data in Case IQ.

To see these additional settings, check the “Show Advanced Options” checkbox in the Advanced Options section. This will display the following Overrides options:

  • Date format override: we recommend using this option only if your source data's date format is not ISO, meaning that your dates are not formatted like: YYYY-MM-DD. If your date values are not in ISO format, select the date format your source data uses in the "Date format override" option. Dates in other formats may be ambiguous to the Data Transfer Manager, so it will refer to your "Date format override" selection to accurately populate data.
    • If your source data's dates are in ISO format, the Data Transfer Manager can automatically handle your source data dates, so using the "Date format override" option is unnecessary.
    • In your application, dates will be displayed in the date format set in Options (see Update Application Options), even if your source data's date format is different.
  • Encoding override: select the encoding type your source data file uses. We recommend using this option only if the Data Transfer Manager cannot detect your source data file's encoding.
  • Phone number override: we recommend using this option only if the phone number format in your source data is not supported by the Data Transfer Manager (see Prepare a Data File for Import: Supported Phone Number Formats). To set the "Phone number override", enter all digits in your phone number format, including the country code, using “X” characters as placeholders. Include any special characters in the phone number format. 
    • For example, suppose your source system data contains phone numbers with number signs, dashes, and brackets, like “#11(325)-545-7333". You would enter #XX(XXX)-XXX-XXXX" in the “Phone number override” field.
  • Country code override: if you are using the "Phone number override" option, enter the country code to use for all phone numbers in your source data. 
    • For example, if your phone numbers include 11 as the country code, you would enter “11” in the “Country code override” field. If any phone number values in your data have a different country code than the “Country code override” you set here, the system will not be able to import that row’s data and log an error.

Delete

3. Identify Relationship

If you select emails, notes, parties, to-dos, or custom forms as the "Import Entity", you can choose how the system will look up each imported record's case in the Identify Relationship section. You will not see this section if you are setting up an import for cases, profiles, or user profiles. If the Identify Relationship section is displayed, complete the following fields in the grid:

  • Source: enter a column header from your source data file that the system should use to identify the case for each imported record. The source field is generally the case's identification number or name in your source or legacy system.
  • Reference Record Type: "Case" will be the reference record type by default for emails, notes, parties, to-dos, and custom forms.
  • Reference Record Target: select a case field in Case IQ that corresponds with the source field you selected. During the import, the system will look for a match between the reference record target field in Case IQ and the source field in your source data file to determine each record's case. 
    • We recommend selecting "Case #", "Source Id", or "Id" as the reference record target, as each case in your system will have a unique case number, source ID, or ID. This means that the system will always be able to find the correct case for the imported records.

Delete

4. Map Fields

Lastly, you can tell the system what data to populate in Case IQ fields. In the Map Fields section, add a row for each column in your source data file, then select the Case IQ field into which the system should populate that data. 

You can add fields in the source column by either automatically generating them based on your source data file or manually filling them in, both of which are detailed in the sections below.

Autogenerate Source Column Fields

  1. Click the Autogenerate Source Column button in the Map Fields section. 
  2. In the "Autogenerate source column" pop-up, upload the source data file you have prepared for this import. Make sure it contains all column headers for the data you want to import.
  3. When your data file has been uploaded, click the Autogenerate Source Column button in the pop-up.
  4. The system will add a field mapping row for each column in your source data file and set the first column in the data file as the unique identifier.
  5. In the Target column, select one of the record's fields in Case IQ for each field mapping row. You can select any dynamic or static field on the import entity record in Case IQ as a target field, such as the "Date Submitted" field. You can only map one source data file column to a field.
  6. Set any fields that are required for a record by checking the "Required" checkbox. If a row in the source data file does not have a value for a field you set as required, the system will not add a record or update an existing record for the row during the import.
Delete

Autogenerating Source Columns for Child Record Types

When you autogenerate source columns, the system will add a row for the source column you selected in the Identify Relationship section. Mapping this field is not necessary, as it indicates the case for each child record (e.g. parties, notes, to-dos, etc.). Make sure to delete the row for this source field in the Map Fields section.

Delete

Manually Map Fields

  1. Click the Add Field Mapping button. 
  2. In the "Add new mapping" pop-up, fill in the following fields:
    1. Source: enter a column header that is in your source data file. 
    2. Target: search for and select a field on the record in Case IQ into which you want to import data. You can select any dynamic or static field on the import entity record in Case IQ as a target field, such as the "Date Submitted" field. You can only map one source data file column to a field.
    3. Required: select whether the field is required for a record. If a row in the source data file does not have a value for a field you set as required, the system will not add a record or update an existing record for the row during the import.
  3. When you have finished filling in the "Add new mapping" pop-up, you can click Save to add the mapping or Save & Add Mapping to quickly map another column in your source data file.
    1. If you are setting up a data import for a child record type (e.g. parties, notes, to-dos, etc.), you do not need to map the source column you selected in the Identify Relationship section.
  4. When you have finished adding field mappings, select a unique identifier. Click the Options button () in the field mapping grid row that you want to make the unique identifier. Then, click the "Set as Unique Identifier" option. You can only select one unique identifier for a data import.

Delete
After adding field mappings using either method, you can edit them as needed.
  • To delete a field mapping, click the row's Options button () and select the "Delete" option.
  • To select a different field as the unique identifier, click the row's Options button () and select the "Set as Unique Identifier" option. You can only select one unique identifier for a data import.
  • If you would like to start mapping from scratch, click the Options button () for the grid and select the "Clear Field Mappings" option. This will delete all rows in the field mapping grid.
Delete

After filling in the Configure Import and Map Fields sections, as well as the Overrides and Identify Relationship sections if necessary, click the Save button on the New Data Import form. You have now finished configuring your data import's settings.

Edit a Data Import's Settings

If you need to update your data import's settings, you can edit them from the data import's page:

  1. Click the Edit button. 
  2. The data import's page will now be in edit mode. You can now update the data import's settings, relationships, and field mapping.
  3. When you have completed your changes, click the Save button. The system will use your updated settings next time it runs the import.

Download a Sample Data File

After saving a data import, you can download a file of all source fields outlined in the data import's field mapping section. You can use the sample header file as a template to create your data or to compare with a data file you have already prepared. The data files you will later upload to Case IQ's SFTP server should include the column headers in the sample header file. To download sample headers:

  1. Click the Options () button on a data import's page.
  2. Select the "Download sample headers" option. 
  3. Your browser will download the .csv file of column headers.

Validate a Source Data File

After configuring a data import's settings, you can test if the source data file you prepared aligns with the data import's settings and that the source data can be properly imported. First, the system will run high-level validation to confirm that the source data file uses the file format and delimiter that you configured for the data import's settings. After passing the initial tests, the system will run row-level validation on the data. To run validation on a source data file:

  1. Click the Options () button on the data import's page.
  2. Select the "Validate a data file" option. 
  3. Upload the source data file you prepared for this import to the "Validate a data file" pop-up in the "Upload file" box. The system will run high-level validation to confirm that the source data file:
    1. Uses the file format and delimiters you selected for the data import's settings.
    2. Contains column headers. 
    3. Has a consistent number of delimiters for each row.
  4. If there are problems with the source data file format, the system will list the first error it finds. You can correct the error in the source data file and reupload it to the pop-up. 
  5. The system will confirm when the source data file has passed high-level validation. Then, click the Validate rows button in the pop-up and the system will run further validation in the background to check that each row has a unique identifier and the correct data type for each column. You can navigate away from the data import's page while the system is running this validation.
  6. When the system has finished validating the data, you will receive a notification to confirm if the source data file can be used to import data.
    1. If the validation was successful, you can click the Go to data imports button in the "Data file validation successful" notification to begin importing the data you just tested.
    2. If the system found errors in the source data file, you can click the View integration log button in the "Data file validation failed" notification to see the rows in which the errors occurred.

Delete a Data Import's Settings

If you need to permanently remove a data import's settings, you can delete it by clicking the Delete button on the data import's page. If you scheduled an import to run later using this data import's settings, the system will not be able to run the import when scheduled. You can edit the "Schedule Process" rule to change the data import (see details in Run or Schedule a Data Import).


sftp profile import sftp profile sftp credentials sftp password data file format schedule data import schedule sftp schedule profile import peoplesoft

Related Articles