PowerShell Problem Solver: Fun with CSV Imported Objects

Posted on October 7, 2015 by Jeff Hicks in PowerShell with 0 Comments

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.

Sponsored

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)

Sponsored

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)

Sponsored

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.

Sponsored

Tagged with , , ,