Does Your Hard Work Advance the Ecosystem?

PowerShell Team

The Lync PowerShell Blog recently posted a fairly chewy piece that described how to enable users in bulk when given an XLS file as input.

Jason Helmick responded, saying:

Instead of your incredible code that no admin would even begin to understand or even be able to write, let’s do the same thing, in one line. (Note: This is an educated PowerShell admin, or any typical Unix guy.)

Step 1: Admin receives xls from HR.  They then proceed to use Excel to convert it to CSV.  Total time, 10 seconds.

Step 2: They then type the following one-liner:

Import-CSV C:\Scripts\NewUsers.csv | %{Enable-CsUser -Identity $_.Name -RegistrarPool $_.RegistrarPool -SipAddress $_.SipAddress}

In fact, if Enable-CsUser supports ValueFromPipelineByPropertyName for the Name, RegistrarPool and SipAddress parameters, it would just come down to this:

Import-Csv c:\scripts\NewUsers.csv | Enable-Csuser

There are definitely a lot of people that program in PowerShell despite functionality that already exists. A perfect example is the C# developer that writes a complex directory recursion script against the .NET file APIs despite "dir -recurse" being right under their nose.

However, PowerShell doesn’t have anything built-in that lets you read XLS files. If you need to automate this scenario (another core promise of PowerShell), manually opening Excel to convert it to a CSV will be a non-starter. From that perspective, the Linc-PowerShell blog post genuinely moves the ball forward for admins everywhere.

One of our core beliefs is in the trickle-down effect. Now that somebody’s done the leg work of digging into an XLS spreadsheet, perhaps other admins will be able to! If they’re proficient at copy + paste, they can hopefully wedge this into their own scenarios.

What really works out well is when another enterprising admin gets stick of copying this code everywhere and writes a wrapper: Import-Xls. This function could do the same as Import-Csv, but use this COM object technique to extract the data. Then, they post it on poshcode.org, and the entire ecosystem gets more efficient. (As a side note, an alternative to dealing with Excel’s COM object is included in the PowerShell Cookbook, and given here: http://poshcode.org/2188)

At that point, dealing with XLS files again becomes a one-liner: Import-Xls c:\scripts\NewUsers.xls | Enable-CsUser.

When you write something great that advances the state of PowerShell, why not take the few moments to wrap it in a reusable function for the world to enjoy?

Lee Holmes [MSFT]
Windows PowerShell Development

PowerShell Team PowerShell Team, PowerShell

Follow