Subtitle: In one line

The other day at work, I had a file of a bunch of activities and the dates on which they occurred.  Something like this:

What I wanted to do was to get a count of the number of activities occurring on each day, so I fired up a PowerShell command shell and ran the following one-liner:



1
ipcsv "C:\my_path\test_data.csv"|group event_date|select Name, Count

That line produced these results:



1
2
3
4
5
6
7
8
Name       Count

----       -----

8/23/2018     10

9/19/2018      8

7/3/2018      18

10/13/2018    15

11/2/2018      9

10/1/2018     15

Nice.  I have my counts, but it’d be even better if my dates were in order.  Let’s try to sort those suckers:



1
ipcsv "C:\my_path\test_data.csv"|sort event_date|group event_date|select Name, Count

That produced:



1
2
3
4
5
6
7
8
Name       Count

----       -----

10/1/2018     15

10/13/2018    15

11/2/2018      9

7/3/2018      18

8/23/2018     10

9/19/2018      8

Not the kind of sorting I was looking for.  It sorted the dates like they were strings.  Wait a minute…



1
2
3
4
5
PS C:\WINDOWS\system32> (ipcsv "C:\my_path\test_data.csv")[0].event_date.GetType()



IsPublic IsSerial Name                                     BaseType                                                                                                                           

-------- -------- ----                                     --------                                                                                                                           

True     True     String                                   System.Object

Just as I suspected: PowerShell is importing those dates as strings!  If I want to sort my results by date, I’ll have to cast that date to a proper datetime data type first.  Here’s what I came up with:



1
ipcsv "C:\my_path\test_data.csv"|select -Property @{Name="event_dt"; Expression={[datetime]$_.event_date}}|sort event_dt|group event_dt|select Name, Count

And the results:



1
2
3
4
5
6
7
8
Name                   Count

----                   -----

7/3/2018 12:00:00 AM      18

8/23/2018 12:00:00 AM     10

9/19/2018 12:00:00 AM      8

10/1/2018 12:00:00 AM     15

10/13/2018 12:00:00 AM    15

11/2/2018 12:00:00 AM      9

Nice!  I generated my counts and sorted by date all in one line of code.  Sweet!