When moving to Azure DevOps, or when working offline away from your existing Azure DevOps project, you need a way to bring your newly created requirements into Azure DevOps.
Many teams face the issue of getting the requirements they have created in Word, Excel, and elsewhere into Azure DevOps. Luckily there are a few simple ways to do this without having to worry about adding a lengthy copy/paste session to your process!
In this article, I will cover a few different ways to import requirements. So lets continue with the different approaches to import work items successfully in Azure DevOps.
- Importing Business Requirements from Microsoft Excel
- Importing Business Requirements from Microsoft Word
Importing Business Requirements from Microsoft Excel
Whether you have all or some of your existing requirements in Excel, or you are looking to export requirements from an in-house tool to a .csv file, there is a free way to import your requirements to your Azure DevOps project.
This is a free solution – provided by Microsoft and you already have Azure DevOps and Excel.
The first step is to make sure you have the Microsoft Excel add-in called “Team tab”
You can download this add-in directly from the link below and install it:
https://go.microsoft.com/fwlink/?LinkId=691127
If you clicked the link above, you will have the ability to turn on your Excel team tab.
When enabled, this extension allows you to connect an Excel sheet directly to a given project in your Azure DevOps Organization.
When you enable it you will have two primary functions available to you:
1. You will be able to publish requirements to your project from Excel
2. You will be able to pull requirements from your project to Excel
This means you can work on your requirements from either interface and connect the changes to your project. i.e. if you pull requirements into Excel and make changes, you can publish those changes backup to your requirements in your project.
After you have run the installer you downloaded you are ready to enable the extension. Below is standard workflow –
Enabling the Team tab in Excel:
- Open Excel
- Create a Blank Sheet
- Click File
- Click Options
- Click Add-ins
- Choose COM Add-ins from the drop down near the bottom of the window
- Select “Team Foundation Add-In and select Okay.
You can also visit the GitHub repository for more information about this plugin and enabled it - azure-devops-docs/track-work.md at main · MicrosoftDocs/azure-devops-docs (github.com)
If you now see the Team tab in Excel, you’re ready to import requirements! I am not going into much detail because there is so much documentation and online material available on importing requirements from MS word.
Importing Business Requirements from Microsoft Word
The challenges comes when you need to import something from Microsoft Word where business requirement are under different headings (H1, H2) or Bullets and Numberings and you don’t have any office plugin available in word just like you have seen in excel discussed earlier in this article. Word import only works by having a well-formatted word document which uses different headings to represent the different Work Items / Requirements and their properties in your document. You need to parse this information and identify parent/child relationship (e.g. Epic, Feature, User Story, Tasks and Bugs). In below screenshot you can see I have some sample requirements which translate to epic, features, user story, tasks and bugs and I must import them successfully in Azure DevOps with maintaining parent/child relationship. You noticed that requirement arranges under bullets and numbering. This requirement may be arranged under headings (e.g. H1, H2 and H3).
So let’s start to import this word document requirement into Excel first and then ADO. Below are standard steps to do this -
A. In my case above requirement is specified in a sample word document called Demo.docx using Bullets and numbering format.
B. I have to save the copy of this word document in text format (because excel supports only plain text/csv file import) as shown in below screenshot (e.g. Demo-Text.txt).
C. While saving it may popup file conversion dialogue box, just click on without selecting anything.
D. Open plain text file (Demo-Text.txt) in notepad and tab properly to each item as shown in below screenshot. Tab should be applied to maintain their parent/child relationship. In my requirement customer requested to maintain the numbering with work item title in ADO so I didn’t remove and tabbed properly.
In my sample data I have max 4 levels depth requirements (e.g. 3 > 3.3 > 3.3.1 > a, b) so tabbed accordingly (which translate epic, feature, user story, tasks/bugs, you can assume any work item hierarchy example user story can have sub user story then tasks and so on).
E. Now next step in to import this data into excel so open excel blank workbook and select data menu > go to From text/CSV option (as shown in below screenshot).
Note: Excel support data import from Text/CSV (not directly from MS Word) that’s the reason I converted the word document to text file.
F. On file selection you will find transformation data dialogue box and you have to click on “Transform Data” button (as shown in below screenshot).
Just click on “Transform Data” button don’t change or select any other values.
G. Next dialogue box will be opened- “Your Query Editor” > Select Split columns > Select By Delimiter option (as shown in below screenshot).
H. Select tab as delimiter from dropdown list and click OK (as shown in below screenshot).
I. You will find that our business requirement data split into multiple columns as per desired hierarchy (parent/child relationship), now you have to load and close this window for final result (as shown in below screenshot).
J. At this moment You have successfully imported the requirement into excel from MS Word. Now next step You have create new sheet to export or publish this data to Azure DevOps. I believe you already have gone though first topic and learn how to install Office plugin. On successful configuration you will find an additional menu “Team” as shown in below screenshot. Let me summarize the information again here step-by-step.
- Click on “New List” because You are publishing new requirements to Azure DevOps.
- You will see a popup to connect with Azure DevOps and may ask you to validate you identify. Please provide all the required details.
- On successful connection, you will find the list of Team Projects as shown in below screenshot.
- Select the team project and click on connect button (as shown in below screenshots).
- In next step you will another New List window, You have to select new input list because You are trying to publish fresh data. “Query List” option only used when You want to modify the existing work items and append new items to the list (as shown in below screenshots).
- You will find a default list will be created with Title 1 column. This default list type will be Flat (means no parent/child) relationship (as shown in below screenshots).
Our objective is to maintain the 4 level parent/child relationship (as per sample data imported from MS Word) so click on Add Tree Level option and keep doing it until total 4 columns are created. Once you perform this operation our List Type will switch from Flat to Tree as shown in below screenshot.
g. Now time to copy imported data from Work Sheet 1 to Work Sheet 2 so You can publish to Azure DevOps. In below screenshot you can see I just pasted the data as it is and in define the work item type for each row. Our smart list is not enough intelligent to decide which row below to which work item type so You need to tell the system about it.
At this moment first column ID is blank because there is no work item ID assigned to the data. As soon as you will publish it to Azure DevOps, you will see the ID column will populated with values.
Conclusion
In this article You covered two distinct approach that you can import both requirements and their assets to your Azure DevOps project. My primary focus was to explain the requirement from Microsoft word because excel already has this functionality using office plugins and lots of documentation available online for this approach.