Copy output powershell TFS data into an excel sheet

Question

I am querying TFS with TFPT.exe and powershell as shown below:

$TFSSERVER   = "http://tfsserveraddress"

Function Get-WorkItem {
$query = "SELECT [System.Id], [System.Title], [System.State], [Completed Work] FROM WorkItems " +
         "WHERE [System.AssignedTo] = 'xyz ' " +
         "AND [System.State] <> 'Closed' " +
         "AND [System.State] <> 'Resolved' " +
         "ORDER BY [System.Id]" 


tfpt query /collection:$TFSSERVER /wiql:$query /include:data >work.xls     }

In the above code I am able to view the data as expected but I am trying to create and copy the data into an excel sheet which is not happening.

Can anyone please help me how to copy the output data into an excel sheet or in xml format.Pleas ehelp

Thank you.

What does that code generate, just a multi-line string, or array of strings? Or does it output objects with properties, or a datatable object? — TheMadTechnician, Mar 24 '15 at 02:24

score 0 · Answer 1 · answered Mar 24 '15 at 02:57

Not having access to TFS server, I'm assuming that tfpt query command produces a table of data, which PowerShell converts to a PSObject.

This should produce a valid CSV file, which can be opened in Excel:

$data = tfpt query /collection:$TFSSERVER /wiql:$query /include:data
Export-Csv -Path "c:\temp\file.csv" -InputObject $data -NoTypeInformation

Note: You need PowerShell v.3 to run Export-Csv.

Copy output powershell TFS data into an excel sheet

1 Answers1