Quick tip: Cure Excel headaches with PowerShell

- select the contributor at the end of the page -
Sure I've been in IT for almost 15 years, but I'm (still) not a Microsoft Excel wizard. I recently had to retrieve some information from a 100-row spreadsheet. Not only did I have to scroll up and down continuously, but the columns were also of a variable size, so I often had to scroll horizontally and resize columns. It was a frustrating experience, and dare I say I was embarrassed that I needed to pull out a piece of paper and pen to scribble down some information.

Use PowerShell!

Then it dawned on me. Why don't I use PowerShell for this? PowerShell is awesome for working with CSV formatted files. Furthermore, if I use the import-CSV cmdlet, everything is magically transformed into an object, and the possibilities are almost unlimited for what I can do with the data.

My sample data

So, this is my sample data that I'm going to use. I'm just using a relatively small Excel spreadsheet, but that should be enough to help you understand where I'm going.

sample excel data

I'm going to be working with a few things like the location, OS, whether I'm dealing with a 32 or 64-bit system, and if the system is physical or virtual. I've added some other columns, but that's also just to demonstrate how to sort of skip over them when I work with the data.

Importing the data

Saving the Excel spreadsheet to a CSV is just as simple as resaving the file in CSV format, but the real magic happens when import-CSV is used to load the data. As I mentioned, the input data gets transformed into an object. As a result, I can use all kinds of built-in cmdlets to work with the data.

First off, I'm just going to import the data and save it to a variable. Then, I'm going to show the first five objects and format the output.

importing the data

I'm specifically using the Format-Table cmdlet; otherwise the presentation would have been a bit more difficult to view since PowerShell was seeing a relatively large number of columns. By default it was trying to present the data in a “list format” (not shown), while I wanted to see a “table format” as shown above.

I'm specifically using the Format-Table cmdlet; otherwise the presentation would have been a bit more difficult to view since PowerShell was seeing a relatively large number of columns. By default it was trying to present the data in a “list format” (not shown), while I wanted to see a “table format” as shown above.

Working with the data

Now, let's run through a few typical scenarios and then show the code:

  • Scenario 1: I know my data isn't complete. Show me the hostname and rack for systems where I don't have a location listed.

  • Scenario 2: I need to move my servers in location “loc2”, list the number of servers from that location, and the rack.

  • Scenario 3: Similarly to scenario 2, notice that I have virtual machines listed in some racks; these must be in a physical host; show me the listing of physical and virtual servers in location “loc1”.

  • Scenario 4: My network team needs to make some network changes and want a list of hosts by VLAN.

  • Scenario 5: I want a breakdown of servers by OS and 32/64-bit.

The full code is shown at the end of this article. In the screenshots below, I've used aliases and other shortcuts to try to shorten the code quite a bit.

Below, I'm showing the code for the first three scenarios. I'm not going to walk through each code example, but by matching the scenarios listed above, I hope readers can follow the logic.

Scenario 3

I will briefly talk about scenario 3. I might have been able to go through some more complicated logic to try to better represent the data, but I chose instead to sort of group the “rack” and “physical/virtual” property together as shown.

server listing

Full code:

$data|where-object{$_.location -eq "loc1"}|select-object -property location,rack,'phy/virt'|group-object -property rack,'phy/virt'|select-object -property name,count|format-table -autosize

Scenario 4:

Below, I show my first attempt to script scenario 4, which is a bit difficult to read, so I used “calculated properties” in my second try to present the information in a slightly more readable format.

calculated properties

Full code:

$data|select-object -property server,vlan|group-object -property vlan

Other parts of the Office Suite

There are also opportunities to work with other products in the Office Suite, like Word. Be on the lookout for more examples of working with some of the other products in the suite in a future post.

Scenario 1 code:

$data|where-object{$_.location -eq ""}|format-table -autosize

Scenario 2 code:

$data|where-object{$_.location -eq "loc2"}|select-object -property location,rack|group-object -property rack|select-object -property name,count|format-table -autosize

Scenario 5 code:

$data|select-object -property os,'32/64'|group-object -property os,'32/64'|select-object -property name,count|sort-object -property name|format-table -autosize

Ready to test your skills in Powershell? See how they stack up with this assessment from Smarterer. Start this Powershell test now.

Get our content first. In your inbox.

Loading form...

If this message remains, it may be due to cookies being disabled or to an ad blocker.

Contributor

Marco Shaw

Marco Shaw is an IT consultant working in Canada. He has been working in the IT industry for over 12 years. He was awarded the Microsoft MVP award for his contributions to the Windows PowerShell community for 5 consecutive years (2007-2011). He has co-authored a book on Windows PowerShell, contributed to Microsoft Press and Microsoft TechNet magazine, and also contributed chapters for other books such as Microsoft System Center Operations Manager and Microsoft SQL Server. He has spoken at Microsoft TechDays in Canada and at TechMentor in the United States. He currently holds the GIAC GSEC and RHCE certifications, and is actively working on others.