Use a CSV File to Populate Parameters of PowerShell Cmdlets

Use a CSV File to Populate Parameters of PowerShell Cmdlets

  • Comments 3
  • Likes

Summary: Learn how to use a CSV file to populate parameters of Windows PowerShell cmdlets.

 

Hey, Scripting Guy! QuestionHey, Scripting Guy! I have a problem. I would like to be able to read content from a comma-separated value (CSV) file that contains a list of WMI classes and servers that I would like to query. I need to be able to read the CSV file, and based upon the data contained in that CSV file, I need to create a query on the fly and execute that query. I am afraid it is too complicated, but if I can do this, it will make things a whole lot easier for us at work. Do you know any secret tricks to help us out of our dilemma?

—SC

 

Hey, Scripting Guy! AnswerHello SC,

Microsoft Scripting Guy Ed Wilson here. Getting to go to different places and talk to people about Windows PowerShell is a lot of fun. However, there is a downside—expense reports. I wish there were a Windows PowerShell cmdlet called Get-Expense, and another one called New-ExpenseReport. If there were, I could use a command such as this one:

Get-Expense | New-ExpenseReport

That is the beauty of the Windows PowerShell pipeline. It looks at incoming properties, and seems to match them with similar items on the other side. Therefore, I can easily type the following:

Get-Process | Stop-Process

And the command works. This is a really cool example because the Get-Process default property is name and the default property for Stop-Process is id. The Windows PowerShell pipeline figures this stuff out. Cool.

If a cmdlet or advanced function does not accept piped input, all is not lost because you can always use the Foreach-Object cmdlet and manually map things inside that cmdlet’s scriptblock.

The key here is that when importing a CSV file via the Import-CSV cmdlet, each column becomes a property of an object. The other key here is that in a pipeline situation, $_ refers to the current item on the pipeline.

Using this as background, I want to look at a sample CSV file such as you described. The first column contains a WMI class name, and the second column is the computer name to use. The sample CSV file is shown in the following figure.

Image of sample CSV file

The following steps are required to read a CSV file and create a WMI query.

  1. Use the Import-CSV cmdlet to import the CSV file.
  2. Use the ForEach-Object cmdlet to walk through the imported data as it comes across the pipeline.
  3. Inside the script block of the Foreach-Object cmdlet, use the Get-WMIObject cmdlet to query WMI.
  4. Map the CSV column headings to the parameters of the Get-WmiObject cmdlet.
  5. Use the $_ variable to refer to the current items on the pipeline.

The command to read a CSV file named WMICSVFile.csv that resides in the C:\fso directory and to query WMI is shown here (in the following command, % is an alias for the Foreach-Object cmdlet, and gwmi is an alias for the Get-WmiObject cmdlet):

import-csv C:\fso\WMICSVFile.csv | % {gwmi $_.class -cn $_.cn}

The command to read a CSV file and automatically query WMI along with the associated output is shown in the following figure.

Image of command and associated output

 

SC, that is all there is to using a CSV file for input to other commands. This also concludes CSV Week. Join me tomorrow for the Weekend Scripter.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

 

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Ed,

    wonderful! At least an excellent point to start from, when we want to automate tasks that are based on data stored in files ... especially CSV files ...

    All the beauty and simplicity here is based on the automatic object and property generation that Import-Csv does for us. I really can't imagine that all these goodies come with powershell for free!

    So let's say: Thank you Microsoft and take it away for daily work!

    Klaus.

  • @Ed,

    Nice post.

    thanks

    aman

    www.amandhally.net/blog

  • @Klaus Schulte I am glad you liked the post. I was really excited about the post, because I knew the power of this technique and the way it can open up new areas in scripting. It is really cool. Thank you for your comment.

    @Aman I am glad you liked the post.