Making Data Dance with PowerShell

 

I think what you’ll learn in this article is going to be fun. Because of PowerShell’s emphasis on working with objects, once you get your head around that idea, you’ll realize PowerShell is a terrific tool for managing just about anything. I use PowerShell for all sorts of what you might consider non-IT tasks from a simple task management system to a tickler program with popup reminders. These tools rely on data and PowerShell doesn’t really care where the data comes from. Once the data becomes a collection of objects, PowerShell makes it a breeze to work with. I want to spend some time demonstrating how you might work with data in PowerShell. As with much of my work, focus as much on the techniques and concepts as the end result. I like coming up with fun applications so you can learn something with a smile on your face. Let’s dig in.

To keep you from getting too distracted, let’s work with some non-technical data, which I’ll bring in from a CSV file. The techniques and concepts I want to demonstrate should apply to any data source so you should be able to apply them to your more technical data. I have a CSV file of movies that I think I’d like to see.

"Title","ReleaseDate","Comments","Rating"
"Star Trek Beyond","7/22/2016","must see","PG-13"
"Jason Bourne","7/29/2016",,"PG-13"
"Patient Zero","9/2/2016","Horror","NR"
"The Magnificent Seven","9/23/2016","PG-13"
"Doctor Strange","11/4/2016","Marvel","NR"
"Fantastic Beasts and Where to Find Them","11/18/2016","Harry Potter related","NR"
"Rogue One","12/16/2016","Star Wars","NR"
"The Dark Tower","2/17/2017","Stephen King","NR"
"Ghost in the Shell","3/31/2017","SciFi","NR"
"Spectral","8/12/2016","Supernatural thriller","PG-13"
"The Space Between Us","8/19/2016","space adventure","PG-13"
"Miss Peregrine's Home for Peculiar Children","9/30/2016","Tim Burton","PG-13"
"Arrival","11/11/2016","sci-fi","NR"
"Moana","11/25/2016","Disney animated","NR"
"Passengers","12/21/2016","sci-fi","NR"
"Assassin's Creed","12/23/2016",,"NR"
"Sing","12/23/2016","animated","NR"
"John Wick: Chapter Two","2/10/2017",,"NR"
"Wonder Woman","6/2/2017","comic book","NR"
"Justice League","11/17/2017","","NR"

This is a pretty simple file and I’m sure you’ve used files very similar to it. CSV files are terrific data sources because you can use Import-CSV and bring the data into PowerShell as objects.

$data = Import-CSV C:\scripts\moviedata.csv

Here’s what I have in $data.

That looks pretty straightforward. Although there are a few potential pitfalls. Notice what happens when I try to sort the data.
When you import data from a CSV file, PowerShell treats everything as a string. The means sorting on the ReleaseDate property doesn’t work as expected. One solution is to create a custom sort property.

$data | sort {$_.releaseDate -as [datetime]}

The underlying object hasn’t changed but at least the sort works correctly.
But if I want to do anything with that ReleaseDate property, I’m probably going to need to do something. We’ll get to that in a moment. In the meantime, let’s extend the imported data using Add-Member to define a new property.

$data | Add-Member -MemberType ScriptProperty -Name "OpensIn" -Value { [int32]((($this.ReleaseDate -as [DateTime]) - (Get-Date)).TotalDays)  }

Unlike using a custom hashtable with Select-Object, this creates a new property that will persist for as long as $data does. I created a special property called a ScriptProperty that will execute a scriptblock to generate the value. I’m calling the property OpensIn, and every time I access the property, PowerShell will calculate the value from subtracting the current date from the release date, as a [datetime] object and selecting the total number of days as an integer. You can see this new property with Get-Member.


And of course I can use this property in a PowerShell expression.

There is also another way to define custom properties. I’ll re-import the data from the CSV file to start all over. I now have a collection of PSCustomObjects in $data. But I can give them a custom type name.

$tname = "MyUpcoming"
$Data | foreach {$_.psobject.typenames.insert(0,$tname)}

You should see the new type name with Get-Member.

Why did I do this? Because now I can use another cmdlet, Update-TypeData, to define new properties for this type. The syntax is very similar to Add-Member.

Update-TypeData -TypeName $tname -MemberType ScriptProperty -MemberName "OpensIn" -Value { [int32]((($this.ReleaseDate -as [DateTime]) - (Get-Date)).TotalDays)  }
Update-TypeData -TypeName $tname -MemberType ScriptProperty -MemberName "Release" -Value { $this.ReleaseDate -as [DateTime]} -Force

Let’s check Get-Member again.

Now I can use these properties in a simple command.

$data | Where {$_.opensIn -ge 0 } | Sort OpensIn | Select Title,Release,OpensIn

I wanted to display movies that have not opened yet sorted by their release date, or more accurately, the OpensIn value.
Finally, because I’ve defined a custom type, I can also define a default set of properties. It doesn’t matter if the properties came from the original CSV import or I added them with Add-Member or Update-TypeData. I can tell PowerShell that whenever you see a MyUpcoming object, display these properties by default.

Update-TypeData -TypeName $tname -DefaultDisplayPropertySet "Title","ReleaseDate","OpensIn","Comments" -Force

The -Force parameter tells PowerShell to update even if I’ve run the command before. When I’m finished, PowerShell is even easier to use.

I wanted to display movies opening in the next 14 days but I need to ignore movies that have already opened. But notice that I didn’t need to do anything special to select properties. I already did that with the default display property set. But this is still a bit of a nuisance so before I let you go today, I’m going to add one more custom property to indicate if the movie has already been released.

Update-TypeData -TypeName $tname -MemberType ScriptProperty -MemberName "IsReleased" -Value {
 if ($this.OpensIn -lt 0) {
    $True
 }
 else {
    $False
 }
} -force

Now my PowerShell code is a little easier to understand and use.

As you are working with data in PowerShell, you need to ask yourself these types of questions. What does the data need to look like? How will it be used? Who will be using it and what will their expectations be? Feel free to grab a copy of the CSV data and try these things out for yourself. Then move on to your data and experiment. When you’re finished, come back here and we’ll continue looking at other was to make data sparkle in PowerShell.
Look for these other PowerShell articles on Petri IT Knowledgebase, Selecting Objects with PowerShell and Filtering PowerShell. The next article in this series is Dancing on the Table with PowerShell.