Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell and Hash Table Examples


By:   |   Last Updated: 2017-11-23   |   Comments   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools