out-this, out-that

I’ve been doing a lot of work recently where I export data to a csv file and then open the file with Excel so I can convert it to a spreadsheet. But I’m inherently lazy so I went looking for an out-excel cmdlet or some such that would allow me to pipe data directly to Excel. I found code samples of how to start Excel and how to write to cells in a worksheet, but couldn’t find what I wanted, so I wrote an out-excel function.

The basic behavior I wanted was to be able to do something like this –
get-process | where {$_.handles -gt 500} | select name,handles,path | out-excel
and get an open workbook in Excel where the column headers were the names of the properties and where each row corresponded to an object in the pipeline.

I cobbled together some code which worked fine but then I wanted more. I knew that the convertto-html cmdlet has a -property  parameter which allows you to select the specific properties you want, so I decided to add that. Now I could do something like this –
get-process | where {$_.handles -gt 500} | out-excel -pr name,handles,path

I’ve been doing a lot of ad-hoc Active Directory searching and reporting, sometimes retrieving DirectoryEntry objects and sometimes only retrieving SearchResult objects. SearchResults are odd creatures that require way too many keystrokes since instead of typing something nice and short like –
get-searchresults -type user,lockedout -raw | out-excel name, userprincipalname
I would have to type something like this –
get-searchresults -type user,lockedout -raw | select @{n=”Name”;e={$_.properties.name}},
    @{n=”UPN”;e={$_.properties.userprincipalname}}| out-excelname

(That get-searchresults function is a tool I’ve built and added to over time to make my AD searching easier. Normally it returns DirectoryEntry objects, but the -raw switch lets it return the SearchResult objects directly. Saves time when I just need the results of the query and don’t actually need to do anything with the corresponding DirectoryEntry objects. I need to write about that sometime. But did I mention that I was lazy?)

So I added a -raw  switch. Now I can do this –
get-searchresults -type user,lockedout -raw | out-excelname -pr name,userprincipalname -raw

The function itself is relatively straightforward.

The begin  section starts Excel, creates a workbook and initializes  two variables.

In the process section, when the first object is encountered, we use the property list to write the property names in the first row. (If we weren’t provided a property list, we just build one based on the properties of the first object.) At the same time, we build a hash table so later we can look up a property name and find the corresponding column.

After this, we can process each object, enumerating the property names in our header table and writing the matching value from the object to the appropriate column. Using a hash table has the advantage that we don’t have to worry about missing properties or the “ordering” of the properties in the columns.

In the end  section, we just resize all the columns to fit the data. Now we have a spreadsheet open for further analysis in Excel or that we can save for later.

Here’s the function in its entirety – out-excel


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s