PowerShell Problem Solver: CSV Import Tricks and Tips

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

During a recent foray into a PowerShell forum, I came across a post from a user wanting to update a CSV file of computers with their IP addresses. Management strategies aside, this seemed like a great opportunity to explore how you can use CSV files in PowerShell. So I thought I’d walk through the process of updating a CSV file with IP address information using a number of techniques. This article is a kind of two-fer in that we’ll look at working with CSV files and resolving computer names to IP addresses.

To begin, I have a sample CSV file.

The great thing about PowerShell is that you can bring any CSV into PowerShell using Import-CSV. The cmdlet will take the column heading and use it as property names.

Importing a CSV file into PowerShell (Image Credit: Jeff Hicks)

Importing a CSV file into PowerShell (Image Credit: Jeff Hicks)

I created my CSV file so that some of the headings match cmdlet parameters I might want to use. Let me explain. One technique I could use to get an IPAddress s with the Test-Connection cmdlet.

Using PowerShell's Test-Connection cmdlet to see an IP address (Image Credit: Jeff Hicks)

Using PowerShell’s Test-Connection cmdlet to see an IP address (Image Credit: Jeff Hicks)

How does this relate to my CSV file? This is when you need to look at cmdlet help, specifically in this case at the Computername parameter.

The Test-Connection Computername parameter (Image Credit: Jeff Hicks)

The Test-Connection Computername parameter (Image Credit: Jeff Hicks)

Sponsored

The description says it can be a name. Good, because that is what I have in the CSV file. But here’s the cool part. Take a look at the ‘Accept pipeline input’ section. It has a value of True and an indication of ByPropertyName. This means the cmdlet will accept piped in objects. If the incoming object has a property name called Computername, PowerShell will take that value and bind it to the corresponding parameter. Let me do a quick proof of concept.

Testing parameter binding (Image Credit: Jeff Hicks)

Testing parameter binding (Image Credit: Jeff Hicks)

If you recall each imported object has a Computername property courtesy of the Computername column heading. Test-Connection sees it and hooks everything up for me. Let’s try it now for the entire CSV file.

Testing connections from imported objects (Image Credit: Jeff Hicks)

Testing connections from imported objects (Image Credit: Jeff Hicks)

Errors aside for offline or unknown computers, this works!

Before we get to the next step, what do you do if your CSV file doesn’t have a matching heading that you can use with parameter binding? The answer is not to edit the file. This is a situation where you will need to use ForEach-Object. Import the CSV and for each imported object, do something.

It works, but is a bit more cumbersome to type. If you have a process planned for your CSV that involves PowerShell cmdlets, the more you can setup your CSV ahead of time with the right column headings, the easier the import and processing.

If your CSV doesn’t have a header line, you can specify one at import:

Or, let’s say your CSV does have a header but you want to use something different and can’t really modify the source file. You can’t use Import-CSV with the Header parameter. Instead, you have to strip off the first line from the file.

Stripping off the header line from a CSV

Stripping off the header line from a CSV (Image Credit: Jeff Hicks)

To bring this into PowerShell as a set of objects, use the ConvertFrom-CSV cmdlet, which also has Header parameter.

Converting a CSV with an alternate header

Converting a CSV with an alternate header (Image Credit: Jeff Hicks)

Since my file is already using Computername, I used Name for the sake of demonstration. From here I could pipe to another cmdlet and take advantage of pipeline binding.

Fortunately, my CSV file is structured, so I don’t have to use any of these tricks.

In looking at the output of Test-Connection with Get-Member, I can identify the properties that interest me.

Selecting IP and Name information from Test-Connection

Selecting IP and Name information from Test-Connection (Image Credit: Jeff Hicks)

Sponsored

Excellent. I still have to deal with errors and update the CSV file with the IP address information I’m now getting. We’ll look at that in another article.

Sponsored

Tagged with , , , ,