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:

>>$a
 
1 
2 
3

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.

SQLNewBlogger

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
    “`

    Like

  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.

    Like

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

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 )

Google photo

You are commenting using your Google 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.