About Data Import

Use the Import Data feature to add new items or update existing items from a spreadsheet. You can import items into an auxiliary table or a specific project in a primary table (application table).

For details, refer to:
Note: SBM provides several mechanisms for importing data. For details on which import feature best meets your needs, refer to Other Options for Importing Data.

Preparing a Spreadsheet for Importing Data

The key to achieving a successful import lies in taking the time to prepare source data for importing. This requires an intimate knowledge of the field types and existing data in your system, and of the data you are importing.

Spreadsheets used to import data must adhere to the following requirements:

Here is an example of a simple spreadsheet used to import users:

image

In this example, row 1 defines the columns to be mapped. This information is not imported. Row 2 is used as sample data, but data is imported.

Import Privileges

The following privileges are required before administrators can import new items or update existing items.

Privilege Privilege Location Notes
Remote Administration User - System page Grant to enable administrators to log into SBM Application Administrator.
Submit New Items User - Item page (primary items)

User - Table page (auxiliary items)

Grant to enable administrators to import new items to a specific project or auxiliary table.
Update All Items User - Item page (primary items)

User - Table page (auxiliary items)

Grant to enable administrators to use the Data Import feature to update existing items.

Record Matching

To use the Import Data feature to update existing primary and auxiliary items in your system, you need a field in your application that contains a unique value for each primary and auxiliary item and a column in the spreadsheet that contains unique values for each row. You must then map the unique SBM field to the spreadsheet column that contains unique values.

During the import process, the value of each row is matched against existing values in the system. Matching is case insensitive.

If a match is found, the existing item is updated with data from the spreadsheet if the Replace mapped attribute option is selected. If no match is found, a new item is created.
CAUTION:
If the values in either SBM or the spreadsheet are not unique, you may have unexpected results. For example, you may update an existing primary or auxiliary item from multiple rows or data may be updated in the wrong SBM item.

Field Mapping Considerations

You can map to most SBM fields; exceptions are the Project field, Folder fields, Sub-Relational fields, the system-provided Last Incident field, and deleted fields.

Note: The import process ignores the read-only setting for fields. If you map to a read-only field, data in the spreadsheet is added or updated, based on import settings.

The following information provides guidance on mapping to specific fields or field types.

Item IDs

  • To automatically generate Item IDs for newly imported items, do not map a spreadsheet column to the Item ID field. When IDs are generated, numbering properties specified for the project apply to the imported items.
  • To retain Item IDs from data in the spreadsheet, map the ID column to the Item ID field, but be aware that doing so overrides the system's automatic ID generation and may result in duplicate Item IDs.
  • To retain historical data while automatically generating Item IDs, consider creating a custom field to store imported IDs and mapping to this field when you import data. For example, in SBM Composer, create a Text field named Old ID Numbers. After deployment, you can map the ID column in the spreadsheet to the Old ID Numbers field. This enables users to search for the old ID numbers, but ensures unique, system-generated IDs in imported items.
  • If you use Item Type prefixes, such as ENH for enhancements, include a column in the spreadsheet for item types (Requests, for example) and map this column to the system Item Type field. Each spreadsheet row can contain either the Item Type value, such as Request, or a prefix, such as REQ. SBM will match spreadsheet values to Item Type prefixes and values specified in SBM Composer.
States and Ownership
  • When you import primary items, you must map a spreadsheet column to the State field.
  • Each row in a column mapped to the State field must have valid data. Rows that do not have a State field value are skipped. If a row contains a value that does not match an existing state, the row is skipped.
  • SBM states use the Active/Inactive field to automatically determine the status of items in each state. You can map to the Active/Inactive field, but it is more beneficial to use the automatic setting of the Active/Inactive field.
  • After importing data, review imported items to ensure they are owned by the correct users.

Selection Values

Selection values are unique to each of these field types:
  • Single Selection
  • Multi-Selection
  • User
  • Multi-User
  • Single Relational
  • Multi-Relational
  • Multi-Group

Data in spreadsheet rows for columns mapped to selection fields must match existing data in the system. If not, the row is skipped. Matching is based on case-insensitive searches of the selections available for a field. For User fields, matching is based on the login ID or name of users in the system.

Users can be specified by their names or login IDs. You can use the Parse multiple values in a cell option to specify a separator, and then import several values within one spreadsheet cell.

For fields that allow you to select users and groups, you do not need to specify which name is in a cell (either the user or the group)β€”the import attempts to determine this automatically. The import process attempts to find a user with the specified name or login ID. If it is not found, then it attempts to find a group with that name. This means that if you have a user and a group with the same name and you specify this name in the spreadsheet, the user name is set as the field value.

Tip: Check for spelling consistency in field selections before importing data. For example, "Not Applicable" and "NotApplicable" are treated as separate selections.

Empty rows are imported as "none" values unless you select the Set default values for empty cells check box and the mapped field has a default value.

Date/Time Values
  • Spreadsheet columns must be formatted as Text types, including those for Date/Time fields. For best results, row data for Date/Time fields should follow these guidelines:
    • For SBM fields that are set as Date Only or Date and Time, spreadsheet data should match the display format, such as mm/dd/yyyy or mm/dd/yyyy hh:mm:ss. After the import process, values will appear to users in the format specified in their user profile.
    • Date/Time keywords, such as startof_lastweek or endof_thisyear, can be used with Date/Time fields set as Date Only or Date and Time.
    • For Time Only or Elapsed Time fields, specify spreadsheet values in this format: hh:mm, hh:mm:ss, or d hh:mm:ss (Elapsed Time fields only).
  • If you do not map to system Date/Time fields, such as Submit Date and Close Date, values may be established as the date items were imported.

Text Field Values

Spreadsheet data should comply with Text field settings. For example, if you map to a Text field set to have a fixed length of 80 characters and a spreadsheet row has 100 characters, the last 20 characters are truncated in the imported item.