Exporting TF Status to Excel

Exporting TF Status to Excel

Posted by richard | December 14, 2012 | TFS 2010, TFS 2012

You can use TF.EXE STATUS to display information about pending changes to items in one or more workspaces. I use this prior to migrations to see what users have files checked-out and locked, and they types of pending changes I’m up against. The utility can give you two outputs: Brief and Detailed.

Brief looks like this:

image

Detailed looks like this:

image

As you can see, neither is good for grouping, sorting, searching, etc. The answer was to convert it to Excel (or SQL Server for really large outputs) so I could play with the data a bit, but that fact that the structure of the file was complex (not simply fixed-width or delimited) posed problems. I’m sure Excel experts could figure it out, but I cheated and wrote some code. Here are the steps I followed:

  1. Ran TF STATUS for all users and exported to Detailed.txt

tf.exe status /collection:http://vsalm:8080/tfs/scrum /user:* /format:Detailed > Detailed.txt

  1. Ran a command line utility (provided below) that reads the detailed output and generates a CSV file.

I chose to name the file .CSV even though the delimiter may not be a comma. For my sample, I used an exclamation point. If you are going to use this utility, then you just need to tweak the path names and delimiter and let it rip. Make sure the delimiter you choose isn’t also being used in the body of the document somewhere.

  1. Launch Excel.
  2. From the Data menu select Get External Data > From Text and select the file you just generated.
  3. Select Delimited and specify the character (i.e. !) used to delimit the columns.

image

  1. Format the Date column to be a Date (MDY) type.

image

When the wizard is finished, it will give you a nice spreadsheet from which you can sort, group, filter, pivot, etc. to your heart’s content.

image

Files: TFStatus2CSV.zip

Related Blogs

Posted by richard | October 7, 2013
The One Work Item check-in policy
Thanks to my fellow Visual Studio ALM MVP Colin Dembovsky for building the “One Work Item” check-in policy that I was always too lazy to build. Judging by the title,...
Posted by Simon Reindl | March 14, 2013
Rename a TFS Project Collection
I was asked the other day how to rename a Team Project Collection. There is a way, and it is more like a three card shuffle than anything else, and...
Posted by richard | November 28, 2012
What’s new in the Visual Studio Scrum 2.1 Process Template?
If you’ve connected to the hosted Team Foundation Service lately, you’ve noticed a new version of the Scrum template is available … Being the curious type, I connected to the...