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) to populate profiles or data form entries in Case IQ. This article covers the steps to activate an SFTP integration by adding a data import. 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 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.
  • You can import a maximum of 25,000 records per import.
• Each record's data must be in one row in the data file, meaning that there is one profile or data form entry per row. Likewise, one profile or data form field per column.
• Data files must be in .csv 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.
• Add double quotation marks (") around text values with commas (,), e.g. "John, a witness, spoke to the subject about the incident".
  • No other special characters require double quotes, such as apostrophes (' and "), ampersands (&), dashes (-), number signs (#) and slashes (/ and \).
• 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).
• The maximum file size is 15MB.

Add a Data Import

To add a data import, click the Add Data Import button on the Data Import page. The New Data Import form includes multiple sections to fill in. First, complete the following fields in the Data Import Parameters section:

• Name: enter a name to refer to the data import.
• Import Entity: choose the Case IQ entity in which you want to import the data. Choose "Profile" if you want to import data into Case IQ profiles or select "Data forms" to import data into data form entries.
• Update Method: choose how the system will import the data into your application. The system will use the unique identifier that you set in the Field Mapping section, such as an employee ID or email address, to match rows in the data file with records in Case IQ. A data file row and Case IQ profile or data form 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 profiles or data form entries currently in the application, then add all records in the data file into Case IQ.
  • 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.
  • 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 profiles or data forms in Case IQ.
• Error Notification Recipients: enter an email to receive error notifications if the system cannot process an import.

Next, you can tell the system what data to populate in profile or data form fields by filling in the Field Mapping section.

1. Click the "+ Field Mapping" button. 
2. Search for and select a profile or data form field in the "Target field" dropdown, depending on the entity into which you want to import data. You can select any dynamic or static Case IQ profile or data form field as a target field. You can only map one data file column to a field.
3. When you select a target field, the “Data file column header” field will be automatically populated with the target field’s “Caption” as a placeholder. You can edit the “Data file column header” so it matches the column header in your data files. For example, if the column header is "Investigation's Outcome", ensure the "Data file column header" field in the mapping section is also "Investigation's Outcome".
   1. Make sure that the imported data matches the format and requirements for the profile or data form field, see the Field Type Data Formats section of this article for details.
4. 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.
5. Click the Options () button for the row. You can set one row as the unique identifier by selecting the "Set as unique identifier" option.
   1. Every profile or data form must have a different value for their unique identifier, such as an email address or employee ID.
   2. You can only select one unique identifier for a data import.
   3. The unique identifier acts as each record's ID. It tells Case IQ how to identify a single profile or data form record. During an import, the system will look for data file rows and Case IQ records with the same unique identifier. Rows and profiles or data forms with the same unique identifier are considered the same profile or data form.
6. Continue to map more fields in your data file by clicking the "+ Field Mapping" button. You can map up to 40 fields per data import.
7. When you are done mapping fields, click the Save button to save your data import. 

After filling in the Data Import Parameters and Field Mapping sections, click the Save button.

Edit a Data Import

You can update a data import at any time 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 parameters 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 “Data file column header” fields outlined in the data import's field mapping section. 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 .csv validation, following RFC 4180 standards, which you can see in the Common Format and MIME Type for Comma-Separated Values (CSV) Files memo. After passing the initial tests, the system will run row-level validation on the the data. To run validation on a data file:

1. Click the Options () button on a data import's page.
2. Select "Validate a data file" option. 
3. Upload your data file to the "Validate a data file" pop-up in the "Upload file" box.
4. The system will run high-level validation to confirm the data file contains column headers and a consistent number of delimiters for each row.
   1. If there are problems with the data file format, the system will list the first error it finds. You can correct the error and reupload the data file to the pop-up. 
5. The system will confirm when the data file is in the correct format. In the "Validate rows" pop-up, you can proceed to make sure each row has a unique identifier and the correct data type for each column. Click Validate rows and the system will validate the data in the background. 
   1. 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. 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. To import 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 and reupload the data file 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 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. 

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 choose 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 the system.
   2. 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. 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 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 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. 

Field Type Data Formats

Each profile and data form 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 Generate a Data Dictionary File. 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. 

Static Profile Field Types

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