Count, Sort and Group-By in Powershell

Question

Are there any cool cmdlets that will help me do the following? I want something in Powershell that is as simple as doing the same in SQL:

select RootElementName , count(*) from Table
group by RootElementName 
order by RootElementName

I'm all XML files in a directory, finding the root element of each XML file.

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force 
foreach ($Filename in $AllFiles) 
{
     $FQFilename = $DirectoryName + $Filename
     [xml]$xmlDoc = Get-Content $FQFilename
     $rootElementName = $xmlDoc.SelectSingleNode("/*").Name; 
     Write-Host $FQFilename $rootElementName 
}

Desired Result:

RootName   Count 
--------   -----
Root1      15 
MyRoot     16 
SomeRoot   24

I know I could could either create two arrays, or an array of objects, store the root elements in the array, and do the counts all using typical code, was just hoping that this new language might have something built-in that I haven't discovered yet.

Could I pipe the "Write-Host $FQFilename $rootElementName " to something that would behave something to the SQL I referred to above?

At a powershell prompt, type: **Get-Help Group Object -detail** and **Get-Help Sort-Object -detail**. Start there. — EBGreen, Jun 20 '14 at 14:48
@EBGreen, is there possibly a nicer way to phrase that? The `Get-Help` command, while a great reference to the docs, is a little bit like "do you know how to use the docs?". Instead, maybe, something like below... — KyleMit, Jun 28 '19 at 21:26
A good starting point might be [`Group-Object`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/group-object?view=powershell-6) and [`Sort-Object`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/sort-object?view=powershell-6) — KyleMit, Jun 28 '19 at 21:26
@KyleMit, if I had have had the time I would have written an actual answer. Sometimes I just have enough time to look through some questions real quickly. In those instances, if I don't have enough time for a proper answer I will suggest something in a comment to hopefully get the OP started in the right direction so that they might find the answer on their own. So that they at least hopefully make some progress on solving their issue. — EBGreen, Aug 23 '19 at 04:49

score 16 · Accepted Answer · edited Jun 28 '19 at 21:35

You can get groups and counts by using Group-Object like this:

$AllFiles | Group-Object RootElementName | Sort-Object Name | Select-Object Name, Count

In your current example, Write-Host doesn't write an object to the pipeline that we can sort or group. Write-Host only prints text to the screen to show the user something, ex. a script menu.

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force | ForEach-Object {
    #The FullName-property contains the absolute path, so there's no need to join the filename and $directoryname
    [xml]$xmlDoc = Get-Content $_.FullName
    $rootElementName = $xmlDoc.SelectSingleNode("/*").Name

    #Outputing an object that we can group and sort
    New-Object -TypeName psobject -Property @{
        FileName = $_.FullName
        RootElementName = $rootElementName
    }
}
$grped = $AllFiles | Group-Object RootElementName | Sort-Object Name | Select-Object Name, Count

I'm creating an object with a FileName-property and the RootElementName so you have it if you need to retrieve the filename+rootelement for a list. If not, we could simplify this to:

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force | ForEach-Object {
    #The FullName-property contains the absolute path, so there's no need to join the filename and $directoryname
    [xml]$xmlDoc = Get-Content $_.FullName

    #Output rootelementname
    $xmlDoc.SelectSingleNode("/*").Name
} 
$grped = $AllFiles | Group-Object | Sort-Object Name | Select-Object Name, Count

Count, Sort and Group-By in Powershell

1 Answers1