Share via

Tips when Importing Work Items into TFS

  • Article

Importing your backlog of work items into TFS from Excel is pretty straight forward until you start dealing with hundreds of work items with different state values. Here are some pointers to help keep your backlog clean and pain free.

Know the Latest Work Item ID // 

Knowing the latest work item is critical when importing and updating work items in bulk. Communicate with your team, or setup security accordingly, so that your team is not adding new items during your import process. Otherwise you may end up inadvertently overwriting or changing work items that are not part of your import.

Create a new query to pull all the work items in your collection so that you can get the last available work item id (@LatestWorkItemId). Hang on to the @LatestWorkItemId because you will need it when creating your import and update queries.

Use Separate Import and Update Queries // 

I like to get all my queries setup before doing the import. I am going to need a few different queries per work item type. For example, requirements (pbi or user story) have different fields than bugs, such as the description vs steps to reproduce fields. To make importing easier, create a query to import requirements and another to import bugs.

When importing work items the only state value TFS will accept on initial import is "New". After the initial import you need to update all the state values and re-publish. Another nuance with this is that Excel likes reformat your rich text fields after the initial publish. So if you have a nicely formatted description, Excel will muck it up and convert it to plain text. To get around this, create separate import and update queries. Your update query will not include any of the rich text fields so you don't have to worry about the text getting reformatted.

Here is an example of importing a list of active user stories. 

First create your query import and update queries and make sure you include the @LastestWorkItemId. We do this so that none of existing work items show up in our query results.

Next import the work items, set the state for all items to new, and publish. if you have not done this before see the MSDN article for details.

 Close the import query and open the update query. Notice that the update query does not have any rich text field columns. Update the state value from new to active and then publish. 

Note, we now have a new @LatestWorkItemId

One State at a Time // 

This is where keeping track of the @LatestWorkItemId comes into play. Most likely you are importing work items with a variety of states (for agile: new, active, resolved, closed). Remember TFS requires you to have the state set to new on initial import, so you have to update the work items after the first publish. This would be simple if we did not have the problem of Excel reformatting our rich text fields (see above). Because of this issue, we need keep track of what items we are importing so we don't get lost. Here are the steps you can take to prevent losing track of importing and updating your work items with a variety of state values.

  1. Manage your import files by state. For example Import_New.xls, Import_Active.xls, etc.
  2. Make sure the import and update queries reflect the most recent @LatestWorkItemId
  3. Import your first list of work items, publish and close Excel. 
  4. Open your update query, update your values and publish. Before you close, get the work item id at the bottom of the list. This is your new @LastestWorkItemId.
  5. Update your import and update queries to reflect the new @LatestWorkItemId (1666)

 

 

Now move to your next import spreadsheet and follow the steps 2 - 5.

Summary //

Importing work items through Excel is pretty painless once you figure out a few of the nuances. These are some of the things I have ran across when importing lots of work items with different state values. Hope this helps.