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).
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
Spreadsheets used to import data must adhere to the following
- Only files of type .xls can be imported. If you have
an .xlsx file or any other type of spreadsheet file, you must convert it to
- Only data from the first worksheet in a file is
- You can only import one spreadsheet at a time.
- You can only import into a single project or auxiliary table at a
- Spreadsheet columns represent
fields and all spreadsheet data is treated as text. Spreadsheet columns must be
formatted as Text types.
- Each row represents an item that will be imported, except for the
first row, which is used to define columns available for mapping. Data from
this row is never imported.
- To import primary items, the spreadsheet must include columns for
- To import auxiliary items, the spreadsheet must include a column
for the system
- If you are using the import to update items, include a column that
can be used to determine uniqueness. For details, refer to
- For best results, limit the number of rows to 10,000. To import
more than 10,000 items, create multiple spreadsheets and import them
- To ease data mapping, label spreadsheet columns with the same name
fields. This enables you to use auto mapping.
- Ensure that all spreadsheet fields are in the "Print Area". If they
are not in the print area, they will not be available for data mapping.
- Also, ensure that none of the rows or columns are selected under
Here is an example of a simple spreadsheet used to import users:
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
The following privileges are required before administrators can import
new items or update existing items.
||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.
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
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.
If the values in either
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
Field Mapping Considerations
You can map to most
fields; exceptions are the
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.
- 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
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.
will match spreadsheet values to
Item Type prefixes and values specified in
States and Ownership
- When you import primary items, you must map a spreadsheet column to
- 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.
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
- After importing data, review imported items to ensure they are
owned by the correct users.
Selection values are unique to each of these field types:
- Single Selection
- Single Relational
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
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
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.
- Spreadsheet columns must be formatted as Text types, including
Date/Time fields. For best results, row data for
Date/Time fields should follow these guidelines:
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
- In addition, ensure that the format of the values in the
spreadsheet matches the Date Format that is selected in your 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
- 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
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
Copyright © 2007–2016 Serena Software, Inc. All rights reserved.