PowerShell Arrays and Hash Tables–#SQLNewblogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was watching the GroupBy talk the other day and noticed that Cláudio Silva was using arrays, or what appeared to be arrays, in his talk. That was an interesting technique, one that I haven’t used very much.

A day later, I ran into an explanation on dbatools.io, that showed this code:

PS C:\> $columns = @{
>> Text = 'FirstName'
>> Number = 'PhoneNumber' 
>> }

That didn’t quite seem like what I wanted, so I decided to investigate more.

I looked up PowerShell Arrays, and that wasn’t what I wanted. These are a list of values, as in

$a = 1, 2,3

Which gives me this:


Useful, but not for my purposes. I need to map things together, which means a hash table.

Hash Tables

It turns out I need a hash table. This is a key value pair that lets me pick a name and value and store them together. The way I construct these are with the @{} structure. Inside here, I set semi-colon separated pairs, with the name=value syntax.

Here’s an example I used:

$ColList = @{Date="EventDate"; Event="Event"}

In here I map two keys (Date and Event) to two values (EventDate and Event). For the cmdlet I am using, this allows me to map these two columns together. When I need a value, I can use the $variable.key to get the value back.

2020-10-28 13_37_45-C__Users_Steve

I assume this is what the SqlBulkCopy cmdlet uses, which is what dbatools wraps. I ended up passing this $ColList hash table in for the –ColumnMap parameter.


A quick writeup that I used to solve a problem. I had some issues figuring this out, and some searching and experimenting got me a little better understanding of what was happening.

After about 30 minutes of some work, I took 10 minutes to type this up and explain it to myself. A good example of what you could add to your blog, showing how you use this in your work.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

3 Responses to PowerShell Arrays and Hash Tables–#SQLNewblogger

  1. brianary says:

    If you want to preserve the order:

    PS C:\> $ColList = [ordered]@{Date=”EventDate”; Event=”Event”}
    PS C:\> $ColList |Format-Table -AutoSize

    Name Value
    —- —–
    Date EventDate
    Event Event

    If you want to create an object with properties instead of a hash, since it serializes better:

    PS C:\> $ColProps = New-Object PSCustomObject -Property @{Date=”EventDate”; Event=”Event”}
    PS C:\> $ColProps |Format-Table -AutoSize

    Date Event
    —- —–
    EventDate Event

    These do the same thing as New-Object, but are simpler:

    PS C:\> $ColProps = [pscustomobject]@{Date=”EventDate”; Event=”Event”}
    PS C:\> $ColProps = [pscustomobject]$ColList


  2. way0utwest says:

    Thanks, that’s great to know. I’ve just started to use these, so good to know I can order them or use a separate object.


  3. Pingback: Working with Arrays and Hash Tables in Powershell – Curated SQL

Comments are closed.