PowerShell Problem Solver: Fun with CSV Imported Objects

The last several PowerShell Problem Solver articles have sprung from a need to update a CSV file that contains computer information with IP addresses. I’ve been using this scenario to demonstrate a number of PowerShell commands like Import-CSV, Export-CSV and Test-Connection.


In this article, we’re going to pick up where we left off, and that includes exploring how to think about this process with objects instead of text in a file. As with many of my article series, take a few minutes to get caught up on the previous articles if you’re just joining us.
Today, I’m going to use a copy of my computers.csv file that has some IP information already entered, and I know the addresses are valid.

​
I'm going to import this CSV into my PowerShell session.
​
You should know by now that $computers is a collection of custom objects.
Displaying an imported object (Image Credit: Jeff Hicks)
Displaying an imported object (Image Credit: Jeff Hicks)
Some of these objects will have an empty IPAddress property. As I showed last time, I can use a regular expression pattern to skip objects with an IP address and use Test-Connection to get the IP address for those that are lacking.
​

I inserted a Write-Host command because you wouldn't know what is happening, otherwise.
Updating objects with an IP address (Image Credit: Jeff Hicks)
Updating objects with an IP address (Image Credit: Jeff Hicks)
Now look at what we have in $computers.
Updated objects (Image Credit: Jeff Hicks)
Updated objects (Image Credit: Jeff Hicks)
An added benefit is that I can also update individual objects.
​
Here you can see the before and after.
Updating a single object (Image Credit: Jeff Hicks)
Updating a single object (Image Credit: Jeff Hicks)
Or perhaps I want to normalize some of the property values. I like computer names to be in upper case. They are not in my CSV file and imported objects. But that is easy to correct.
​
I can display the objects in $computers using the PowerShell cmdlets you already know.
Displaying sorted and formatted results. (Image Credit: Jeff Hicks)
Displaying sorted and formatted results. (Image Credit: Jeff Hicks)
When I'm ready, I can export whatever results I want CSV files.
​
When you are working in PowerShell, you should always be thinking about how you can use and re-use what you have. Once $computers has all the data I want, I easily created three different CSV files from the same information. I could also have exported $computers back to the original file, overwriting it with updated information.
Given everything I've shown you, there are a few caveats. There is an assumption that each server only has a single IP address. If that is not the case, then exporting to a CSV will be problematic. Suppose one of the computer entries looks like this:
A multi-value property (Image Credit: Jeff Hicks)
A multi-value property (Image Credit: Jeff Hicks)
The IPAddress property is an array or collection. When I attempt to turn $computers into a CSV file, I'm going to get an unexpected result.
An exported CSV with a multiple value property (Image Credit: Jeff Hicks)
An exported CSV with a multiple value property (Image Credit: Jeff Hicks)
I used ConvertTo-CSV to make it easier to demonstrate. The result would be the same using Export-CSV. If you have many properties like this, then a CSV file is the wrong format and you should switch to XML, which will preserve everything for you.
Exporting to an XML format (Image Credit: Jeff Hicks)
Exporting to an XML format (Image Credit: Jeff Hicks)
Otherwise, your option is to create a single string for all the IP addresses like this:
​
Now when you convert or export to CSV, you don't lose anything.
Joining multiple values together into a single string (Image Credit: Jeff Hicks)
Joining multiple values together into a single string (Image Credit: Jeff Hicks)
The other potential issue is that the computer must be configured to respond to a ping, otherwise Test-Connection will fail, even if the computer is up and running. But there are a few other name resolution tricks you could use and I'll cover those in a wrap-up article to this scenario.