Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET:IT Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET

PowerShell Problem Solver Updating CSV Files

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)
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. 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 Content

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

Related Topics:


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (0)

Leave a Reply

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: