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 system or a 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. Collecting the data you want to import and organizing it into .csv, .txt, or .xlsx files.
2. Configuring the settings for the data import with the Data Transfer Manager, which tells Case IQ how to import the data.
3. Running the data import or scheduling when Case IQ should run it.

This article covers step 1 in this process by explaining how to format and structure your source data and data files. If you are preparing to import file attachments, see the Import File Attachments in Bulk article instead, as the process is slightly different. If you have already prepared your data files, refer to Configure, Edit, or Test Data Import Settings with the Data Transfer Manager and Run or Schedule a Data Import for steps 2 and 3 respectively. 

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

Data File Requirements Checklist

While this article explains all our data file requirements in detail, we have also created this quick checklist for your reference. This checklist is also available as a .xlsx file, so you can check off requirements as you complete them. In step 2 of the data transfer process, you can also validate your data file in Case IQ when configuring your data import settings to check if the Data Transfer Manager can import your source data.

DataFileChecklist.xlsx

Source Data File Format

Make sure the source data files you want to import into Case IQ meet the following format requirements:

Source Data File Structure

Case IQ can read data that is in a specific structure in .csv, .txt, or .xlsx files. Organize the data in your data files into the following structure:

Source Data File Structure Requirements
1 source data file/import	Include all data for an import in one data file. For a recurring data import, you can upload a new data file to Case IQ's SFTP for each scheduled import.
1 row/record	Make sure the data for each record is contained in one row of the data file. For example, the images below show a sample data file for parties. The data for each party is contain in one row in this data file.
1 column/field	Ensure each field has its own column. In the images below, there is a "First Name" column, which contains each record's first name.
For case data file:  1 column/workflow field	If you are preparing a data file for cases, create a separate column for each workflow field you want to set. For example, suppose you have three workflows in your application, called "HR Investigation", "Security Investigation", and "Fraud Investigation", and you want to set a status for each of them via the import. Your data file would need to contain the following three columns: "HR Investigation Status", "Security Investigation Status", and "Fraud Investigation Status".
Unique identifier column	Include a column for a unique identifier, such as an email address or identification number. Each record must have a different value in the unique identifier field, so that Case IQ can use each unique identifier value to find any records that have already been added into the system. If a row in the data file and a record in Case IQ have the same unique identifier, Case IQ will consider those the same record.
For email, note, party, to-do, or custom form data file: Reference record target column	If you are preparing a data file for emails, notes, parties, to-dos, or custom forms, make sure the data file contains a column that tells Case IQ to which case the system should add each record. When configuring your data import's settings, you can map this column to a Case IQ field. We generally recommend using the case's identification number or name in your source system. In the images below, this is the "Legacy Case Number" column.
For user profile data file: SSO user column	If you are preparing to import users into Case IQ and your application has an Single Sign On (SSO) integration, the system can automatically set them as SSO users via the import. In your source data file, you can add a column called "SSO User". For each user you want to set as an SSO user, enter "true", "yes", or "1" for the user's row in the "SSO User" column.  To configure SSO for your application, see Set up Single Sign On (SSO).
For .csv or .txt:  1 record type/data file.	If you are using .csv or .txt format data files, make sure each data file only contains data for one record type. If you plan to migrate multiple record types into Case IQ, create a data file for each record type. For example, if you are migrating data into cases and parties, you would need a data file for parties and another separate data file for cases.
For .xlsx:  1 record type/worksheet.	If you are using .xlsx format for your data file, ensure each worksheet in the .xlsx file only contains data for one record type. Name each worksheet by record type. For example, if you are migrating data into cases and parties, organize all case data in one worksheet and all party data in another. The system can only import data from one worksheet in the file at once. When running an import, the system will look for a worksheet with a name that includes the data import's entity. For example, if you are importing cases, it will look for a worksheet named "cases". If the Case IQ cannot find such a worksheet, it will import the first worksheet in the .xlsx file.
No scattered empty rows.	Remove any scattered empty rows in your data file, as they will cause errors during an import. However, empty rows at the end of a dataset will not cause errors.

Text String Format

Your text string data, such as descriptions or email body content, may contain text characters that are ambiguous for Case IQ. By adding double quotation marks (") around text with these characters, the system can properly populate your data in Case IQ fields.

Date Format

Dates in .csv files are stored as plain text. However, spreadsheet software, like Microsoft Excel or Google Sheets, may automatically reformat date values using your regional settings. If you open your source data files in these applications before importing them, dates may not appear in the correct format. You can follow the steps below to prevent this automatic reformatting.

Phone Number Format

The Data Transfer Manager supports a variety of standard phone number formats, which it can automatically detect in your source data file and import into Case IQ. See all phone number formats that Case IQ can accept in the Supported Phone Number Formats chart. The phone numbers in your source data file must include a country code.

If your source data uses an unsupported phone number format, you can still migrate your phone number data into Case IQ. When configuring your data import settings, you can manually set the phone number format that your data uses in the Overrides section. 

Supported Phone Number Formats

Match Data to Field Type

Each type of field in Case IQ can only accept certain kinds of data. For example, textboxes, text areas, and picklists can contain text strings, like names and descriptions. If you import text to an integer, number, or money field, the text could not be displayed properly in Case IQ. Likewise, if you import numeric data into a textbox, text area, and picklist field, Case IQ would treat the numeric data like text, so you would not be able to use that data to run calculations in the application.

While preparing your source data file, consider how your organization intends to use the data you want to import. To do this:

1. Plan the Case IQ fields into which you want to import data. You can add fields to record types if necessary, see Edit a Standard Record Type and Add and Edit a Custom Form Type for details.
2. Check the type for each field. You can check a field's type in the data dictionary, under the System tab in Settings, see detailed steps in the Generate a Data Dictionary File article. We have also listed the field types for the standard profile fields in the Static Profile Field Types section of this article.
3. Find each field type in the chart below, Field Type Formats and Requirements.
4. Confirm that your source system data meets the requirements detailed in the Field Type Formats and Requirements chart.

Field Type Formats and Requirements

Static Profile Field Types