Importing Data from Excel to codebeamer Importing Data from Excel to CodeBeamer

Tags: not added yet

Watch a video on how to import from Ms-Excel here.

For more information about Excel round-trip see documentation here:

Importing Data from Excel Files to CodeBeamer

CodeBeamer Excel import functionality allows you to import data from an Excel file. The major features of this import is:

You can import data to a Tracker and create new work items there
Excel 2003 & 2007 (*.xls, *.xlsx) formats are supported.
First Excel row is used as header, 2nd row and below is used as data (customizable)
Excel columns are mapped to issue fields, mapping is configurable
Importing issue hierarchies is possible using indented data
Date, number format is customizable, other detailed data conversion is possible

Gettings started: preparing your data

Before importing some Excel data-file it should conform to following rules:

The first Excel sheet can be imported, it should contain the data
The first row should contain the header information like field names. The headers can contain any descriptive text, but the best approach is to try to use those labels which match (or close) with the CodeBeamer tracker columns you want to load the data into. If your data is coming from CodeBeamer already then try to keep the header as-is, without any changes.

During the import the wizard will try to automatically pair the header-names in the Excel sheet to the closest matching field name in the target CodeBeamer Tracker. So if your names in the sheet match then the configuration will be much easier for you.

First steps: Importing to a Tracker

So to start importing to a Tracker go to the Tracker, and from the "more" menu choose Import...

The import wizard will start up, and you can walk through the wizard pages and configure the details in there.

Click on "Attach file" to choose and upload the Excel file. You can also drag-drop file from file-system here (some browsers does not support that however).

Click on next to start importing...

Mapping data to CodeBeamer fields

On the second page of the wizard you have the option to map the cell-data coming from the Excel sheet to the fields of the CodeBeamer issue/bug/requirement that will be created from that data.

The data will be handled as:

Each data-row of the Excel sheet will be imported into one issue/bug/requirement in the current tracker
You can set the row number where your data starts in the Excel sheet. That number specifies which is the first row that creates an issue. Typically the data starts at the 2nd row.

The bottom part of this page of the wizard also shows you the sample of raw data found in the Excel sheet. This allows you to check if the correct data is being imported, and also decide about which Tracker field should the data loaded into.

Above each sample-data column you will see the list of fields of the issues in the Tracker. These might be the standard fields provided by CodeBeamer or your customized fields in this Tracker.

From each field-list you can:

Select the first option: "--" if you don't want to map that column to any field
Select any field-name so the data will be loaded to that field
Select multiple fields too: it is possible to load one column of the Excel sheet to multiple fields. For example you can load the column which contains user's names to both the "submitted by" and "assigned to" fields of the created issues this way.

Note: that import wizard tries to automatically figure out the mapping for you, by using the header text that is "closest" matching to the fields' names. That is acceptable most times, but carefully review and adjust mapping if necessary.

Default date and number formats

