PowerShell Problem Solver: CSV Import Tricks and Tips
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. 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. 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 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.
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.
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. To bring this into PowerShell as a set of objects, use the ConvertFrom-CSV cmdlet, which also has Header parameter.
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.
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.