Import from Excel

This topic describes how to import data from a Microsoft Excel file.

Note: The import function is not intended for migrating other systems into OpenText Core Software Delivery Platform. OpenText offers a variety of migration tools. For details, contact support.

Overview

The import function is used for migrating information data from one instance to another.

The following is a common flow for importing entities from an Excel file:

Prepare an Excel file that complies with the required format. For details, see Prepare the import file.
Add values for new entities or for existing entities that you want to update. For details, see Field values.
Begin the import. During the import several validations are performed. For details, see Import the file.
Fix any errors. Failed validation steps do not cancel the import. All valid records are imported, while problematic records are added to a new Excel file that you can re-import after you fix the issues. For details, see Duplicate items.
Re-import the new Excel file after the issues are fixed.

Tip: You can import tasks to a particular backlog item directly from the item's Tasks tab. For details, see Break items into tasks.

Prepare the import file

To import entities, you prepare an Excel (.xls or .xlsx) file with a list of the items to import, in a specific format.

You can import the following entities: Requirements, Backlog items, Defects, Tasks, Tests, and Test suites. Calculated fields and certain system fields cannot be imported, such as Testing tool type, Last runs, Estimated hours, and Code coverage.

You can export items and re-import them to the same workspace with modifications, or import them to another workspace. For details, see Export data.

Tip: To help you prepare an Excel file with the formatting and fields required for import, download a template file. Open the Settings menu and select the appropriate import action. In the Import dialog box, click View import file example.

When updating an existing item, add the item's ID number in the unique_id column, prefixed by '@. For example: '@9100. Only enter values for the fields that you want to change. All other fields will remain unchanged.

If you exported items to the file from another tool, you must update the Excel file according to the specifications described below.

To prepare a new import file:

In an Excel file, create a separate sheet for each item type you want to import. Name the sheets with one of the following names: User stories, Quality stories, Defects, Manual tests, or Test suites. Alternatively, you can export items of the same item type that you want to import, edit, and re-import the file. For details, see Export data.
In Row 1 of the sheet, type the names of the fields for which you want to enter values. You must use the fields' logical names, not the field labels. You can include both system and user-defined fields. For lists of supported and required fields for each item type, see Fields for import files.

You can also include any REST API-editable field. For details, ask your system admin.

For entity-specific guidelines, see Specific item type guidelines.

Field values

On separate rows in the spreadsheet, enter field values for each item you want to import. For possible field values, see Fields for import files.

When entering item values, consider the following:

Area	Details
Required fields	Each entity has required fields, as determined in the workspace configuration. If you do not enter values for all the required fields of an item, the items are imported in draft status.
Memo fields	Memo fields do not preserve their source formatting when they are imported.
Multiple values	In fields that support multiple values, separate the values with a comma or any other character defined by your admin. Note: Before entering multiple values, check the value delimiter set by the EXPORT_IMPORT_MULTIVALUE_DELIMITER parameter. The admin can change the value delimiter by editing the parameter. Changing the delimiter affects all files that are imported or exported for the purpose of import. For details, see Configuration parameters. Importing field values that contain commas or any other characters defined as a value delimiter is not supported.
Localization	In fields that use system lists, use the localized list values according to your client's language.
Date fields	The following date syntaxes are supported: Time with offset: "2015-05-12T10:15:30+01:00" Time with zone ID: "2015-05-12T10:15:30Z" Time with offset and zone ID: "2015-05-12T10:15:30+01:00[Europe/Paris]"; "2015-05-12T10:15:30+00:00[Z]"

Tip: Using the user_tags field, add a tag to each imported item that will allow you to identify and filter imported items after they have been imported.

Specific item type guidelines

Note the following guidelines pertaining to the different item types.

Item type	Guideline
Backlog items	When importing backlog items, both the Epic and Feature fields are used to determine where the backlog item is created. For example, if you want to place a user story under a feature which is under an epic but you do not specify the epic, a new feature is created under the Backlog root. By default, you can import up to 1000 backlog items from a single file. If you have more than 1000 items, use separate files. Site admins can change this limit by modifying the IMPORT_WORK_ITEMS_FUSE parameter. For details, see Configuration parameters.
Tasks	You can import tasks both from the general Settings menu, and from the Tasks grid of a backlog item. When importing from the Settings menu, tasks are imported to the backlog items that you specify in the spreadsheet. When importing from the Tasks grid, all the tasks in the spreadsheet are imported to the current story or defect.
Manual tests	In the rows following a manual test, define steps for the test. For details, see Import from Excel. A manual test row should only contain values in the manual test fields. The test step rows should contain values only in the test step fields. Application modules: If an application module's name includes a forward slash or backslash, in the import file, double the slash. For example, if the application module's name is `app/1`, specify it in the file as `app//1`. Note: In the import file you cannot specify application modules beginning or ending with a slash.
Test suites	In the rows following a test suite, list the tests that are part of the suite. See examples in the template file. Refer to the tests using their unique_id in the manual tests sheet, or their ID in OpenText Core SDP. For details, see Fields for import files. The test suite row should contain values only in the test suite fields. The test rows should contain values only in the test fields.
Requirements	There are several guidelines that are specific to importing Requirement entities. For details, see Requirements.

Custom lookup lists

In fields that use custom lookup lists, in each of the field's values you need to specify the lookup list that the field uses. For example, the Test Type field in your workspace may be configured to use a custom list with different values than the default list.

In each cell that uses a custom lookup list, use the following syntax: listName/listValue

listName: The name of the custom lookup list.
listValue: A valid value in the custom lookup list.

Note:

You can use either a forward slash or backslash in the syntax.
If the list name or list value themselves contain a forward slash or backslash, double the slash: // or \\. This causes the character to be imported literally. Example: list//Name/listValue
When specifying an alternative system list, listName must be the English-language name of the list, even if your space is set to another language. listValue should contain a value in your space's language.

Import the file

After you have prepared the Excel file, you can import the file.

To import a file:

Open the Settings menu. Under the Import section, select a type, such as Backlog Items, Tests, or Tasks.
Click Browse.
Select the Excel file you want to import and click Open.
Click Import.
Two stages of validations are performed before items are imported:
1. Stage 1 validations. The following is checked: All required fields are listed, and all field values are valid.
  
  If errors are detected, the import fails. Fix the errors based on the report and try importing again.
  
  Warnings can also be issued, such as redundant fields in the import file or duplicate items. The warnings do not cause the import to fail. For details on handling duplications, see Duplicate items.
  
  After passing the stage 1 validations, stage 2 validations are performed.
2. Stage 2 validations. The following is checked: Field values do not break any business rules.
  - Items that pass the stage 2 validations are created in the system.
  - Items with issues are added to a separate file where you can fix the issues and attempt to import again. Click the Download link in the import report to download a file with the failed items.

Note: Items that you imported without values in required fields are created as drafts. After the import, filter the relevant grid based on the Is Draft field and fill in values for all the required fields. To prevent the creation of draft entities, instruct your admin to turn off the Allow draft entity creation permission in the General System Actions section. For details, see Permission categories and types.

Duplicate items

Another type of validation is a check for duplications in Backlog and Test items. If you already have an item with the same name and in the same tree location as an item you are importing, a warning is issued about the duplication. A duplication warning may also be issued if there was a network outage during the import process.

You can either cancel the import and rename the duplicate items in Excel before importing, or continue with the import, creating two items with the same name.