In a previous post we looked at using the de-facto standard for importing data into EA – the CSV import. Now while this method works, it is a pain and comes with the limitations of using a CSV based format – one wrong comma can throw off your entire data set!
I've been meaning to write about the wonders of eaDocX for a while now but it seems this is my first opportunity to sing its praises. eaDocX does away with the horrendously outdated RTF functionality that EA uses and replaces it with the new standard XML based .docx format
In additional to the huge host of benefits from using .docx (I'll write about them soon I promise!), the corporate edition comes with much-needed Excel integration. We can use the power of Excel to import/export data from EA without having to be constrained by CSV.
Lets take a look!
So when you have eaDocX installed, it appears as an extension.
We can select a package and open in Excel – hooray!
Open packages directly in Excel with eaDocX!
This hands the reigns over to eaDocX to take the package XML and recreate it in a .xlsx document. What does this look like? It looks an instance of Excel with all elements in your package listed
Packaged viewed within Excel
Notice the ‘Element’ listing on the left. This defines the type of element to be included, which in our example is the default ‘Package’ and ‘Requirement’ which suits our example of importing requirements quite nicely!
Click on the ‘Columns’ tab to select the attributes to display for each element.
select columns to display for the selected elements.
I’ve selected a few extra columns to display for this example, namely adding the alias, description and status.
Once you’ve selected the extra columns, hit the big EA –> button to export these new columns into Excel.
adding requirement-related columns
To make changes to this and re-import is easy! Simply make the changes directly into the Excel and hit the middle button ‘EA =’ to perform a comparison on your changed data and the EA package.
making changes and performing a comparison with the EA package
I’ve gone and changed the status of some requirements to ‘Active’ and the comparison has highlighted the change cells.
To import these back into EA, you guessed it, the 3rd and final button, EA <-
If I know look at my requirements in the ‘Package Browser’ I can see all of my requirements have had their statuses updated to ‘Active’ – magic!
seeing the changes applied back into EA
A practical application of importing and re-importing
If you’ve read some of my other articles, this is the part where I say, well that's interesting, but how is it useful? Well wait no more friend, I shall reveal.
In most organisations (unless your already deep in EA world or love your Rational Suite) the requirements are written up in either Word or Excel and have various levels of ‘document management’ applied to it to keep it maintained.
My first step in most projects is to remove the risk and import a reasonable baseline document into EA to begin building a solid model. This works well the first time, but requirements being requirements, they tend to change over time and you may not have the luxury of enforcing maintenance through EA. If that’s the case, you need a repeatable process to keep the EA requirements in sync with any externally managed ‘artifact’ (typically the ubiquitous Word document).
Providing you have a stable requirement number (I hope for at least that much!) you can always keep things maintained.
A Worked Example – Team Foundation Server (TFS) synchronisation
I use this example for TFS but in can be applied to any external system using a reference number.
We have a lot of requirements stored in TFS and updates managed through its version history, so at any given point in time the TFS item will have the latest status and description of the requirement.
We can export the list of TFS requirements into Excel easily (handy MS product integration), but the old copy-and-paste method works just as well if an export function exist for your external system. We end up with a output something like this
an example out of TFS requirements in Excel
The title here is generally longer than this but we have a structure which is Client - Req# – Short Name so I can quickly strip out the rest to get just the Req number as above.
Now that I have something like that, I can use the power of VLOOKUP in Excel to retrieve the value of the TFS item from my eaDocX Excel version. Confused? Let me explain in pictures.
I write a VLOOKUP function to lookup the value of the requirement ID (REQ-001) and find the associated column for the TFS ID.
If your unfamiliar with VLOOKUPS, essentially its using the value of B17 (the requirement ID), looking into the worksheet ‘Raw text’ between cells C2 and G217 for a match and returning the value in the 1st column which is a validation check to ensure the right record is return. The FALSE at the end ensures an exact match is made (not a partial)
So what happens when we do this for our data set?
VLOOKUP returning the relevant TFS tasks for each requirement
I know have a list of requirements with their corresponding TFS item # – magic!
Now VLOOKUPs can be tricky, so I add an extra validation step to the one I mentioned above – performing a simple check between the requirement ID return in the VLOOKUP and the original requirement to ensure they are the same. if not, we must be returning the wrong value.
What does this look like?
That's it! it will return TRUE if they match or FALSE if not. Rinse and repeat for all cells.
adding a bit of basic validation
Protip: I often use a conditional formatting rule to mark FALSE records in red to make life a bit easier.
When I'm happy everything is in order lets import back into EA.
But wait, where would I store such a value? Well this is precisely what tagged values are and should be used for.
So head to any requirement and view its tagged values.
None? That's okay, we need to add a new one.
adding a new tagged value for the TFS #
Don't enter a value but give it a useful name.
Now when we return to our eaDocX Excel view, we should be able to see our tagged value in the list of available columns.
selecting the tagged value to be part of the export to excel
Look there he is!
Export a new version to Excel.
all blanks because nothing has been added yet
A column full of blanks? Excellent. Now we can add our data from the VLOOKUP before and sync it back to EA
Run the Compare function.
performing the compare
A whole column of blue changes – that's what we want.
Hit the import function to sync these back into EA.
Back in the Package Browser we can see these changes have been applied
seeing the TFS #s in the package browser
This can be repeated for other properties such as status, development effort, assignee etc but be careful how much you use because it is additional overhead to maintain.
My motivation for keeping both TFS and EA in sync is to give the power and flexibility of using EA to model, analyse and output static documents for clients while retaining the familiar interface of TFS for the development team.
Note: It must also be repeated that when importing at scale the validation of data becomes important as it can be quite easy to accidentally overwrite things – so don’t neglect the validation checks and be sure before you commit changes!
A closing note
Hopefully that's a clear enough explanation of how to use the functionality provided by Excel integration from Ian and the eaDocX team and I’ll be back to write-up more on the even more useful documentation generation and document management capabilities it offers – but until then this will have to suffice!