Group object by a csv column and select maximum value from the other one

user_14874627 0
Viewed 93

I again need your help. I'm trying to group by "Users" column, showing the maximum "LastAccessTime" value and corresponding "FullName". MyCSV.csv below contains:

"Users","FullName","LastAccessTime"
"user1","c:\felder1\Sub1","20201203"
"user1","c:\folder1\Sub1a","20201223"
"user1","c:\folder1\Sub1b","20201123"
"user2","c:\folder2\Sub2","20201218"
"user2","c:\folder2\Sub2a","20201228"
"user3","c:\folder3\Sub3","20201212"
"user3","c:\folder3\Sub3a","20201214"
"user3","c:\folder3\Sub3b","20201215"
"user3","c:\folder3\Sub3c","20201011"

It works with this code except "FullName" refers only first line when it match due to $_.Group[0].FullName

Import-Csv -Path "$Env:USERPROFILE\Desktop\MyCSV.csv" |
Group-Object Users |
ForEach-Object {
        New-Object -TypeName psobject -Property ([ordered]@{
        User = $_.Group[0].Users
        FullName = $_.Group[0].FullName
        "LastAccessTime" = $_.Group | Measure-Object -Property "LastAccessTime" -Maximum | ForEach-Object { $_.Maximum } } )

} | Export-Csv "$Env:USERPROFILE\Desktop\result.csv" -NoTypeInformation

In fact the result is:

"User","FullName","LastAccessTime"
"user1","c:\felder1\Sub1","20201223"
"user2","c:\folder2\Sub2","20201228"
"user3","c:\folder3\Sub3","20201215"

but I wish it were so:

"User","FullName","LastAccessTime"
"user1","c:\felder1\Sub1","20201223"
"user2","c:\folder2\Sub2a","20201228"
"user3","c:\folder3\Sub3b","20201215"

what should i change?

csv powershell
1 Answers

You can adjust the accepted answer on this question of yours as such

$properties = @{Name='User'; Expression={$user}},
              'FullName',
              @{Name='LastAccessTime'; Expression={$_.LastAccessTime.ToString('yyyyMMdd')}}

Import-Csv $HomeDir | ForEach-Object {
    $user = $_.users
    Get-ChildItem $_.HomeDir -ErrorAction SilentlyContinue | 
        Where-Object {$_.PSIsContainer -eq "True"} | 
            Select-Object $properties |
                Sort-Object -Descending -Property LastAccessTime |
                    Select-Object -First 1
} | Export-Csv $newcsv -NoTypeInformation
user_40853310
Related