You are not logged in. Click here to log in.

codeBeamer ALM

Search In Project

Search inClear

Tags:  not added yet

Excel Round-trip

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 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 can not 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 format 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 TestCases's are exported together with their TestSteps, 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 round-trip and especially for TestCases' Steps...

User/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're 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).
  • 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.

Wiki text fields
Wiki fields are exported using their wiki source, as Excel can not represent them otherwise for example as rich text in cells.
Field length limitation

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

  • The long text is trunacated 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 can not 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 mappins" 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 can not 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 shows you the data found in the Excel sheet after the mapping and conversions. That is:

  • It shows only the changed and new data rows from the Excel, the rows which contain no change are removed.
  • You can select which changes are applied by ticking the checkbox appearing at the begining of each row
  • The new data is highlighted with the [NEW] blue badge
  • The changed data - which will update an existing item- is shown as two rows:
    • The first row shows the new value coming from the Excel sheet
    • The second row shows the current value as it is now in the target item in codeBeamer. Only that information is displayed where change is detected to avoid duplicated information unecessarily...
  • If there is any mapping or conversion problems that should be highlighted in red with some explanations

If something goes wrong: Error handling
Even the carefully configured mapping may fail when trying to save the data into the desired Tracker, so save may throw an exception. When such exception happens then:

  • Nothing is saved (the transaction is rolled back)
  • The import wizard remains open on the last page, and will show the error messages above each import row describing why the import has failed for that row.

This screenshot shows an example of such situation:

To resolve such situation you can click on the "uncheck all rows..." message in the topmost error (marked with #1), and that will remove the selection for all rows where an error happened during save. After that the remaining rows should be nicely saved by pressing the "Finish" button again.

An alternative of resolving this situation is that you 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 roundtrip export and Excel import supports exporting and importing work-items which are parents or children of each other. When exporting such items the Excel roundtrip export will automatically add the an indentation to the parent/child items' "Summary" (Name) column.

This screehshot shows how such a hiearchy 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 the 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 recognise 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 roundtrip Excel export to copy items to a different Tracker then the images will be missing after importing the Excel

FAQ

  1. Can I delete specific columns in the roundtrip 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 roundtrip excel file?
    Yes, you can add additional fields in the roundtrip 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 roundtrip 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 roundtrip 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 behaviour 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 roundtrip 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 behaviour 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 indivitually during the exports:

Round-trip editing TestCases in Excel, and importing/exporting TestCases from Excel or Word

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