PowerShell and Hash Table Examples


By:   |   Updated: 2017-11-23   |   Comments   |   Related: More > PowerShell


Problem

In this tip, we'll work through some examples with PowerShell - from basic CRUD operations to organizing and aggregating data with hash tables. We'll also generate a few errors intentionally to understand the basic structure and limitations. We'll also look at the dynamic nature of the data in both the keys and the values and how this can be helpful for building configurations or processing in steps when those steps may change.

Solution

A hash table provides a dictionary with a key-value pair, where we input a distinct key with an associated value and can return the result as a two column table, the first column being the name and the second column being the value. In our first exercise using PowerShell ISE, we'll perform basic CRUD operations with a hash table: adding values, changing a value, removing a value, getting a value, and finally adding properties from system variables to a hash table.

The code and output are shown below.

Write-Host "

Example One - Add (Insert):

"
$newhastable1 = @{}
$newhastable1.Add("KeyOne","ValueOne")
$newhastable1.Add("KeyTwo","ValueTwo")
$newhastable1
Write-Host "

Example Two - Change (Update):

"
$newhastable2 = @{}
$newhastable2.Add("KeyOne","ValueOne")
$newhastable2.Add("KeyTwo","ValueTwo")
$newhastable2.Set_Item("KeyTwo","The quick brown fox jumped over the lazy dogs")
$newhastable2
Write-Host "

Example Three - Remove (Delete):

"
$newhastable3 = @{}
$newhastable3.Add("KeyOne","ValueOne")
$newhastable3.Add("KeyTwo","ValueTwo")
$newhastable3.Remove("KeyOne")
$newhastable3
Write-Host "

Example Four - Get (Select):

"
$newhastable4 = @{}
$newhastable4.Add("KeyOne","Data points in one.")
$newhastable4.Add("KeyTwo","Data points in two.")
$newhastable4["KeyOne"]
Write-Host "

Example Five - Add Properties

"
$versionhash = @{}
$versionhash.Add("OS Version:",[System.Environment]::OSVersion.VersionString)
$versionhash.Add("PowerShell Version:",$PSVersionTable.PSVersion.Major)
$versionhash
Example set 1 - CRUD

Hastable names must be unique; for an example, if we try to add KeyOne twice to our hash table, it will fail with the message Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: 'KeyOne' Key being added: 'KeyOne'.

$newhastablefail = @{}
$newhastablefail.Add("KeyOne","ValueOne")
$newhastablefail.Add("KeyOne","ValueFail")

$newhastablefail
Example fail same key

As long as the key is different, we won't get an error. For an example, we can have different names with the same value:

$newhastablesamevalue = @{}
$newhastablesamevalue.Add("KeyOne","ValueOne")
$newhastablesamevalue.Add("KeyTwo","ValueOne")

$newhastablesamevalue
Example fail same key

If we tried the same with integers, it would also fail - we input 1 twice as a key:

$newhastablefail = @{}
$newhastablefail.Add(1,"ValueOne")
$newhastablefail.Add(1,"ValueFail")

$newhastablefail

Neither the keys nor the values must be of the same type either, since a hash table is a dictionary, as we see when we divide with the number values or apply regex to the strings:

$newhastablemixed = @{}
$newhastablemixed.Add(1,25)
$newhastablemixed.Add("a","Collies")
$newhastablemixed.Add((Get-Date).Date,"Today is ")

### Equals 5
$newhastablemixed[1]/5

### Matches
$newhastablemixed["a"] -match "oll"
Example mixed keys and values

This provides us with flexibility, since we may need different types of values when completing steps, adding or editing configurations, or migrating data. For an example in practice, we add four values to a hash table and output them to a configuration file - the below uses the key for the configs XML tags and the values for the values within the tags:

$newhashtableconfig = @{}
$newhashtableconfig.Add("Symbols",30)
$newhashtableconfig.Add("RefreshIntervalSeconds","1")
$newhashtableconfig.Add("TickerDate",(Get-Date).AddDays(-4))
$newhashtableconfig.Add("Message","All stories may be delayed five minutes.")


foreach ($key in $newhashtableconfig.Keys)
{
    $info = "<$key>" + $newhashtableconfig[$key] + "</$key>"
    Add-Content "E:\Sites\Ticker\ticker.confg" $info
}
Example config

In our next example, we'll use an array of lower case alphabet characters, and iterate over a sentence string, saving each letter found in the sentence string to the hash table, while keeping count of each character. For an example, in our sentence, "Put it in miscelaneous", it will save the letter i as the name and the count of 3 as the value, since there are 3 in the sentence.

$letters = @("a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","t","u","v","w","x","y","z")
$sentence = "Put it in miscelaneous."
$letterTable = @{}


foreach ($eachletter in $sentence.ToCharArray())
{
    [string]$eachletterstring = $eachletter
    foreach ($letter in $letters) 
    { 
        if ($letter -eq $eachletterstring.ToLower()) 
        { 
            if ($letterTable.ContainsKey($letter))
            { 
                [int]$currentcount = $letterTable[$letter]
                $letterTable.Set_Item($letter,($currentcount + 1)) 
            } 
            else 
            { 
                $letterTable.Add($letter,1) 
            }
        }
    }
}


$letterTable
Example adding words and counts

By using the method ContainsKey, we can prevent adding a duplicate key - which would throw an error. If a key exists, the method returns true and if not, the method returns false:

$newhastablecontains = @{}
$newhastablecontains.Add(1,2)
$newhastablecontains.Add(2,4)
$newhastablecontains.Add(3,8)
$newhastablecontains.Add(4,16)

$newhastablecontains.ContainsKey(1)
$newhastablecontains.ContainsKey(3)
$newhastablecontains.ContainsKey(10)
Example contains

We can also use hash tables in steps - such as the below example where we check whether a path exists, get all the files by an extension, and validate if a file exists by a date. In practice, these values could change by configuration file or table and depending on how many steps are involved, allow for easier debugging, as in that scenario we could call the hash table, see the steps under the name, and the variables for those steps under value. The alternative would be to search through the script, which may be fast or slow depending on the length.

$steptable = @{}
$steptable.Add(1,"C:\ETLFiles\")
$steptable.Add(2,"*.txt")
$steptable.Add(3,((Get-Date).Year.ToString() + (Get-Date).Month.ToString()))


if (Test-Path $steptable[1])
{
    $files = Get-ChildItem -Path $steptable[1] -Filter $steptable[2]
    
    foreach ($file in $files)
    {
        if ($file.BaseName -match $steptable[3])
        {
            $file.FullName
        }
    }
}
Example - steps
Next Steps
  • After going through the exercises, create and manipulate other hash tables by using the CRUD operations.
  • From configurations with dynamic values to manipulating data with key-value pairs, hash tables can offer a useful tool for faster development and data manipulation.


Last Updated: 2017-11-23


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools