Display value from all file after merging in single csv-CodePudding

I have 12 csv file which I am reading and merging it into single csv based on condition.

but after merging I am able to get only one record against each server as I used $_.Group[0]

below is my code

$allFiles = Import-Csv -Path  (Get-ChildItem -Path C:\Files\ -Filter '*.csv').FullName

$Final_Data = $allFiles | Group-Object VM | Select-Object Name,Count, @{n='Start';e={$_.Group[0].Start}},@{n='Host';e={$_.Group[0].Host}}, @{n='VMDiskLatency';e={$_.Group[0].VMDiskLatency}},@{n='Datastore';e={$_.Group[0].Datastore}} | Where-Object {$_.Count -ge 5} | Export-Csv -Path "C:\Files\Final_Report.csv" -NoTypeInformation

the data is like below in $allFiles

Start                 Host                  VMDiskLatency Datastore                                                                                                               
-----                 ----                  ------------- ---------                                                                                                               
1/11/2022 8:12:01 AM  t02.r                    94            MGT_1d59
1/11/2022 8:12:01 AM  z02.r                    81            Z_3255_325a                                                                             
1/11/2022 8:12:01 AM  n34.r                    108           EN_259b                                                                              
1/11/2022 8:12:01 AM  n35.r                    210           EN_259b                                                                                 
1/11/2022 8:12:01 AM  n37.r                    104           EN_f5bf

with my code I am getting final output like below

Name           Count    Start                Host           VMDiskLatency   Datastore
TN2AP           12  1/11/2022 8:12            n34.r            108           GEN_319c
TN1AP           12  1/11/2022 8:12            n35.r            210           GEN_259b
TN4AP           12  1/11/2022 8:12            n37.r            104           GEN_f5bf
H1SC            12  1/11/2022 8:12            n34.r            202           GEN_7a29

but the output I am looking for is like e.g giving for 1 server

Name           Count    Start                 Host          VMDiskLatency   Datastore
TN2AP           12  1/11/2022 8:12            n34.r            108           GEN_319c
                    1/11/2022 7:12                             201
                    1/11/2022 6:12                             182
                    1/11/2022 8:42                             103
                    1/11/2022 8:22                             66
                    1/11/2022 5:12                             99
                    1/11/2022 4:12                             541
                    1/11/2022 9:12                             100
                    1/11/2022 8:12                             209
                    1/11/2022 6:32                             115
                    1/11/2022 8:12                             78
                    1/11/2022 8:12                             62

The server in above came 12 times ( means in all the csv files) so, for Start and VMDiskLatency, there should be all the 12 entries. If it was 6 then, all the 6 entries and so on..

Please let me know how can I do that.

CodePudding user response：

If you want to only attach the Name and Count values are set on the first object in each group, make two distinct selections - one for the first object with the property values, and one for the remaining objects where the property values are empty:

$allFiles |Group-Object VM |Where-Object {$_.Count -ge 5} |ForEach-Object {
  $name = $_.Name
  $count = $_.Count

  $first,$rest = $_.Group

  $first | Select-Object @{Name='Name';Expression={$name}},@{Name='Count';Expression={$count}},'Start','Host','VMDiskLatency','Datastore' 
  $rest | Select-Object @{Name='Name';Expression={""}},@{Name='Count';Expression={""}},'Start','Host','VMDiskLatency','Datastore' 
} | Export-Csv -Path "C:\Files\Final_Report.csv" -NoTypeInformation

I'd personally recommend keeping the values attached to each object in the group, this way you can re-order the data without losing that context - and your expression becomes much simpler:

$allFiles |Group-Object VM |Where-Object {$_.Count -ge 5} |Select-Object Name,Count -Expand Group |Export-Csv -Path "C:\Files\Final_Report.csv" -NoTypeInformation