Case IQ Knowledge Base

How can we help?

Add or Edit a Data Import

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

Activate and configure a Secure File Transfer Protocol (SFTP) integration for your Case IQ application on the Data Import page under the System tab in Settings. With an SFTP integration, administrators can import data from another application (such as the organization’s human resources information system) or a legacy case management system to populate profiles, data form entries, cases, parties, and user profiles in Case IQ. 

This article covers the steps to activate an SFTP integration by adding a data import for any record type, excluding file attachments. For details on importing file attachments into Case IQ, see Import File Attachments in Bulk. If you have already set up a data import and want to schedule the process, see the Schedule a Data Import section of this article. 

Data Import page in Settings under the System tab.

Delete

Before Setting Up a Data Import...

Make sure your organization is prepared to post data files to Case IQ's SFTP server. Reach out to your Account Executive for your SFTP credentials.

Data File Format

Data files uploaded to Case IQ's SFTP server must meet the following requirements:

  • All data for an import must be included in one data file. For a recurring SFTP data import, you can upload a new data file for each scheduled import.
  • Data files must be in .csv, .txt, or .xlsx format. 
    • We use RFC 4180 standards for .csv files, which you can review in the Common Format and MIME Type for Comma-Separated Values (CSV) Files memo.
    • The system can only import data from one worksheet in an .xlsx 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 it cannot find such a worksheet, it will import the first worksheet in the .xlsx file. 
  • You can import a maximum of 500,000 records per import.
  • The maximum file size is 200MB.
  • You can only import data into one record type at once. See the Migrating from a Legacy System to Case IQ section of this article for details on importing multiple record types into your system.
  • Each record's data must be in one row in the data file, meaning that there is one case, profile, data form entry, etc. per row. Likewise, one field per column.
  • Add double quotation marks (") around text values with commas (,), e.g. "John, a witness, spoke to the subject about the incident".
  • Add double quotation marks (") around array data. To separate each element, use one of the following delimiters: commas (,), newlines (\n), tabs (\t). For example, a list of emails using newlines would look like: 
    "maria@email.com
    rashid@email.com
    john@email.com"
  • No other special characters require double quotes, such as apostrophes (' and "), ampersands (&), dashes (-), number signs (#), and slashes (/ and \).
  • When importing cases, you can set certain workflow fields for each imported case, including workflow status. 
    • Each workflow field you want to set should have a separate column in your data file. For example, if you have three workflows in the application and you want to set a status for each of them, there should be three columns for workflow statuses in your data file. 
    • Make sure the workflow is active before running the case data import. See details on activating workflows in Edit, Activate, Deactivate, or Delete a Workflow.
  • Basic HTML formatting is supported for migrated emails. This includes headers, basic tables, bolding, italics, and bulleted lists. More complex HTML may not appear as expected when viewing migrated emails in Case IQ.
  • Data file names must begin with the "Unique file name prefix" set in the SFTP data import rule action (see the Schedule a Data Import section).

Remove Empty Rows Before Importing

Make sure there are no empty or blank rows in a data file. Scattered empty rows will cause errors during an import. However, empty rows at the end of a dataset will not cause errors. 

Migrating from a Legacy System to Case IQ

Welcome to Case IQ! If you are migrating your previous case management system to your new Case IQ application, make sure to:

  1. Create a data import for each record type into which you want to migrate data (see the Add a Data Import section for details).
  2. Run the data imports (see the Import a Data File section for details) in the following order: 
    1. User profiles.
    2. Cases.
    3. Profiles.
    4. Child records (emails, files, notes, parties, to-dos, and any custom form types in your application) and data forms.

For example, suppose you want to migrate data into Case IQ cases, users, and parties. You would need to create three data imports, one each for cases, users, and parties. This allows you to configure the settings for each import separately. After adding all the data imports:

  1. First, run the users data import. 
  2. When the system has imported all user profile data, run the case data import. 
  3. Lastly, import the party data to complete your migration. 

This order allows the system to properly create connections between records in your Case IQ application. For example, cases in Case IQ contain user fields, like "Case Owner" or "Created By", which refer to users in your system. 

Add a Data Import

Click the Add Data Import button on the Data Import page to get started configuring the settings for your data migration. The New Data Import form includes multiple sections, so you can configure each aspect of your data import and ensure the system properly processes your data: Configure Import, Identify Relationship, and Map Fields. "Identify Relationship" is only applicable when adding a data import for emails, notes, parties, to-dos, or any custom form types. See details on each in the sections below.

Delete

Importing File Attachments?

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

1. Configure Import

First, complete the following fields in the Configure Import section:

  • 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 data file's format. You can select .csv, .txt, or .xlsx.
  • Delimiter: if you select .csv or .txt as the data file format, choose the delimiter used in your data files. You can select comma- or pipe-delimited for .csv or .txt data files.
  • Update Method: choose how the system will import data into your application. The system will use the unique identifier that you set in the Map Fields section, such as an employee ID or email address, to match rows in the data file from your source system with records in Case IQ. A data file row and Case IQ record with the same value in the unique identifier field will be considered the same record. Select one of the following options:
    • Replace all existing records: the system will delete all records of the import entity currently in the application, then add all records from the 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 data file that do not have a matching unique identifier in Case IQ. It will not update any existing records in Case IQ.
  • Date Format: select the format that your data file uses for date values. Case IQ will refer to this date format to accurately populate dates in your application. If your data file's date format is different than the in-application date format set in Options (see Update Application Options), the system will transform date values to your application's date format.
  • Error Notification Recipients: enter an email to receive notifications if the system cannot process an 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 user profiles than intended.

Delete

2. 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. You will not see the "Identify Relationship" 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 data files 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 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

3. Map Fields

Lastly, you can tell the system what data to populate in Case IQ fields by filling in the Map Fields section. You can either automatically generate the source column based on your data file or manually fill it 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 a data file from your source system. 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 data file and set the first column in the data file as the "unique identifier". 
    1. The unique identifier acts as each record's ID and tells Case IQ how to identify a single record. During an import, the system will look for data file rows and Case IQ records with the same unique identifier. Rows and records with the same unique identifier are considered the same record.
    2. Every record must have a different value for their unique identifier, such as an email address or employee ID.
  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 data file column to a field.
    1. Make sure that the imported data matches the format and requirements for the target field, see the Field Type Data Formats section of this article for details.
  6. Set any fields that are required for a record by checking the "Required" checkbox. If a row in the 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.
  7. You can edit the field mappings as needed.
    1. To delete a field mapping, click the row's Options button () and select the "Delete" option.
    2. 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.
    3. 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

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 field column in the Map Fields section.

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 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 data file column to a field.
      1. Make sure that the imported data matches the format and requirements for the field, see the Field Type Data Formats section of this article for details.
    3. Required: select whether the field is required for a record. If a row in the 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.
  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 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, you can select a "unique identifier", which tells Case IQ how to identify a single record. 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.
    1. During an import, the system will look for data file rows and Case IQ records with the same unique identifier. Rows and records with the same unique identifier are considered the same record.
    2. Every record must have a different value for their unique identifier, such as an email address or employee ID.
  5. You can edit the field mapping if needed.
    1. To delete a field mapping, click the row's Options button () and select the "Delete" option.
    2. To select a different field as the unique identifier, click the row's Options button () and select the "Set as Unique Identifier" option.

After filling in the Configure Import and Map Fields sections, as well as the Identify Relationship section 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

You can update a data import 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 and field mapping.
  3. When you have completed your changes, click the Save button.

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. The sample header file serves as a template to prepare your data. The data files you will 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 "Download sample headers" option. 
  3. Your browser will download the .csv file of column headers.

Validate a Data File

After adding a data import, you can test a data file to confirm it is in the correct format and its data can be properly imported. First, the system will run high-level validation to confirm that the data file uses the file format and delimiter set for the data import. After passing the initial tests, the system will run row-level validation on the data. To run validation on a data file:

  1. Click the Options () button on a data import's page.
  2. Select the "Validate a data file" option. 
  3. Upload your data file to the "Validate a data file" pop-up in the "Upload file" box. The system will run high-level validation to confirm that the data file:
    1. Is in the file format set for the data import. 
    2. Uses the delimiter set for the data import. 
    3. Contains column headers. 
    4. Has a consistent number of delimiters for each row.
  4. If there are problems with the data file format, the system will list the first error it finds. You can correct the error in the data file and reupload it to the pop-up. 
  5. The system will confirm when the 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 page while the system is running the validation.
  6. When the system has finished validating the data, you will receive a notification to confirm if the 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 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.

Import a Data File

From a data import's page, you can upload and import a data file from your source system into your Case IQ application. The system will begin processing the data file immediately using the data import's settings. If you would like to schedule a recurring data import on a set frequency, see the Schedule a Data Import section in this article. 

Delete

Schedule File Attachment Imports

You cannot run an import for file attachments from the data import's page. See the Import File Attachments in Bulk article for details.

To run a one-time import of a data file:

  1. Click the Options () button on a data import's page.
  2. Select the "Import data" option. 
  3. The "Import data" pop-up will be displayed. If you have already validated a data file, click Import data. 
    1. If you have not validated a data file yet, you can upload your data file in the "Upload file" box in the "Import data" pop-up. The system will run high-level validation to confirm the data file contains column headers and a consistent number of delimiters for each row. 
    2. If there are problems with the data file format, the system will list the first error it finds. You can correct the error in the data file and reupload it to the pop-up. 
    3. When the system confirms the data file is in the correct format, you can click the Import data button to proceed.
  4. The system will begin importing data in the background. You can navigate away from the data import page while the system processes the file. The system will send a notification to the data import's "Error Notification Recipients" when the import is complete. In the "Data Import Completed" notification, you can click the View integration log button to see the process logs and check for any errors.
Delete

User Profiles Will Be Inactive After Import

User profiles will be imported as "inactive", meaning the individual cannot use their account to sign in to your Case IQ application yet. See details on activating user profiles in Edit, Activate, Deactivate, or View Activity of a User Account.

Schedule a Data Import

After configuring your data import, you can schedule when the system will run it by adding a "rule". In Case IQ, rules are configured by system administrators to automatically execute tasks. You can add a "Schedule Process" rule to set when the system will run your data import. Use the following steps to add a "Schedule Process" rule and "SFTP Data Import" action. You can see details on other types of rules in Add a Rule.

  1. In Settings, navigate to the Rules page under the Workflow tab. 
  2. Click the Add Rule button.
  3. The New Rule form will be loaded. In the "Name" field, enter a descriptive name for your rule.
  4. In the "Rule Type" field, select "Schedule Process".
  5. The "Frequency" and "Trigger Time" fields will now be displayed for you to fill in. 
    1. Frequency: choose the frequency with which the system should run the data import. You can choose from the following options: calendar quarterly, daily, daily (work week), fiscal quarterly, weekly, and monthly. 
      1. If you select "Daily (work week)", the process will be triggered every Monday, Tuesday, Wednesday, Thursday, and Friday.
      2. If you choose "Weekly" or "Monthly", a "Days of the Week" or "Days of the Month" field will be displayed so you can choose the days within the week or month to run the process. The "Days of the Month" field has a "Last calendar day of the month" option at the end of the list. You can select multiple days.
      3. "Calendar quarterly" and "Fiscal quarterly" will schedule the process for 00:00:00:000 on the first day of the next quarter. Fiscal quarters are based on the "fiscalYearStartMonth" setting on the Options page, see Update Application Options.
    2. Trigger Time: set the time that the system should run the process. The system may not use the same timezone you do. Hover your cursor over the question mark icon beside the field to see the system's timezone.
  6. Click the "+" button in the Actions section and select "Add SFTP Data Import".
  7. The following fields will be displayed in the New SFTP Data Import form for you to fill in: 
    1. Data Import: select the data import you want to schedule. You can only select data imports that have already been added into your application.
    2. Keep Source Files: choose if the system should retain the data files you upload to Case IQ's SFTP server. Your data files may be processed again if they remain. 
    3. Validate Only: choose if the system should only validate the data file. If you check this option, the system will run validation on the data file you upload to Case IQ's SFTP when the rule is triggered, but it will not add or update records in Case IQ.
    4. Unique Filename Prefix: enter a unique set of lowercase characters you will add to the beginning of data file names for this import. Case IQ uses this unique file name prefix to identify the files to import into your application when this rule is triggered. 
      1. For example, if you are importing information on your organization's personnel into Case IQ profiles, you could use "staff", "employees", or "contractors" as the unique file name prefix. Do not include spaces or special characters and ensure all characters are in lowercase. 
  8. When you fill in the unique file name prefix field, Case IQ will populate what your data file names should look like following our file naming conventions in the "Data file name preview" field, i.e. "[unique file name prefix]_{arbitraryId}.csv". 
    1. Make sure the data files exported by your source system are named following this format. All data file names must begin with the unique file name prefix for the import followed by an underscore. For example, if the unique file name prefix is "employees", your data file names must begin with "employees_".
    2. Any text following "[unique file name prefix]_" in a data file name is called an "arbitrary ID". Case IQ will not use the arbitrary ID to identify data files, so you can include any text as the arbitrary ID.
    3. Each data file for the data import can have a different arbitrary ID. Arbitrary IDs can be helpful for users to identify different data files in a recurring data import. For example, the arbitrary ID can be a timestamp when the data file was exported by your source system. 
  9. After noting the data file name preview, click the Save button in the New SFTP Data Import form. 
  10. Click the Save button for the New Rule form. You have now scheduled your data import.

Delete a Data Import

If you need to permanently remove a data import, you can delete it by clicking the Delete button on the data import's page. If you added a "Schedule Process" rule and "SFTP Data Import" action for this data import, the system will not be able to run the data import when scheduled. You can edit the "Schedule Process" rule to change the data import. 

Field Type Data Formats

Each record field type can only accept certain kinds of data. For example, textboxes, text areas, and picklists can contain text strings, like names and descriptions. If you imported text to an integer, number, or money field, the text could not be displayed properly in Case IQ. Likewise, if you imported 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.

We recommend considering how your organization intends to use the data you want to import. A field's type will indicate the kind of data that the field can accept. You can check a field's type in the data dictionary, under the System tab. The video clip below shows how to generate a data dictionary file for the profile form, and you can 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 next section, Static Profile Field Types.

When you find out each field's type, you can reference the following chart to check the data each field type can accept as well as other requirements. 

Field Type Data Format and Requirements Example
Checkbox Boolean.

True

False
Date YYYY-MM-DD format. Must comply with ISO8601 standards (see ISO 8601 Date and time format). 2023-12-31
Email Use the following format: local-part@domain. Must comply with RFC 6854 standards (see the Update to Internet Message Format to Allow Group Syntax in the "From:" and "Sender:" Header Fields memo). All characters will be converted to lowercase when imported. jane.doe@example.com
Integer 15 digits maximum. 123456789123456
Money Maximum of 9 leading digits and 2 decimal digits. 123456789.12
Number Maximum of 9 leading digits and 6 decimal digits. 123456789.123456
Picklist Each option can be 255 UTF-8 characters maximum. Discrimination (age, disability, gender, marital status, military status, national origin, pregnancy, race, religion, sexual orientation), harassment, drug/alcohol abuse, hostile work environment, environmental hazards, or unsafe working condition/process
Postal Code Either US Zip Code or Canadian Postal Code format, depending on your application's configuration.

US: 12345

Canada: A1A 1A1
Text Area 10,000 UTF-8 characters maximum.
Text Editor 10,000 UTF-8 characters maximum, supporting HTML5 formatting. HTML excluded from character limit. <ol><li>Review the relevant details of the case.</li><li>Confirm the role of the witness and their significance to the case.</li><li>Obtain any available background information on the witness.</li><li>Review prior statements or testimonies they may have given.</li><li>Draft a list of open-ended questions to elicit detailed responses.</li><li>Include questions that clarify any inconsistencies or unclear points.</li><li>Anticipate the witness’s perspective and potential biases.</li><li>Prepare questions that may uncover any personal or situational biases.</li><li>...</li></ol>
Textbox 255 UTF-8 characters maximum. See picklist example.
Time 24-hour time. Must comply with ISO8601 standards. 23:59:59
Toggle Boolean.

True

False
URL 255 UTF-8 characters maximum. https://caseiq.app/note/d3ea373c-1968-40f4-…a369-517b48d5cbba/edit
Phone Number 15 digits maximum in E.164 format. 

+447911123456, where:

  • Country Code: +44
  • National Destination Code: 7911
  • Subscriber Number: 123456

Static Profile Field Types

See the data contained in each static profile field in the chart below.

Delete

Static data form fields?

Data forms only have one static field: Date Submitted. All other data form fields are dynamic, so you can check their data type by opening the data form in the Form Builder.

Field Field Type
First Name Textbox
Last Name Textbox
Middle Initial Textbox
Date of Birth Date
Address Textbox
City Textbox
State / Province Picklist
Country Picklist
Zip Code /Postal Code Textbox
Home Phone # Phone Number
Work Phone # Phone Number
Email Address Email
Date Submitted Date
sftp profile import sftp profile sftp credentials sftp password data file format schedule data import schedule sftp schedule profile import peoplesoft

Related Articles