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!