Importing Items Via Excel

By Knowledge Base posted 02-03-2015 01:30

  
Excel can be used to import many kinds of items into Jama, one of them being requirements. However, Excel spread sheets must be formatted correctly in order for Jama to successfully import them. Required fields such as Name, Description, and Status must be listed horizontally at the top of the spreadsheet, with qualifying information below them. Custom fields can also be included in the import and should be listed in the same manner.

In the example below, each row will be imported as an individual item along with the specified information within that row.

Example Spreadsheet




NOTE: When entering information into a field that is a pick list (such as Priority), be sure to exclusively use options from that pick list.

Entering options that are not in the pick list will result in blank entries upon import, as shown in the example below with the value of "Fun".



Importing in Jama


After you have filled out your spreadsheet to your liking, you can import it into Jama. This can be done from two locations: from the project menu at the top of the Project Tree, or from right-clicking the desired set or folder.

From atop the project tree:



From the desired location:



Using the Data Import Wizard 

From the Data Import Wizard pop-up window, you will be prompted to choose your Excel document. You can also choose or change the desired import location.Note: The document to be imported needs to be an .xls type, regardless if using MS Excel or another program.

Field Mapping allows you to select conditions for a document you have saved, allowing for faster imports. This is a good idea to set up if you find yourself importing multiple documents with the same field-mapping layout.


Encoding will be automatically set on UTF8, the character encoding used by Jama's database.



After uploading your document, you need to map the fields so Jama knows what information to put where.

Under Excel Optionsselect what sheet from your document you want to use. Excel documents can be imported only one sheet at a time. If you have a document with multiple sheets, you will have to import them separately.

Next, select the Excel Header Row. This will be the row in your document where you list field labels, such as Name, Description, or Status. It is important that you select the correct row, or your field mappings will not be correct.



The last step is to map the fields for your import. From the drop down menus to the left of the Item fields, select which field you would like to use. In the example below, we have mapped Name to Name, Description to Description, and Status to Status. Select "Next" when you are finished.

You will be able to tell you have selected the correct header row, because the field name values will be in the Mapping drop down.



If the fields have been mapped successfully, the final pages of the Data Import Wizard will first show a preview of the imported information...



...as well as notify you how many total items were added. From this page you have the option to import another file. You can also save how you mapped this document to be used for future uploads.



When you use the layout above, your import will look like this in the project tree.

Example Import




Importing Items Via Excel with Hierarchy
Hierarchy can be imported into Jama through Excel in two ways, through indentation or cell background coloring.

NOTE: Hierarchy cannot be established or edited during roundtrip import/exports.

Hierarchy using Indentation

Excel's indent option allows users to specify hierarchy within Jama for the items being imported.



Above, you can see that the indentation hierarchy has been used in the "Description" column, which the user must specify from the pick list on the left. If a user had indented cells under the "Name" column, the user would choose "Name" from the pick list.

Example Worksheet

Here, indentation has been used under the "Name" column with 3 tiers of hierarchy.


Note: the Excel "Increase Indent" button should be used for indent. Using keyboard spaces won't work.

Example Import

After importing a spreadsheet using indentation, the items will appear like the following in the project tree.


Hierarchy using Cell Coloring

Excel's cell coloring option allows users to designate item hierarchy upon import.



Above, you can see that the cell color hierarchy has been used in the "Name" column, which the user must specify from the pick list on the left.

Example Worksheet

In the below example, each colored cell contains the parent items and all subsequent items will be the child items.



Example Import




NOTE: The cell coloring hierarchy method only allows 1 tier of hierarchy and does not differentiate between colors.

Structure

Jama inherently creates a folder-item structure when importing a hierarchy of items.

In order to create an item-sub item structure you must specify by checking the box while using the import wizard.



Example (Item-Sub Item Vs. Folder-Item)


Here is a comparison of the two structures within the project tree;

Sub-Item Structure



Folder-Item Structure



#requirementsmanagement
4 comments
924 views

Comments

10-09-2015 18:27

Victoria, it looks like you also submitted a support ticket for this issue. Because Paul has already started the troubleshooting process, I think it's best to let him continue. Don't hesitate to post to the community in the future, though! We have support engineers available here and via the ticketing system.

10-09-2015 17:09

Hi, I have a similar question on importing excel files to update and overwrite data in the same fields. I updated some of the data in my Excel file which was originally created via a round trip export. I had selected "Update, on import Excel wins" option, but when I import, I have another duplicate set of requirements that are imported, rather than updating the requirements that I already have. How do I update my current fields with the updated info?

04-27-2015 18:44

Srini, you are actually running into a known issue, SOS-BUG-330. Essentially these "Additional Options" aren't compatible with each other; when you select the Indented Cell option and the Colored Cell option, Jama will obey the Colored Cell option over the Indented Cell option. I would recommend not checking the Colored Cells option, and convert those items to folders after the import is complete.

I've attached this report to the bug in our internal tracking system, so we'll reply to this post when the status of the bug changes.

04-24-2015 16:49

Thanks for your explanation Paul.  However we have little different scenario here.  In our case,  using the above example ---  "Imported Requirements"  should be a folder.  Whereas "Requirement 1"  should be parent item and "Sub-Requirement A"  should be a child item.

In Excel, Column containing "Imported Requirements"  is color coded ( yellow ) and no indentation. Then in next ( immediately followed ) row  we have "Requirement 1"  in the same column, again with no indentation.   Then in the third row we have "Sub-Requirement A"  but this time it is indented. 

In the Import options user interface,  We have selected all the three check boxes.  Upon import,  we notice "Imported Requirements"  is NOT a folder.  Whereas "Requirement 1" and "Sub-Requirement A" are correctly depicted as parent-child item. 

Modified Import options and did NOT select the first check box. However in that case, "Imported Requirements" and "Requirement 1"  are marked as folder which is incorrect.

So for our case how do we setup ? 

Any help appreciated.

Regards

Srini