
close
close
Chance to win $250 in Petri 2023 Audience Survey
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.
$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.
$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.
$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.
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.
$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.
More in PowerShell
Most popular on petri