Excel Round-Trip and Excel Import

Overview

With Excel Round-trip editing of Requirements (or other kinds of issues like Tasks / Bugs etc.) in Codebeamer users can:

• Export several issues to an Excel document and view, read or print it in Excel
• Edit the exported items' fields, descriptions or summary in Excel
• Add new items by simply adding new rows to the Excel table
• Finally easily import back all these changes made in the Excel document to the original issues or Requirements
• During import users have the option to review all changes and selectively import only the desired ones

Round-Trip Workflow

Exporting Work Items

Exporting issues from Codebeamer to Round-trip Excel documents starts with navigating to the tracker which contains the issues to be exported. Within the tracker choose the "Export to Office" action in the "more" menu.

You can:

• Export all data displayed in the current view
• Or export the selected items only. Use the checkboxes or control-clicking in the tree in the Document-view mode to pick the items which will be exported. Also the selected items' children are optionally exported.

When the Excel export is selected Codebeamer will build an Excel document which contains the issues displayed by the current view and the download of that Excel document will start in few seconds automatically. The generated file name will contain the current project's and tracker's name, so it is easier to identify later where that export file is originated from.

Content of the Export

When exporting to Round-trip only those issues/requirements will be exported which match the active view in that tracker.

The active view determines two important things:

• Which item will be exported the Excel (i.e. the data rows in the Excel sheet)
• Which fields of the items are appearing in the Excel: i.e. the data columns are appearing.

So you can easily control which issues are exported by defining a new Tracker view or choosing an existing view that matches your needs. You can also create custom views to control the rows and columns being exported, see here: Tracker Views

The ID Column

Regardless of what the view mandates the round-trip Excel export will always contain an ID column as 1st column in the Excel export. This contains the numeric unique id of the item being exported, which is then used to identify the target items when importing the data back. This ID column also contains a link pointing back to Codebeamer, and by clicking on that link will open the relate item in a web browser. This makes very easy to find the exported items from Excel later.

Exporting the Description of the Items

The Description of the issues/requirements is not included by default in an Excel export, because this field usually contains a long description of the problem, requirement which hardly fits to an Excel cell (Excel has serious limitations what a cell can contain, like it cannot display a real rich content in a cell, and a cell can contain only up to 32000 characters).

However because usually the Description of an item contains the most information about the subject you have the option to export this to the Excel field, by turning on the "Export description" checkbox on the Excel export dialog.

For limitations of exporting Description see:

Field Length limitation section below

Editing Data in the Round-Trip Excel

You can edit the data in the Excel sheet, and import back your changes later, which will update the original items in Codebeamer. This is the so called round-trip workflow.

During the export the data is formatted in the Excel sheet so that editing should be easy. However due to some limitations and differences between Excel and Codebeamer's data format you will need to understand how the data looks like and can be edited in Excel. The following section describes this.

Editing Various Field Types

The export formats for various field types are:

Text Fields

Simplest text field is exported as-is to Excel

Date Fields

Dates exported as native excel dates in cell, and also imported from native date cells. The importer can also import a date from a "string" formatted cell too, the date-format is configurable on the importer GUI.

Number Fields

The number and integer fields are exported as number/integer cells to Excel. Also a cell constraint forces that only the appropriate double/integer numbers can be entered in cells.

Boolean Fields

The exporter exports Boolean fields as a drop-down with "true" and "false" as possible values.

Choice

The exporter will export the current value as the choice's text, and accepts only the possible values in the Excel document by using a drop-drown control.

Multi-Value Choice Fields and Other Multi-Value Fields

Multi-value choice fields and can be imported using various import data formats. See this detailed guide

Table Fields

Table fields are exported to Excel tables in a way that every table row becomes a new row in the Excel sheet. The importer can also nicely import back such structured Excel files.

For example Test Cases are exported together with their Test Steps, and that can be edited and imported back to Codebeamer. See the Importing and round-trip editing of TestCases with TestSteps in Excel which shows you examples and explanation how the Tables are handled in roundtrip and especially for Test Cases' Steps.

User or Member Fields

The exporter exports the users and roles as simple list like "bond,[Developer]". The users are exported as their account names, the roles are exported as "[<role-name>]" text, multi-values are separated by "," character.

When importing this field back the importer will recognize the same format, the roles are looked up in the same project where the target tracker is.

Reference Fields

Reference fields are exported like this:

• "[ITEM:8334] - v 7.4", where the "[ITEM:8334]" is the interwiki reference of the referenced item (it contains the type and the id number) and followed by the name or short description of the referenced item.
• If multiple references are exported they are separated using a "," character, for example: "[ITEM:8334] - v 7.4,[ITEM:8335] - v 7.5"

Modifying references in Excel is possible:

