Dancing on the Table with PowerShell

In the first article of this series we looked at a variety of ways of working with data in PowerShell. PowerShell doesn’t really care where the data comes from or what it looks like. Once you have turned the data into objects, your options are practically unlimited. As I mentioned at the end of the previous article, which you should read first if you haven’t already, it is very important to think about who will use your data and how they will consume it. To that end, in this article I want to demonstrate another technique for presenting data in PowerShell. We’ll even keep this fun and non-technical and use my CSV file of movie information.

Here’s an updated version of that file.

"Title","ReleaseDate","Comments","Rating"
"Jason Bourne","7/29/2016",,"PG-13"
"The Magnificent Seven","9/23/2016","","PG-13"
"Doctor Strange","11/4/2016","Marvel","PS-13"
"Fantastic Beasts and Where to Find Them","11/18/2016","Harry Potter related","PG-13"
"Rogue One","12/16/2016","Star Wars","PG-13"
"The Dark Tower","2/17/2017","Stephen King","R"
"Ghost in the Shell","3/31/2017","Sci-Fi","R"
"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","R"
"Moana","11/25/2016","Disney animated","G"
"Passengers","12/21/2016","sci-fi","PG-13"
"Assassin's Creed","12/23/2016",,"R"
"Sing","12/23/2016","animated","PG"
"John Wick: Chapter Two","2/10/2017",,"R"
"Wonder Woman","6/2/2017","comic book","PG-13"
"Justice League","11/17/2017","","PG-13"
"Transformation","10/7/2016","Syracuse film","R"
"Night of the Living Dead: Genesis","12/30/2016","horror","R"

As before, the first step is to convert this data into objects using Import-Csv.

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

The CSV format is the easiest to use. If you need to use a different delimiter, be sure to read help and examples for Import-Csv. I suppose I should also mention that everything I’m showing you works because the data is structured and predictable. Even if some values might be missing, I can at least tell what would fill in those blanks. Now, what are we going to do with this data? How about a table? And I don’t mean something created with Format-Table. When you use any of the format cmdlets there’s nothing else you can do other than send the output to a printer or text file.
Instead, I’m going to create a table object like you would use with a database.

$table = New-Object System.Data.DataTable

You might think that because this is a new type of object to pipe it to Get-Member to discover more.

Sadly, there’s nothing to look at yet so you’ll have to trust me to show you what to do. The first thing we need to do is define a set of columns. Each column will correspond to an object property. You should define the columns in the order you want them displayed.

$table.Columns.Add("Title","string")

The Add() method I’m using takes a parameter for the column heading (“Title”) and one for the object type expected in the column. This will have the added bonus of converting the assigned value to that type.

The method writes a column object to the pipeline. If I had wanted to, I could have saved the command to a variable and then modified any of these parameters. Perhaps making the column ReadOnly.

$c = $table.Columns.Add("Title","string")
$c.ReadOnly = $True
$c.Unique = $True

Note that I recreated the $Table object so I could recreate the Title column. But now I’ve fine-tuned the column.

The rest of the columns won’t require anything extra so I can add them and pipe to Out-Null to suppress the column object output.

$table.Columns.Add("ReleaseDate","datetime") | Out-Null
$table.Columns.Add("OpensIn","int32") | Out-Null
$table.Columns.Add("Comments","string") | Out-Null
$table.Columns.Add("Rating","string") | Out-Null
$table.Columns.add("Released","boolean") | Out-Null

Note that not only am I defining columns for data in the CSV file, I’m also adding the custom properties that I created in the first article. And even though there’s still no content in $table so Get-Member still fails, the PowerShell ISE can still detect the properties and methods.

Let’s add some data to the table.
To accomplish this I need to add a row object for each piece of data I want to add. The Row object will inherit the column headings as properties. I can then assign a value to each Row property from either my data or anything else I want to calculate or derive from PowerShell.

foreach ($item in $data) {
    #define a new row object
    $r = $table.NewRow()
    #set the properties of each row from the data
    $r.Title = $item.Title
    $r.ReleaseDate = $item.ReleaseDate
    $r.OpensIn = ($r.ReleaseDate - (Get-Date)).TotalDays
    $r.Comments = $item.Comments
    $r.Rating = $item.Rating
    $r.Released = if ($r.OpensIn -lt 0) {
        $True
        }
        else {
        $False
        }
    #add the row to the table
    $table.Rows.Add($r)
    } #foreach

Finally, $table has something that Get-Member can see.

Do you see that the Title property is only a “get”? Remember, I made it read-only. All that remains is to display the table.

Wait a minute. I thought I created a DataTable? Well I did, but that doesn’t have anything to do with formatting. Because each row or object has more than 5 properties, PowerShell defaults to a list. If I want a table I have to ask for it.

The great thing about the DataTable, which is really just another type of custom object, is that i can use it like I would any other object in PowerShell.


If you want to modify a table entry you need to select it using Where-Object or the Where() method.

$m = $table.where({$_.title -eq 'Justice League'})

Then you can modify it. The tricky part to remember is that $m is technically a collection of DataRow objects, even though there is only one.

$m[0].comments = "DC Comics"

Or I could have modified it like this:

$table.where({$_.title -eq 'Justice League'}).foreach({$_.Comments = "DC Universe"})

In any event, this automatically modifies the row in the table.

If I were to use this technique around a data source, I would most likely create a set of functions to make it easier to manipulate the table and package the whole thing as a module. You don’t want to force the user of your script or PowerShell tool to try and figure out how to use the data.

Using a DataTable object doesn’t necessarily bring anything different to the party than the techniques I showed in the first article. Although depending on your data and what you need to do with it, it might be a viable option. I at least wanted to know it was available. And speaking of options, next time we’ll look at another way of presenting data in PowerShell that I think you will find very appealing.