Constructing a table from raw data in powershell

Question

I am frustrated beyond belief with Powershell at the moment, because I feel stupid for spending 2 whole work days figuring out a (most likely super simple) solution for the following problem: I would like to convert two arrays ($HeaderCells, $DataCells) into a table and nothing seems to work. I tried PSObjects, Arrays, Hash Tables, data tables... Here is my code:

$Table = @()
$HeaderCells = @("Company","Country")
$DataCells = @("Test Inc.","Misc Corp.","USA","UK")

foreach ($HeaderCell in $HeaderCells)
{
    foreach ($DataCell in $DataCells)
    {
        $Table += New-Object -TypeName PSObject -Property @{$HeaderCell=$DataCell}
    }
}
$Table

my Output is:

Company   
-------   
Test Inc. 
USA       
Misc Corp.
UK       

I would like to get two columns (Company and Country), but no matter what I try (even the most nested for loops, I always end up overwriting variables or just getting errors.

My actual use case is actually a bit more complicated (extracting a table from a html page), but solving this part will allow me to continue, I hope.

My desired output would be to have the HeaderCells as the Headers and the DataCells as the Rows, so something like this:

Company     Country
-------           --------
Test Inc.       USA 
Misc Corp.  UK

Answer 1

If we assume you already have a way to populate $HeaderCells and $DataCells with collections, you could take a more dynamic approach:

$DataCountPerGroup = $DataCells.Count/$HeaderCells.Count

$table = for ($i = 0; $i -lt $DataCountPerGroup; $i++) {
    $hash = [ordered]@{}
    $dataIncrement = 0
    $HeaderCells | Foreach-Object { 
        $index = $dataIncrement * $DataCountPerGroup
        $hash.Add($_,$DataCells[$i+$index])
        $dataIncrement++
    }
    [pscustomobject]$hash
}
$table

This assumes that $DataCells.Count % $HeaderCells.Count is 0 and if not, you will need some error checking.

The idea is if you have n number of headers, then you will have n category groups of data with each of those having the exact same number of items.

The outer for loops through the number of items in a data category. This means if you have 4 companies (category 1), 4 countries (category 2), and 4 websites (category 3), the loop will iterate 0 through 3 (4 times). An ordered hash table is initialized at the beginning of the loop. $dataIncrement is a temporary variable to help us jump from the different categories within $DataCells. Once the hash table is populated, it can then be used to construct the custom object that will represent an entry in your table ($table).

---

A trivial example using the indices of your two arrays would process as follows:

1. $HeaderCells contains 3 items (indexes 0-2)
2. $DataCells contains 15 items (3 groups of 5 items)(indexes 0-14)
3. $i becomes 0.
4. $hash is initialized.
5. $hash adds key $HeaderCells[0] and value $DataCells[0].
6. $hash adds key $HeaderCells[1] and value $DataCells[5].
7. $hash adds key $HeaderCells[2] and value $DataCells[10].
8. A custom object is created using $hash and is added to $table.
9. $i becomes 1.
10. $hash is initialized.
11. $hash adds key $HeaderCells[0] and value $DataCells[1].
12. $hash adds key $HeaderCells[1] and value $DataCells[6].
13. $hash adds key $HeaderCells[2] and value $DataCells[11].
14. A custom object is created using $hash and is added to $table.
15. $i becomes 2.
16. $hash is initialized.
17. $hash adds key $HeaderCells[0] and value $DataCells[2].
18. $hash adds key $HeaderCells[1] and value $DataCells[7].
19. $hash adds key $HeaderCells[2] and value $DataCells[12].
20. A custom object is created using $hash and is added to $table.
21. By now, you can see the repeated processes that are happening. $i will continue to increment and the processes will continue with the same pattern until $i becomes 5. When $i becomes 5, the processing will break out of the loop.

Answer 2

You can also try:

$DataCells = "Test Inc.","Misc Corp.","USA","UK"
$half = [math]::Floor($DataCells.Count / 2)

(0..($half - 1)) | ForEach-Object {
    [PsCustomObject]@{
        Company = $DataCells[$_]
        Country = $DataCells[$_ + $half]
    }
}

Output:

Company    Country
-------    -------
Test Inc.  USA    
Misc Corp. UK

Answer 3

I'm a little confused by your example data, but here's how you might structure the code if your data was tweaked a bit to have a repeated list of [company1], [country1], [company2], [country2], etc.

$HeaderCells = @("Company","Country")
$DataCells = @("Test Inc.","USA","Misc Corp.","UK", "Canada Post", "CA")

$Table = @()

for ($i = 0; $i -lt $DataCells.Count; ) {

    $row = new-object -TypeName PSObject

    # Suggestion to replace the following:
    #   foreach ($HeaderCell in $HeaderCells)
    #   {
    #     $row | Add-Member -Type NoteProperty -Name $HeaderCell -Value $DataCells[ $i++ ]
    #   }
    # with:
    $HeaderCells | ForEach-Object { 
        $row | Add-Member -Type NoteProperty -Name $_ -Value $DataCells[ $i++ ]
    }
    $Table += $row
}

$Table

Answer 4

I do not know what data you want to sort out there, but look at the example bellow. Basically I am creating a [PSCustomObject] and attach that as a row of an array.

$list=@(
    "Apple Corp",
    "Peach LLC",
    "Ananas LLC",
    "Tomato Corp"
)

$result = New-Object System.Collections.ArrayList

foreach ($name in $list){

    if($name -like "*corp"){
        $obj=[pscustomobject]@{
            Company = $name
            Country = 'USA'
        }
        $result += $obj
    }else{
        $obj=[pscustomobject]@{
            Company = $name
            Country = 'UK'
        }
        $result += $obj
    }
}

And the here is the output :

Write-Output $result

Company     Country
-------     -------
Apple Corp  USA    
Peach LLC   UK     
Ananas LLC  UK     
Tomato Corp USA    

This is just PoC to get the idea, the logic you want to implement is all up to you.