|Importing Data from Excel to codebeamer #543510/HEAD / v6924|
Tags: not added yet
Table of Contents
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:
Gettings started: preparing your data
Before importing some Excel data-file it should conform to following rules:
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:
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:
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:
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:
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:
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:
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.
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:
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:
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.
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:
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:
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:
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.
When you want to import data to such Table fields you have the following options:
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
"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:
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:
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:
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" ... />
codebeamer Knowledge Base
Services by Intland Software