Does Your Hard Work Advance the Ecosystem?

Does Your Hard Work Advance the Ecosystem?

Rate This
  • Comments 7

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

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
  • Hi,

    The most diffcult thing in powershell is that you can write it several ways and hardest part is figure out which way to write it.

    J

  • I think an Import-Xls function is a great idea. Jason makes a great point that nobody, even IT people, send files in csv format. As long as we're leaning on Excel to do the heavy lifting, I suggest using the "SaveAs" method.

    $excel = New-Object -ComObject Excel.Application

    $excel.DisplayAlerts = $false

    $excel.Workbooks.Open("c:\Import.xlsx")

    $excel.ActiveWorkbook.SaveAs("c:\Export.csv", 6)

    $excel.Workbooks.Close()

    $csv = Import-Csv "C:\Export.csv"

  • Yet-Another-Way-To-Import-Excel...

    COM is great for 10s maybe 100s of users, but beyond that, it may be slower than one might hope.  If speed is an issue, there's some unofficial projects that bring OpenXML support to PowerShell.

  • I agree...having an import-xls file would be great. Two quick questions:

    1. At Microsoft, do application teams (excluding the server teams such as Exchange, DPM, SQL) have any buy-in to PowerShell? It would seem like a useful function such as import-xls might be something the Excel team itself might release to further adoption of xls over the csv files most of us use now. Having the ability to parse Word, or integrate PowerShell with OneNote sections/notebooks, would be hugely useful.

    2. To the audience at large: does anyone know if there are plans for the next service pack or version of Windows Phone 7 to include PS support? If I had remote powershell on my phone I could manage a great number of the servers I remote into now from my desktop/laptop.

  • Does anyone know if Windows Phone 7's new service pack (or next release) will bake in PowerShell support? That'd be a huge reason for me to switch to that platform...being able to administer servers via command line would be great from the road.

  • It would be even better if someone wrote a cmdlet/module for using

    - openxml sdk 2 (www.microsoft.com/.../details.aspx)

    and/or

    - extrememl (extrememl.codeplex.com/.../60658).

    That way a user wouldn't have to have excel installed on a computer to read/write/manipulate office documents.

  • Oh hey, I found this article after writing the functions. They're rough...really rough, because I was doing this on the fly in oen night as a dare. Anyway, here is the linke:

    gallery.technet.microsoft.com/.../7b2fdc8f-a528-4f19-b9ef-f5af349dc906

Page 1 of 1 (7 items)