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.
- Related: PowerShell Problem Solver: CSV Import Tricks and Tips
- Related: PowerShell Problem Solver Updating CSV Files
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. 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. Now look at what we have in $computers. An added benefit is that I can also update individual objects.
Here you can see the before and after. 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. 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: 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. 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. 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. 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.
Devolutions Remote Desktop Manager
Devolutions RDM centralizes all remote connections on a single platform that is securely shared between users and across the entire team. With support for hundreds of integrated technologies — including multiple protocols and VPNs — along with built-in enterprise-grade password management tools, global and granular-level access controls, and robust mobile apps to complement desktop clients.