Import CSV files in Powershell

If you work a lot with powershell and different Microsoft products then you will run into importing CSV files. Importing CSV files, can be a cumbersome task in Powershell. So here are a few tips and tricks. 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)

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)

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

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)

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

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *