PowerShell hash table from multiple Excel columns and use further

Question

I am reading few columns of an Excel file and storing the values in a hash. My objective it to use this hash further like

Hostname: $computer['server']['hostname']         # Hostname: host1
IP: $computer['server']['ip']                     # IP: x.x.x.x
Environment: $computer['server']['Environment']   # Environment: production

Code snippet:

$computers = @{}
$computers['Server'] = @{}
$computers['Server']['Hostname'] = @()
$computers['Server']['Environment'] = @()
$computers['Server']['ip'] = @()   

for ($startRow=2; $startRow -le $rowCount; $startRow++) {
    $hostname = $workSheet.Cells.Item($startRow,2).Value()
    $environment = $workSheet.Cells.Item($startRow,1).Value()
    $pip = $workSheet.Cells.Item($startRow,4).Value()
    $sip = $workSheet.Cells.Item($startRow,5).Value()

    $computers['Server']['Hostname'] += $hostname
    $computers['Server']['Environment'] += $environment
    $computers['Server']['ip'] += $ip
}

foreach ($computer in $computers) {
    foreach ($server in $computer['Server']) {
        $myhost = $computer['Server']['Hostname']
        $environ = $computers['Server']['Environment']

        Write-Host "$myhost : $environ `n"  
    }    
}

Actual output:

host1 host2 host3 host4 : prod dev prod stag

Expected output:

host1: prod
host2: dev
host3: prod
host4: stag

EDIT NOTE: I can always call and display the variables in first for loop itself while reading the Excel files but I also want to store them in a hash table for later usage.

Answer 1

You're getting that result because the data structure you created looks like this (using JSON notation):

{
    "Server": {
        "Hostname": [ "host1", "host2", "host3", "host4" ],
        "Environment": [ "prod", "dev", "prod", "stag" ],
        "IP": [ ... ]
    }
}

when you actually want something like this:

{
    "Server": [
        {
            "Hostname": "host1",
            "Environment": "prod",
            "IP": ...
        },
        {
            "Hostname": "host2",
            "Environment": "dev",
            "IP": ...
        },
        {
            "Hostname": "host3",
            "Environment": "prod",
            "IP": ...
        },
        {
            "Hostname": "host4",
            "Environment": "stag",
            "IP": ...
        }
    ]
}

To get the desired result you need to create an array of hashtables and assign that to the key "Server", or just make $computers an array if "Server" is your only key anyway:

$computers = @(for ($startRow=2; $startRow -le $rowCount; $startRow++) {
    ...

    @{
        'Hostname'    = $hostname
        'Environment' = $environment
        'IP'          = $ip
    }
})

You can then enumerate the computers like this:

foreach ($computer in $computers) {
    '{0}: {1}' -f $computer['Hostname', 'Environment']
}

Alternatively you could make $computers a hash of hashes

$computers = @{}
for ($startRow=2; $startRow -le $rowCount; $startRow++) {
    ...

    $computers[$hostname] = @{
        'Environment' = $environment
        'IP'          = $ip
    }
})

and enumerate the hosts like this:

foreach ($computer in $computers.GetEnumerator()) {
    '{0}: {1}' -f $computer.Key, $computer.Value['Environment']
}