• to remove a reference simply delete its interwiki reference text, i.e. remove the "[ITEM:8334]" from the cell
• to add a reference insert its interwiki reference, like add "[WIKIPAGE:43]" to the cell anywhere (the surrounding text is ignored).
• Since CB 20.11 (Carmen) you can also use the [TESTCASE-1234] format for tracker-item references as this will look up the referenced item in the format of [${tracker-key}-${id}].
• to add a new reference you can also add just the name as simple text of the referenced Work Item. Such values are looked-up by "name": if the import value contains a simple text that is looked up within the possible references and the first matching reference item will be automatically used. So for example if the cell contains text "Banana" in a reference field, then the importer will try to look up the Work Item named "Banana" and the reference field will point to that.
• The Excel import process does not consider the filter set for Status or Resolution value in a Work/Config items reference field. For more information about this behavior, refer to Limitations for the Status or Resolution Filter in Work/Config items.

Wiki Text Fields

Wiki fields are exported using their wiki source, as Excel cannot represent them otherwise for example as rich text in cells.

See: Why my wiki is exported as wiki source

Field Length Limitation

Because an Excel cell cannot contain more than 32000 characters the Excel round trip exports long data specially, that is:

• The long text is truncated and "..." is added to the end
• This truncated text appears as orange and strike through
• It is in a read-only cell which shows an error message if user tries to edit it.

An example:

When trying to import such Excel data the importer will automatically recognize truncated cells, and will ignore it, so truncated data will not overwrite the full data in any case.

The following screenshot shows how this appears on the import UI:

Read-Only Fields

Some fields are not editable in Codebeamer, for example editing the "Submitted At" field is pointless because this field always contains the date when the issue is created. Therefore when exporting a "Submitted At" field using round trip the Excel cell will be read-only.

Adding New Items Using Excel

You can easily add new items in Excel by simply adding a new row. The ID field/column of the new data must be left empty, but all other fields can get values. During the import such new rows will appear, and marked by a [NEW] badge. The following screenshot shows how this looks like in Excel and then during import:

Importing Round Trip Changes Back from Excel

Importing changed values from an Excel round trip document is simply initiated by the usual import functionality of the Trackers. The round trip data is recognized by the "ID" column of Excel, and the matching item/requirement/bug/etc. will be updated.

For more details about simple import to Excel see documentation: Importing Data from Excel to CodeBeamer

So as first find the "Import" menu from the more menu of Trackers:

Choose that and the import wizard starts. Upload the modified round trip Excel file, and choose Excel format if necessary. On the next page of the wizard you should see that the mapping of the Excel columns and the Codebeamer fields are automatically mapped for you.

Automatic Mappings of Fields

The automatic mapping should be fine in most cases, but sometimes you may want to adjust mappings, for example you can ignore a column by removing its mapping, or forward data to some other fields by changing its mappings.

The most important field in round-trip is the "ID" field, because without the proper mapping of the "ID" field the original item/issue cannot be found, and therefore the data will be (mis-)recognized as NEW and will create a new record. Tip: if you want to add all Excel data to a new tracker then just clear the mapping of the "ID" field/column and the data will be inserted as new

The result of automatic mapping can be reconfigured by clicking on the mapping selectors above each column. You can also map a column to multiple field - turn this on by clicking on the "Multiple field mappings" checkbox- this may be useful if you want to load the same data to multiple fields.

Warning when Automatic Mapping Maps a Field Twice

Sometimes automatic mapping cannot decide properly and maps a field twice. The importer will show a warning when this happens: the user can correct this situation manually:

Final Step: Review Changes and Differences

The last page of the import wizard displays the data found in the Excel sheet after the mapping and conversions:

• The first 50 rows are listed on the preview page.
• Only the changed and new data rows are listed from the Excel. The unchanged rows are not displayed.
• The new data is highlighted with the NEW blue badge.
• The changed data which will update an existing item is listed in two rows:
  • The first row contains the new value coming from the Excel sheet.
  • The second row is the current value in the target item. Only the modified information is displayed to avoid duplication.
• Whether there are any mapping or conversion problems that should be highlighted in red with some explanations.

Error Handling

Even a carefully configured mapping may fail when saving the data into the desired Tracker, and saving may throw an exception.

In such cases:

• The changes are not saved, the transaction is rolled back.
• The import wizard remains open on the last page and displays error messages above each import row, describing the reason of the failed import.

To resolve this issue, you can either go back to your Excel sheet and correct all data where the save have failed, or you can use a custom conversion script inside the import wizard.

A typical scenario for such errors happening is when you change the Status of many items in Excel, however your Tracker's workflow may forbid the transition from the original Status to the new Status. Such errors can be resolved by either removing the mapping of the Status field, or use a Status value that is possible.

Exporting and Importing Work Item Parent-Child Hierarchies

The Excel round-trip export and Excel import supports exporting and importing work-items which are parents or children of each other. When exporting such items the Excel round-trip export will automatically add the indentation to the parent/child items' "Summary" (Name) column.

This screenshot shows how such a hierarchy appears in the Excel export file:

As can be seen the "Summary" column will contain indentations which visually represent the parent-child hierarchy of the exported Work Items. When importing back the same structure from Excel then the importer will rebuild the same parent-child hierarchy. This is especially useful if you want to import the data as new Work Items to a different tracker.

