PowerShell Problem Solver Updating CSV Files

Posted on September 30, 2015 by Jeff Hicks in PowerShell with 0 Comments

In “PowerShell Problem Solver: CSV Import Tricks and Tips,” we started looking at the process of updating a CSV file with IP information. If you missed it, take a moment to get caught up, otherwise my examples might not make sense to you.

While you may not have the specific business need regarding this scenario, you most likely work with CSV files to some degree, and I’m assuming want to learn a bit more about PowerShell. In this article, I’m going to take the IP information from Test-Connection and insert it back into the CSV file.

I’m going to use the same CSV file as in the previous article.

First, let’s make sure I get the output I expect using the original column headings.

Creating new output (Image Credit: Jeff Hicks)

Creating new output (Image Credit: Jeff Hicks)

For the IP address, I’m using a custom hashtable to define a property called IPAddress. The value will be whatever is the result of the Expression scriptblock. That scriptblock runs Test-Connection using the current object’s Computername parameter and sending a single ping.

By wrapping that command in parentheses, I’m telling PowerShell to run that command first, which will result in an object. That object I already know from using Get-Member has an IPv4Address. You can put in as much code in the expression scriptblock as you need. You aren’t required to use a one-line approach. This will give you the same result and might be easier to follow.

In my output, you’ll notice I also no longer get any errors, instead the IPAddress is empty. If I try to use a bad computername I still get an error:

Test-Connection with an unknown name (Image Credit: Jeff Hicks)

Test-Connection with an unknown name (Image Credit: Jeff Hicks)

Sponsored

But when nestled inside the Expression scriptblock the error never rises to the surface, which for my purposes is just fine which is to update the CSV file. Now that I have the results, I can re-run the command export everything back into a CSV file.

In this case, I am overwriting the existing file. That’s why I imported the file first into the variable $computers. If I tried to Import, select and export all in one line I would most likely run into an open file error. Since I plan to use the CSV file outside of PowerShell I’m instructing Export-CSV to not include any type information.

Let’s consider a variation here. Suppose your original CSV has IP addresses for some computers, which we’ll further assume is correct. My CSV file is small, but yours might have hundreds of computers. It might make more sense to skip those with existing IP address information. I don’t want to filter them out because my goal is to end up with a revised file. Instead I can add some logic to the expression scriptblock.

I’m using a simple regular expression pattern for an IPv4 address. If the incoming object’s IPAddress matches, then I’ll use the existing value. Otherwise, I’ll use my Test-Connection code. For the sake of clarity, I inserted a Write-Host command so you could track the process.

Skipping objects with an existing IP property (Image Credit: Jeff Hicks)

Skipping objects with an existing IP property (Image Credit: Jeff Hicks)

I can re-run with the same Export-CSV expression as I used before to update the file.

Sponsored

Everything I’ve been showing you has been intended to update an existing file and in some aspects feels like all we’re doing is manipulating some text. But I like objects, which will lead us down another track that I’ll save for next time. If any of this is confusing or unclear, please post a comment.

Sponsored

Tagged with , , ,