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

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

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:

• Any Excel sheet can be imported, by default the last-selected sheet is imported
• 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...

Importing multiple sheets

If the Excel file contains multiple sheets then the user can select which sheet will be imported. The import will choose the last-selected sheet (which was selected the last time when the Excel file was saved), but user can switch to some other sheet. When switching sheets the mapping will be reconfigured and the selected sheet's data will be displayed.

Structure of the import data

CodeBeamer expects that:

• The first row of the Excel import is the header: the codeBeamer field names are mapped from here.
• The real data starts at the 2nd row of the Excel sheets that is imported from here.

If your data does NOT start at the 2nd row, but somewhere below then you can choose the start row by clicking on the 1st data row, or by changing the row number here:

Also the importer tries to figure out where your data starts by looking at the "locked" property of cells in the Excel sheet. The logic here is that your data starts where the 1st not locked and non-empty cell is. So you can control where your data starts by editing this in your Excel sheets. Here is an illustration screenshot:

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 first data-row can also be selected by clicking into the data-table below: the clicked row will be the 1st where your data starts.

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 if you turn on the "Multiple field mappings" checkbox above the table: so 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.

Automatic and type-safe mapping

The import wizard automatically tries to figure out the mapping by picking the field which is "closest" match to the header text (the 1st row) in your Excel sheet or input data. This produces good mapping most times, but always carefully review the automatic mappings and adjust if necessary.

The auto-mapping also checks the data-type of the input data. For example if a column in your Excel sheet only contains numbers that will be/can be only mapped to the numberic fields of codeBeamer. If you want more flexibility then you can turn this strict mapping off:

Updating existing Work Items

When importing data-rows to codeBeamer any row of the import data can either create new items, or update existing items. The importer by default uses the ID field for deciding if a data is an update:

• if some data is mapped to the ID field then the importer will try to find the existing item with the matching ID number and if if it is found then the found Work item will be updated.
• otherwise the data will be added as new Work item

This default mapping by the ID field is perfect when using Excel roundtrip, when the exported Excel sheet contains the IDs of the original Work items.

However in some other cases (when the import/Excel data is not originated from codeBeamer's roundtrip) you may want to use some other fields for finding the target Work items and update those.

For example the import could use the Summary (= Name) field for looking up the existing Work Items. If an item with the same Summary found that will be updated in the import.

An example use case scenario:

• User imports an Excel sheet to Work items,which creates several new items, for example one with Summary="Monthy Python"
• Then the user adds some new rows to the same Excel sheet, and changes some cell data in the row of "Monthy Python"
• The user tries to import this modified Excel sheet. Now if he selects the "Summary" field as identifier of the existing data then:
  • The importer should find the existing Work item in CB with Summary="Monthy Python", and update this instead of creating a new one
  • For any new rows in Excel the importer will not find a matching Summary, and these will be inserted as new Work items.

This flexibility allows that the same Excel sheet can be imported several times without creating duplicates of the same information. This also is useful when importing from Microsoft Project several times: the changes from the MS Project can be updated and keept synchronized with codeBeamer this way.

On the import wizard you can configure which fields are used to look up the existing Work items. This screenshot shows how to configure the "Summary" field:

Different data-mapping for New items and Updated items

Sometimes when you import both new items and update existing items from the same Excel file you may want to have different mappings between the new and existing items.

Such scenario happens when you just want:

• Insert new items using All input data
• But only add a Comment to existing Work items

You can achieve this by selecting which fields are used for new items and existing ones on the 2nd page of the import Wizard. The mentioned scenario is configured as the screenshot shows below:

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. When importing dates you can specify which time-zone that date is.

Skipping certain cells

During the import you may want to avoid updating certain values of one or few items. Then in your Excel sheet you can edit the cell(s) which should not be loaded and change it to a special value which tells the importer to skip that and keep existing value.

This special value can be configured during import here:

This means that if the Excel cell contains the "<SKIP_DATA>" text that will be skipped and ignored

The value of not-mapped Fields

When importing new Tracker-Items the not-mapped Fields will get their initial value from the Tracker Configuration: so they will get the same value as if you would create new items normally there. (Since CB 8.1.1+)

The value of empty cells

When an Excel cell is empty and that is written to a certain Tracker-Item Field that will clear the Field value during import.

You can change this behavior during importing new Items, by using the option below: by using the "Set to the initial value configured in Tracker" will cause that empty values will be set to the same which is configured in the Tracker.

So For example if the "Severity" field has "Critical" initial value in the Tracker configuration then empty import data will be set as "Critical" 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.

Use the "indent" button of Excel located in the Excel toolbar:

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

See also: Excel Round-trip and Excel import and Exporting hierarchy of Work items to Excel using custom templates

Special mapping: importing comments

There are few 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" or "Comment2" 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 comments to issues.

The "Comment Format" or "Comment2 Format" field is specifying the format of the "Comment"/"Comment2" 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 The date import also trims down the lead/trail " or ' characters, so a value like __"2018-01-01"__ is handled as __2018-01-01__ value.
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 foundthen the converter: Tries to create a new choice value automatically in the target Tracker, and uses this new value, or if the choice value can not be created then the field is left empty
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 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. For example if the cell contains the text "Banana" and there exists an Work Item named "Banana" that will be automatically found. 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.0the 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
Country/Language field	Can contain 2 letter like "de" 3166 ISO code or country "Germany" or language name "German" as well

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 (Deprecated since codeBeamer 9.4)

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.

First set the "Multiple field mappings" checkbox to enable multiple choice options in mapping.

Once multi-choice is enabled select the required table columns for field mapping. Here is how this looks like in our example:

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:

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:

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.

Importing Images from Excel

codeBeamer imports images in Excel workbooks and attaches images to the target issue/work item during import. The import also adds the wiki references of these images into the fields where the images appear.

Yet there is one limitation: the images from Excel are just attached to the end of the text of the cell where they belong.

This is happening because in Excel the images are not "inside" of the text, but floating above them, and there is no exact location is known where the image belongs. For example if in excel you have a cell with "apple [picture] banana", then there is no information available that the "picture" is actually between the two words (there is no anchor to the picture there).

This should be manually corrected by editing all the locations where the images are inside codeBeamer and putting the images to the appropriate places.

FAQ and Hints/Tips

Can not import my Excel what to do?

If you get an exception during import like "...part /xl/styles.xml fail..." that might be caused by that your Excel document contains such formatting or style elements that can not be loaded by our Excel processing code.

The solution should be that you prepare and clean-up your Excel data for importing. Do this:

• Create a new empty Excel document, and copy-paste the data you want to import to codeBeamer
• When pasting the Excel data use the right-click menu, and choose the "Values" only paste option from the menu:
  

Now your data is clean, should be imported well to codeBeamer...

When importing a special Excel file the importer starts at at a "random" row not where my data really starts, why?

The Excel importer tries to be "smart" to figure out where the "data" starts by looking at the "Locked" property of the cells. If a cell is not "locked" that means editable by the user, so the data is starting there (at the first cell of such.).

Here is how the cell should be marked where the data starts: The gray cells are all locked: so they are considered as HEADER data an not imported by default:

Troubleshooting

I wanted to import 10k row but 9996 is imported

It is a limitation of tomcat, please change the maxParameterCount in your server.xml file

<Connector
   ...
   maxParameterCount="10100"
   ...
/>