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!
Recent Comments