Tip: Using Excel To Speed Up Your PowerShell Scripting On Exchange

If you’ve run across a scenario where you need to create/edit/remove more than a few Exchange objects at a time, your best bet is to script the fix using PowerShell.  The biggest problem you’ll come across though is, “I have this list of users, so how can I pre-populate a PowerShell script with the correct variables?”

Here’s the quick-and-dirty method:

1) Fire up Excel

2) Arrange your columns in such a way that you can simply paste in columns from text files or other spreadsheets with the correct first name and last name values—as well as any other variables you might need—and have those columns fit into the general syntax of what you’re trying to script… here’s an example:

   

    As we can see above, I’m running the following command for each user on an individual line:

    New-MailContact –Name “Billy Bob (Mozambique)” –ExternalEMailAddress billybob@somewhere.mz

    I can also easily paste in the list of first & last names, as well as copy the standard contents of columns A and E all the way down to the end, as shown below:

   

    You can even get fancy with the cell values & tabs… by way of example, I’ve broken down my ultimate goal into three discrete steps, each of which is its own tab:

   

    …and on the additional tabs, just set the values for the First Name and Last Name columns based on the value in the first tab using a formula like the xample below:

   

3) With your spreadsheet complete, do a Ctrl-A (Select All) and paste the contents into Notepad:

   

4) Highlight one of the tab characters and hit Ctrl-C (or right-click and Copy):

   

5) On the Edit menu, go to Replace:

   

6) Paste in the tab character you copied earlier and click the Replace All button:

   

7) Now that the text document is correctly formatted and doesn’t have any additional spaces or tabs, you should just be able to paste it into PowerShell on the Exchange server directly:

   

Tip: I’d recommend only pasting in a few lines at a time to start with just to make sure your syntax is correct.

Hope this saves you some time!  :-)

views