As the date and number format of your Excel data might be variying (depending on your country's customs), you can set the default date and number formats on this second page too.

Importing hierarchies

Import can build a hierarchy (a tree) of items from the import data. This hierarchy is built from a column which contains indented data. The indentation levels will be used to determine the parent-child relationship of the items. Use Excel's indent button to add indentation in a cell or use tabs in CSV files for building hierarchies.

For example if the "name" column contains this data in this indentation:

APPLE
   BANANA
PEACH
   PLUM

Then the 4 created issue will be in the same parent-child hierarchy, i.e. the BANANA will be child of APPLE and PLUM will be child of PEACH Like this:

APPLE
   └── BANANA
PEACH
   └── PLUM

Special mapping: importing comments

There are two special fields also appear in the mappings selection, which are the "Comment" and "Comment Format" fields. See highlighted here:

If a data is mapped to the "Comment" field that will be added as a new comment to the issue being created from that row ! This is a very convenient way to add also comments to the issues.

The "Comment Format" field is specifying the format of the "Comment" being added. The possible values are "W" for "Wiki", "H" for HTML, and "P" for plain text. If the Comment Format is not specified the comment is added in plain text format.

Data conversions options

As you have mapped the incoming data to fields of the issues of the target Tracker, the importer will try to load that data into the field using some smart defaults. For example it tries many date format for date fields. Other example is that it looks up the users by their account name if the data is mapped to the "assigned to" field.

Yet sometimes the conversion may fail, but for such cases you can select some default values. You can:

Choose an user account to map all unknown "user"-typed fields used as default value in case the mapping fails.
Choose a certain "status" from the available statuses. This will be used if the "Status" field o the created issue would be otherwise empty.

There is lot more conversion options than this, see section Data conversions for more details...

Previewing convered import data and correcting problems

As the last step of the import wizard shows you the converted data - after all configured conversions are applied- and also tries to write that data to the mapped columns.

Important: that at this time no issues at all created, so if you would cancel the process there would be no changes whatsoever.

So the preview page shows the converted data, and if there are conversion or mapping errors then it higlights those cells where that happened. Also that should provide an information message which should contain enough detail to correct the problem. Mostly the problems are:

May happen that the mapping is incorrect, for example a wrong fields is mapped to a column.
The cell data is in a format that is not correct, so some custom converion might be required.

If problems are spotted you've the option to go backwards in the wizard: use the "back" button to go back and for example change the mapping or conversion.

Alternatively if the problems are rare or not sever then you may want to go forward. Then the importer will try to create the issues anyway, and for any failed data loading either result in that an issue will be missing or some of those fields will be empty.

Finally: creating issues and reviewing them

So after finishing the import the importer will print out the number of issues created, and also will print out links to those issues. That is advisable that you double check the created issue-data, and spot for possible conversion errors for completeness of your data.

For easier review you have the option to tag the imported issues during import. This option appears on the last page of the import wizard, and if the tag name is not empty then all issues will be tagged with that. For creating a private tag add a "#" prefix to the tag name.

An example:

Data conversions

Following section describes the data conversions in details

Data conversion smart defaults

When importing from Excel the importer tries to convert the data smartly, and it does try several smart ways to convert the incoming data. This conversion takes account the target field's type. Following table summarizes this:

Field type	Conversion attempts and data formats accepted
Integer or decimal	Tries the user specified format selected in the wizard UI or if that fails then tries the number format in the importer user's language (locale)
Date (for example "Start Date")	Tries the date format specified on the wizard UI or tries the same format with time "H:m:s" added (with hours and minutes) or tries with "yyyy-MM-dd HH:mm:ss" and "yyyy-MM-dd" formats, and also it ignores the "-" and "." differences
Choice fields (with simple options like Resolution field)	First tries to find the existing choice option value with the same name ignoring the case differences or tries to find the same name using the importer user's language. For example the "Sehr hoch" is converted to "Highest". This conversion is using the "tracker.choice.<name>.label" translation found in ApplicationResources_de.properties file. or tries to find the same name using the English language (locale) Finally If a choice value is not found then the converter: If a default value is set on the wizard UI the converter uses that If a default value is not set then a new choice value is automatically created in the target tracker, and that value is used
User fields (for example: Modified by)	The converter ignores and removes the leading and trailing " and ' characters ( "bond" is equivalent to bond ) Tries to find the user by looking up the same account name or tries to find the user using its "real"/full name, i.e. "firstName lastName" (ex. "James Bond")
Users or roles fields (for example: Assigned To)	Converter ignores leading and trailing " and ' characters The data can contain multiple items if these are separated by ; or , characters as separators Finally the users and roles are resolved using reference field resolving - see next section-, for example [USER:64] or [ROLE:33] can be used
Reference fields (Choice fields referencing other entities, for example Release)	Values are not matched by name Values are referenced by Interwiki links, for example [ISSUE:1234] or [ITEM:1234]. To make the imported spreadsheet more human readable additional text can be entered in the same cell, which will be omitted at import, e.g. for release 1.0 the content of the cell might be 1.0 [ITEM:1234] . Alternatively custom conversion script can be used if necessary (see below). Multiple references can be created if these are separated by ; or , characters Other reference formats accepted like "(type,id)" or "type-id" where the type is the type integer for the referenced entity type, the id is the integer id of the target entity

Other specialities:

The parent field: if this special field is mapped, that can contain an integer value, which is used to reference other issues in the imported set. This can be used to build parent child hierarchies between the imported elements too.

Advanced data conversions

In some cases the data needs some more advanced conversion, because it is not matching with the default conversion rules built into the importer.

In such cases you can add some small scripts to do additional conversion. For that click on the "Configure advanced conversions" text which opens the following view:

The rules are:

You can set one conversion for each mapped field
Typically you want to add a small script (Javascript) which converts the data from Excel so that it accomodates to a format which is understood by the importer
After this advanced conversion is executed the importer will try to use the smart default conversions which is described in the previous section.

Advanced JavaScript conversion

One most powerful option is to use a small JavaScript for data conversion. That is activated by selecting "ScriptConverter" from the select box in each mapped field. The previous screenshot shows an example.

For a detailed Javascript reference click here

Use a script to do the conversion. The script's return value will be used as result of the conversion. This script can use following variables:

input - The input data: for example cell content
applicationContext - Spring's ApplicationContext to look up and use Managers and Daos
request - The http-request object
user - The current user (UserDto)
field - The field this conversion performed for (com.intland.codebeamer.flow.importer.ImportField instance)
column - The index of the column the conversion is performed for
tracker - The tracker being imported to (TrackerDto instance)
importForm - The import form bean (com.intland.codebeamer.flow.form.ImportForm class or ImportIssueForm subclass)
bindings - The bindings map contains the variables available in the script

Few examples of conversion JavaScripts:

To extract first 5 characters of the original text use substring JavaScript function:
```
input.substring(0,5);
```
To extract a sub-set of the original text using a Regular Expressionuse this:
```
(new RegExp("(\\d*)")).exec(input)[0]
```

For more complex conversions you can embed a JavaScript function like this:

// adds a "hello" prefix to the original data
function convert(input) {
 var result = "hello " + input;
 return result;
}

convert(input);

Test Case and Test Step import

As CodeBeamer supports Table fields in Trackers, importer has some advanced support for importing data into such fields. Table field is a special field type, which means that the field contains a tabular data with named columns, and that can contain several rows. A typical example of that is the Test Case's "Test Step" field which is a table field with 3 named columns, which are the "Critical", "Action" and "Expected Result" columns.

For example:

When you want to import data to such Table fields you have the following options:

Import Table field data from cells which contain a HTML table
Import from cells which contain CSV - Comma separated values- data
Import from cells which contain multiple data values using some simple separator (separated by ; or , character or similar)

In the followings here we provide some examples.

Mapping Table fields' columns to Excel data

No matter which conversion is applied later first you will have to map the Excel cells to the Table fields.

Assuming that you have an Excel sheet which contains one column which contains the Test Steps in HTML table format. Here is the sample Excel file downloadable testCasesWithSteps.xls.

This screenshot shows the column which contains the HTML table we want to import into the "Test Steps" field. The "F" column contains the HTML table with the data:

The Table fields also appear on the mapping page of the import wizard. These appear on as separate fields like: "Test Steps.Action" and "Test Step.Critical" and "Test Step.Expected Result in the field list. By mapping them to a column you can load the column's data into the Table fields!

Because the cells in this "F" column contain all data for "Test Steps" we have to map all columns of "Test Steps" table to this single Excel column. This can be achieved by selecting multiple elements from the field list. Use CTRL-click to select multiple elements. Here is how it should look like after proper selection:

Converting HTML table data to Table field

After the HTML table is mapped to the Table field on the next wizard page we need to configure which part of the HTML table goes to which field. This is done by selecting the "OneColumnFromHtmlTableCellLoader" converter. This converter will extract one column from the HTML table, and puts it to one column to the "Test Step" table cell.

For example if this is the HTML table in our Excel cell:

action	expected result	critical
fill up the tank	check if it's full %	false
go to driving range and reset odometer	it shows 0km	false
start driving and drive until fuel is out	car stopped	false
...	...	...

Then you want to extract the 0th column (red) and map to the "Action" field, extract the 1st column (green) and map to "Expected field", extract the 2nd column (blue) and map to "Critical" field. This extraction is configured as the following screenshot illustrates (config is indicated by the matching color):

Reviewing if mapping and conversion is correct for Table cells

If the field is correctly mapped and the column is correctly extracted from the HTML table the preview page of the import wizard should show something like this:

Specialized Converters for Table fields

In the previous example we've shown the OneColumnFromHtmlTableCellLoader which extracts one column from an HTML formatted table. There are few additional converters, and as last resort you can always use a JavaScript script using ScriptConverter .

"Splitter" table converter

Splitter converter can split the input data from the Excel cell to several smaller sub-parts. This is useful if your table cell has such content:

apple|banana|peach

And you want to load that into a Table field like this:

fruit
apple
banana
peach

This case you should use | character as delimiter, and map the result to the "fruit" column in the Table field. An example shot for this converter:

"OneColumnFromCSVCellLoader" table converter

This is similar to the HTML table converter, but instead of a HTML table it can extract data/column from a CSV data So your data in cell is a CSV like:

fruit;price
apple;10
banana;20
peach;5

Then you can extract the "fruits" from this data using ";" as CSV separator, and by mapping the 0th column to the "fruit" Table cell, and 1st colum to the "price" Table cell. An example:

"OneColumnFromHTMLTableCellLoader" table converter

Probably the most useful converter is which we've seen in the above example. This extracts columns from a HTML table which can be mapped to columns in Table cells.

Table of Contents