Use a calculated value in a Group-Object?

[Mike Leone]

So I have this CSV I am importing, each line has a date field in it. I need to make a summary report, showing totals of various types by year. But my date field is a full date (i.e., "08/05/2014") and gets imported as a string.

Now, I can get groupings by importing the CSV and piping through the Group-Object:

$IM_Inventory = Import-CSV -Path $InputFileName | SORT -PROPERTY LogicalVault, Media | Group-Object -Property LogicalVault, MT, Generation

This gives me:

Count Name
----- ----
  786 ="8MM", 8M-8MM Tapes,
 1454 ="DLT", TD-DLT Tapes (in-case),
 7259 ="LTO", LC-LTO Tapes (in-case),
    1 ="LTO", LC-LTO Tapes (in-case), L4
 7243 ="LTO", LC-LTO Tapes (in-case), L6
 1158 ="LTO", LC-LTO Tapes (in-case), L7

which is great. But I need totals by year within that LogicalVault (that's the ="8MM", etc).

I could construct a value from the "DateIn" field, which is imported.(this is pseudo code), since the field comes in as a string, I extract only the 4 rightmost chracters

$Year = $_.DateIn.SubString($_.DateIn.Length -4,4)

So how best to do this? Do I import-CSV, and then loop through, and Add-Member $Year? Do I need to save it as a custom object (populating the rest if the object with the other fields I just imported) in an array of objects?

And then Sort and Group on the new array of custom objects?

I'm not seeing a way to do this all in 1 line, like the Import-CSV and pipleine shown above.

Any hints gratefully accepted.

--

Mike. Leone, <mailto:tur...@mike-leone.com>

PGP Fingerprint: 0AA8 DC47 CB63 AE3F C739 6BF9 9AB4 1EF6 5AA5 BCDF
Photo Gallery: <http://www.flickr.com/photos/mikeleonephotos>

[Michael B. Smith]

The output of the group-object is a new object with three fields Count, Name, and Group. Count is just the number of objects in the group. Name is string from attributes you have selected (in your case “LogicalVault, MT, Generation”), and Group is the collection of all the objects meeting your grouping criteria.

 

So… you can select-object the output of group-object and do your calculations within the select-object.

--
You received this message because you are subscribed to the Google Groups "ntpowershell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ntpowershell...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/CAHBr%2B%2Bhq8%3DBbTmZGGo-y45r2d9v_%3D99yoziZEUxU9Gx%2BADiM2A%40mail.gmail.com.

[Mike Leone]

On Fri, Dec 19, 2025 at 1:21 PM Michael B. Smith <mic...@smithcons.com> wrote:

The output of the group-object is a new object with three fields Count, Name, and Group. Count is just the number of objects in the group. Name is string from attributes you have selected (in your case “LogicalVault, MT, Generation”), and Group is the collection of all the objects meeting your grouping criteria.

 

So… you can select-object the output of group-object and do your calculations within the select-object.

OK .. I'm still not following. I get that I have a new object with 3 properties (Count, Name, Group). So the "Group" is basically all the other fields I am importing, but all strung together into 1 field? 

Count Name                      Group
----- ----                      -----
  786 ="8MM", 8M-8MM Tapes,     {@{CustNum=AD1606; CustomerName=PHA; Media=="005161"; CreatedDate=08/05/2014; Status=A…
 1454 ="DLT", TD-DLT Tapes (in… {@{CustNum=AD1606; CustomerName=PHA; Media=="002979L6"; CreatedDate=10/25/2018; Status…

So are you saying I need to do a select on the Group field? And somehow extract out the individual fields within it (DateIn, which unfortunately isn't shown above), construct a new "Year" value, and then ... group it again?

Sorry if I'm being dense ...

 

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/642711ffecbc44f9867a161d453240db%40smithcons.com.

[Michael B. Smith]

The group is a collection of all the records that match the grouping criteria.

 

The first line says 786. So Group is a collection of 786 items (numbered 0 thru 785) containing each line of the CSV.

 

If you do this:

 

$IM_Inventory = Import-CSV -Path $InputFileName | SORT -PROPERTY LogicalVault, Media | Group-Object -Property LogicalVault, MT, Generation

$IM_Inventory[ 0 ].Group

 

You will get the list of the 786 records in the CSV file that matches the grouping criteria.

 

You access each record by an index.

 

$IM_Inventory[ 0 ].Group[ 0 ]

 

And it will contain the full CSV record at that index. Then you can just use the field name, same as any other time you use a CSV record.

 

$record = $IM_Inventory[ 0 ].Group[ 0 ]

 

$record.CustNum, $record.DateIn, etc. etc.

 

Just play with it. It’s pretty obvious.

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/CAHBr%2B%2BiGFyA4XhNKUZ4WuzU63b2p8OFJLwZ44YxYRLRn%3DP73-Q%40mail.gmail.com.

[Mike Leone]

On Fri, Dec 19, 2025 at 2:35 PM Michael B. Smith <mic...@smithcons.com> wrote:

The group is a collection of all the records that match the grouping criteria.

Oh, I see now, group is it's own collection! 

 

The first line says 786. So Group is a collection of 786 items (numbered 0 thru 785) containing each line of the CSV.

Kind of like a nested array.

 

If you do this:

 

$IM_Inventory = Import-CSV -Path $InputFileName | SORT -PROPERTY LogicalVault, Media | Group-Object -Property LogicalVault, MT, Generation

$IM_Inventory[ 0 ].Group

 

You will get the list of the 786 records in the CSV file that matches the grouping criteria.

 

You access each record by an index.

 

$IM_Inventory[ 0 ].Group[ 0 ]

 

And it will contain the full CSV record at that index. Then you can just use the field name, same as any other time you use a CSV record.

 

$record = $IM_Inventory[ 0 ].Group[ 0 ]

 

$record.CustNum, $record.DateIn, etc. etc.

 

Just play with it. It’s pretty obvious.

Now I think I got it. 

Thanks! Sorry for being dense.

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/ae0d176b6de94623a18cdd5c8dcbf36d%40smithcons.com.

[Mike Leone]

So I ended up (so far) with this:

$IM_Inventory_ALL = Import-CSV -Path $InputFileName | Select-Object *, @{Name="YearIn"; Expression={$_.DateIn.SubString(6,4)}}
$IM_Inventory_By_Date = $IM_Inventory_ALL | SORT -PROPERTY YearIn, LogicalVault, Media | Group-Object -Property YearIn, LogicalVault, MT, Generation

(I didn't realize you could effectively add new fields during an import-csv! Made my life a whole lot simpler)

Which gives me:

Count Name
----- ----
  776 2014, ="8MM", 8M-8MM Tapes,
 1396 2014, ="DLT", TD-DLT Tapes (in-case),
 7215 2014, ="LTO", LC-LTO Tapes (in-case),
    1 2014, ="LTO", LC-LTO Tapes (in-case), L4
   88 2014, ="LTO", LC-LTO Tapes (in-case), L6
  570 2014, ="REELS", RR-3420 Reels,
    3 2015, ="LTO", LC-LTO Tapes (in-case),
  721 2015, ="LTO", LC-LTO Tapes (in-case), L6
    6 2016, ="DLT", TD-DLT Tapes (in-case),
    7 2016, ="LTO", LC-LTO Tapes (in-case),
  748 2016, ="LTO", LC-LTO Tapes (in-case), L6
  767 2017, ="LTO", LC-LTO Tapes (in-case), L6
   10 2018, ="8MM", 8M-8MM Tapes,
   52 2018, ="DLT", TD-DLT Tapes (in-case),
   11 2018, ="LTO", LC-LTO Tapes (in-case),
  753 2018, ="LTO", LC-LTO Tapes (in-case), L6
    1 2019, ="LTO", LC-LTO Tapes (in-case),
  697 2019, ="LTO", LC-LTO Tapes (in-case), L6
    1 2020, ="LTO", LC-LTO Tapes (in-case),
  727 2020, ="LTO", LC-LTO Tapes (in-case), L6
  705 2021, ="LTO", LC-LTO Tapes (in-case), L6
  665 2022, ="LTO", LC-LTO Tapes (in-case), L6
    2 2023, ="LTO", LC-LTO Tapes (in-case),
  838 2023, ="LTO", LC-LTO Tapes (in-case), L6
  386 2024, ="LTO", LC-LTO Tapes (in-case), L6
  471 2024, ="LTO", LC-LTO Tapes (in-case), L7
   19 2025, ="LTO", LC-LTO Tapes (in-case),
  148 2025, ="LTO", LC-LTO Tapes (in-case), L6
  687 2025, ="LTO", LC-LTO Tapes (in-case), L7
    2 2025, ="TRAN", X2-X2 - TR MULTI-MEDIA DBL,
    2 2025, ="TRAN", XT-XT - MULTI-MEDIA TRANS,

Which is great ... IF I can get it to show me a summary count by year. I know I can do a 2nd Group-object command (just by YearIn), and output that,

$IM_Inventory_By_Year = $IM_Inventory_ALL | SORT -PROPERTY YearIn, LogicalVault, Media | Group-Object -Property YearIn
$IM_Inventory_By_Year | SELECT Count, Name

10046 2014
  724 2015
  761 2016
  767 2017
  826 2018
  698 2019
  728 2020
  705 2021
  665 2022
  840 2023
  857 2024
  858 2025

 but it would be ideal if I could get a count of each year, followed immediately by a line that shows the total count for the year. But I don't see any way of doing that.

i.e., 

  776 2014, ="8MM", 8M-8MM Tapes,
 1396 2014, ="DLT", TD-DLT Tapes (in-case),
 7215 2014, ="LTO", LC-LTO Tapes (in-case),
    1 2014, ="LTO", LC-LTO Tapes (in-case), L4
   88 2014, ="LTO", LC-LTO Tapes (in-case), L6
  570 2014, ="REELS", RR-3420 Reels,

Total for value 2014: 10046

Is there any way I can get that? I don't see how, it doesn't seem to be an option on the grouping. And I don't see how piping that through something like the Measure-object could do it, either.

If not, I'll just output the values in 2 stages.

Thanks!

[Michael B. Smith]

No, you need a report-writer for that.

 

Could you fake it? Sure.

 

Post-process $IM_Inventory_By_Date.

 

Do it just like you would in COBOL. 😊

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/CAHBr%2B%2Bj6fhNCN6E0X-VqO8wOBuF-jHEriENWP2FzKx6QWQGWnw%40mail.gmail.com.

[Kurt Buff]

Which version of COBOL? There are so many...

LOL

Kurt

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/72b51b1cfbf84390b61a1b62141d6e0f%40smithcons.com.

[Michael B. Smith]

I learned cobol-68 and cobol-74. None of those high-falutin’ modern thing-a-ma-bobs.

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/CADy1Ce6DwwY_%2BkAk0%2BbHXTBdaUFCdZ-QoGLBQua_oU8ghd_R7Q%40mail.gmail.com.

[Kurt Buff]

Is it Admiral Doctor Grace Hopper, or Doctor Admiral Grace Hopper?

Inquiring minds want to know.

Kurt

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/f290acc897c44103908813e83591993c%40smithcons.com.

[Michael B. Smith]

Just Grace Hopper.

 

She disdained all the various distinctions she was awarded. She claimed that her work as a teacher was the most important thing she ever did.

 

I do find it interesting that she was the first woman to receive the “Computer Science Man-of-the-Year Award”. 😊

To view this discussion visit https://groups.google.com/d/msgid/ntpowershell/CADy1Ce5iFURe%3DOfM8RrVt0hdizNCVjNwr%2BVEubG5cwt2-PvLWQ%40mail.gmail.com.