Sort data using a custom list in PowerShell

5865108068_2d80d15834_m

Sometimes you might come across a situation where you’d like to sort a collection based on a custom list (similar to the feature available in Excel), rather than doing a basic sort based on the alphanumeric order of the property values using the Sort-Object cmdlet:

$numberWords="three","one","two","four"
$numberWords | sort

The above doesn’t really do the trick, if one would like to sort the collection based on the semantic order. For one dimensional arrays like the one used in the example above the System.Array type has an overload of the static sort method that can do this (kind of):

$numberWords="three","one","two","four"
$ranks=3,1,2,4
[Array]::Sort($ranks,$numberWords)
$numberWords

While this produces the correct order (changing the order of the items in-place), it does not really scale to a longer list, since it requires the list of ranks to be provided for each element of the array. Furthermore this wouldn’t work for collections of objects. The problem can be better approached by utilizing calculated properties for the Property parameter of the Sort-Object (see …

Get-Help sort -Parameter Property
Get-Help sort -Examples 

for more details):

$numberWords="three","one","two","four"
$numberWords=$numberWords*10
$customList="one","two","three","four"
$numberWords | sort { $customList.IndexOf($_) }

In the example above a scriptblock is used instead of a property Name for the Property parameter. Inside the scriptblock the ranks of the items within the collection ($numberWords) are determined by their position within the custom list ($customList) through the usage of the IndexOf() method. The same approach can also be used to sort collections of objects. Let’s try to sort processes by a custom list of process names:

$customList = 'iexplore', 'excel', 'notepad' 
Get-Process | sort {
	$rank=$customList.IndexOf($($_.Name.ToLower()))
	if($rank -ne -1){$rank}
	else{[System.Double]::PositiveInfinity}
},Name

In addition to the approach used in the previous example, here we are also dealing with property values that do not appear in the list. In this case the IndexOf() method returns -1, which would lead the object(s) to show at the top of the list, instead we assign a very high number for those values this makes the object(s) showing up at the bottom of the list. Adding the Name property as an additional Property parameter value produces the additional property values to be sorted alphabetically.
Let’s wrap this into a function for better re-usability:

function Sort-CustomList{
    param(
    [Parameter(ValueFromPipeline=$true)]$collection,
    [Parameter(Position=0)]$customList,
    [Parameter(Position=1)]$propertyName,
    [Parameter(Position=2)]$additionalProperty
    )
    $properties=,{
        $rank=$customList.IndexOf($($_."$propertyName".ToLower()))
        if($rank -ne -1){$rank}
        else{[System.Double]::PositiveInfinity}
    } 
    if ($additionalProperty){
        $properties+=$additionalProperty
    }
    $Input | sort $properties
}
$customList = 'iexplore', 'excel', 'notepad' 
Get-Process | Sort-CustomList $customList Name Name

Sort-CustomList can also be downloaded from my GitHub repository.

shareThoughts


Photo Credit: …-Wink-… via Compfight cc

Advertisements

Using the Excel intersection operator

tree
One of the lesser-known features of Excel is the intersection operator which can be used to simplify look-up operations. An intersection is the overlap of two or more cell ranges within excel. For instance:
In the screenshot below the ranges C1:C5 and B3:D3 (Cell ranges in Excel are written by using the range operator “:”) overlap in the cell C3.
intersection1
The intersection operator ” ” (a space) can be used to find the intersection of ranges. To find the intersection of the two ranges one can just use the following formula “=C1:C5 B3:D3”:
intersection2
Combining the intersection operator with named ranges yields to pretty intuitive look-ups in Excel. Let’s take up another example using monthly revenue data by region:
intersection3
Highlighting the table and pressing CTRL+SHIFT+F3 will bring up the “Create Names from Selection” dialog. We can go with the defaults (Top row, Left column) in order to create named ranges for each column and row within the table based on their labels:
intersection4
Now, in order to retrieve the March Results for the East region we can simply use “=March East”:
intersection5
Getting the Sum of the revenues from January-April for the West region is equally simple “=Sum(January:April West)”:
intersection6
Even non-consecutive ranges can be easily referred to. Pulling up the Sum of revenues for the month of January, March, and May for the South region is as easy as typing “=Sum((January,March,May) South)”:
intersection7

shareThoughts


Photo Credit: 1 brian via Compfight cc

Run SQL against Excel and Access through Access Database Engine with PowerShell – Part 1

tree
This is part 1 of a series of blog posts around using SQL against Excel and Access through ACE drivers (Access database engine) with PowerShell.
Part 1 is about introduction and getting up and running.

PowerShell has already some built-in support to slice and dice data via the select, where, and group cmdlets. If it comes to bigger data-sets and join operations though, it lacks performance and features.
Many of us (including me) have data sitting in either Excel or Access files rather than SQL server or similar more scalable DBMSs.
In case of Excel converting the file to .csv and subsequently pulling the data in using Import-CSV is of course a valid option for several use cases. On the other hand there is another (better) way that doesn’t require any conversion and enables reading and writing the data directly into Excel.
First we’ll need to download and install the Microsoft Access Database Engine 2010 Redistributable. The name is a bit misleading as this is a replacement of the older Jet and OLEDB drivers which can be used for older and newer Excel and Access files. The download offers a 32 and a 64 bit version where the bit size corresponds to the respective bit size of the used office version. In order to avoid the nitty gritty details and focus on the task I have created a module that is a modified and extended version of the ACE module Chad Miller created. Let’s download the module to into the user’s module path.

$folderPath = Join-Path -Path ([Environment]::GetFolderPath('MyDocuments')) -ChildPath WindowsPowerShell\Modules\ACE
mkdir $folderPath
$url = 'https://raw.githubusercontent.com/DBremen/PowerShellScripts/master/functions/ACE.psm1'
Invoke-WebRequest $url -OutFile "$folderPath\ACE.psm1"

This is everything needed to get started. See you hopefully in the next part to dive into some example queries.

shareThoughts


Photo Credit: Fountain_Head via Compfight cc