Making Data Dance with PowerShell

Posted on August 31, 2016 by Jeff Hicks in PowerShell with 0 Comments

 

PowerShell-and-Data

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.

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.

Here’s what I have in $data.

Imported data in PowerShell

Imported data in PowerShell (Image Credit: Jeff Hicks)

That looks pretty straightforward. Although there are a few potential pitfalls. Notice what happens when I try to sort the data.

Sorting data attempt

Sorting data attempt (Image Credit: Jeff Hicks)

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.

The underlying object hasn’t changed but at least the sort works correctly.

Sorting data with a custom property

Sorting data with a custom property (Image Credit: Jeff Hicks)

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.

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.

Verifying the new property

Verifying the new property (Image Credit: Jeff Hicks)

Sponsored

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

Using the custom property

Using the custom property (Image Credit: Jeff Hicks)

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.

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

Verifying the new type name

Verifying the new type name (Image Credit: Jeff Hicks)

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.

Let’s check Get-Member again.

New type properties

New type properties (Image Credit: Jeff Hicks)

Now I can use these properties in a simple command.

I wanted to display movies that have not opened yet sorted by their release date, or more accurately, the OpensIn value.

Testing the custom property

Testing the custom property (Image Credit: Jeff Hicks)

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.

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.

Using the new properties in a PowerShell expression

Using the new properties in a PowerShell expression (Image Credit: Jeff Hicks)

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.

Sponsored

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

Testing the custom properties

Testing the custom properties (Image Credit: Jeff Hicks)

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 KnowledgebaseSelecting Objects with PowerShell and Filtering PowerShell. The next article in this series is Dancing on the Table with PowerShell.

Sponsored

Tagged with , , , , , ,