See also: How the Excel import handles hierarchies, and How to export Work Item hierarchies using custom Excel templates

Multi-Language Support in Excel Round Trip

When exporting issues you may notice that the Priority, Status and other similar choice fields and also roles of user fields are exported in German language. This also affects the possible values of choice fields, the selection contains your local language (if translations are available).

This screenshot shows an example:

Also the round trip importer will take care of this difference, for example it will recognize the German Priority values in the Excel sheet and import them correctly. However the importer can only translate back the German text found in the Excel sheet if your language also is set to the same language as the export was done, i.e. the German/Deutsch in the browser !

In order to help to recognize such situation when the export was performed in different language than the import the Excel round trip has a built in mechanism to detect that. The importer will show this warning message in this situation, then the solution is to change the language of the import to the same as the export was performed:

Known Limitations and Issues

• Limitation: Excel has 32k limit on size of cells, that has a built in solution: You must login to see this link. Register now, if you have no user account yet.
• It would be nice that round trip Excel - and non-roundtrip- Excel export would support custom templates: You must login to see this link. Register now, if you have no user account yet.
• Images and other attachments are not exported to Excel. So if you want to use a round-trip Excel export to copy items to a different Tracker then the images will be missing after importing the Excel
• Only 1000 rows handled during Export Importing processes. But you can run the importing procedure several times.

Frequently Asked Questions: My Wiki Looks Corrupted when Exporting to Excel

If you have a wiki text in Codebeamer like the "Description" of the issues/tickets an export these to Excel then what you will see in Excel is the plain wiki text instead of nicely formatted rich text like bold/colors/fonts/images etc.

Why? This is simply because Excel has a very limited rich-text capabilities for formatting in Excel cells. It can do some formatting like boldness or colored text, but that is far from supporting complete capabilities of the HTML rendering that we use when we render the wiki to a web page.

Therefore it is technically impossible to properly export a wiki/HTML to Excel which looks nice and "same" to what you see inside Codebeamer.

So we just decide to export the wiki source to Excel: which can be nicely imported back from Excel in a round-trip scenario.

If we would try to "downgrade" the wiki/HTML to the capabilities of the Excel then this would result in corrupting the wiki/HTML data when you import this text back to Codebeamer.

That's why it is recommended to use Word for exporting/importing wiki/HTML which contains lots of rich text or heavy formatting instead of Excel. Word's internal markup capabilities are "close" to the capabilities of HTML, therefore, the round-trip works there.

Frequently Asked Questions

1. Can I delete specific columns in the round-trip excel file?
   Yes, you can delete any columns, but you have to keep the unique Codebeamer ID, name, description and any other configured mandatory fields from the tracker.
2. Can I add additional columns in the round-trip excel file?
   Yes, you can add additional fields in the round-trip file any time. Please note, that you will need to customize the tracker fields in order to be able to import the columns into the right field.
3. What can I change specifically in the excel round-trip file?
   You can change any fields except the unique Codebeamer ID. One more limitation: Excel doesn't support rich text with e.g. images. Images in the Excel description can be imported, but round-trip wouldn't work.
4. How do I import the Description field as plain-text or HTML?
   When importing new data then you can select the format for the Description filed on the import UI: choose the "Plain Text" if your data is NOT wiki but plain-text or HTML if your data is HTML. Note: this setting is overridden by if you map the Description Format field to an import column: then you can also import mixed content when some row is wiki some are plain text by putting the appropriate format to that column.
5. When exporting items there is a gap between each row, why?
   Probably what is happening is that you have a reference/choice/user field with multiple values and each such value is appearing and adding a new line in the export file. This is normal behavior and Excel import can nicely import these multi-row values back and creates multi-value for these fields.
   If you don't like the default settings you can configure that. Here is how the multi-value appears in the Excel export:
6. The ID field is not mapped automatically during import: why?
   Since CB 9.3 the ID field from the Excel import is not mapped automatically, but only if the Excel file is generated by Codebeamer (if it is a round-trip Excel). Otherwise the ID is not mapped, but user can map it if he wants.
7. I have some hidden columns in the Excel import, but that is not loaded, why?
   Since CB 9.3 the hidden columns are not import by default (it was imported before this release. However if the Excel sheet contains hidden columns then the UI shows a checkbox: and if the user checks that then the hidden column's data is appearing and loaded too.
   

Configuring Excel Export Default Settings

You can configure Excel export's default behavior on the Application Configuration page by adding this section:

"excelExport": {
	"exportMultiValuesToMultiRows": "true",
	"addDescriptionToExcelExport": "false",
	"exportDatesAsUserFormattedStrings": "false"
}

These settings control the default export options appearing on the UI: they can be altered individually during the exports:

Round Trip Editing Test Cases in Excel and Importing or Exporting Test Cases from Excel or Word

See this wiki page for details: Importing and round-trip editing of TestCases with TestSteps in Excel