PowerShell create table issue

Question

I need to create a PowerShell table with multiple columns and in each column, I need to push different data, a succeeded to create the table but the solution looks like that:

I want that each column will start in the first index

This is the code:

$tabName = "SampleTable"

#Create Table object
$table = New-Object system.Data.DataTable “$tabName”

$groups = 'prod', 'env', 'app'
$vms = '1', '2', '3' 

foreach ($group in $groups)
{
    $col = New-Object system.Data.DataColumn $group,([string])
    $table.columns.add($col)

    foreach ($vm in $vms)
    {
        $row = $table.NewRow()
        $row.$group = $vm
        $table.Rows.Add($row)
   }

}

$table # | Export-Csv $path -NoTypeInformation

from another function (I fetch the data from request API), now I create it manually only for test. — xzOtikx, May 02 '22 at 08:13
I don't really understand how the above will help me because, In the end, I will need to create a table and push it to CSV. — xzOtikx, May 02 '22 at 08:41

score 1 · Answer 1 · answered May 02 '22 at 09:09

The reason for behaviour is that there are two nested loops, and nine rows are created in the inner loop. Let's have a look:

foreach ($group in $groups)
{
    # Here we will have prod, env, app. This loop executes three times.
    $col = New-Object system.Data.DataColumn $group,([string])
    $table.columns.add($col)

    foreach ($vm in $vms) 
    {
        # We have three VMs, so this executes three times
        # 3*3 makes nine:
        # for prod, we'll have 1,2,3 , so make three rows
        # for  env, we'll have 1,2,3 , so make three more rows
        # for  app, we'll have 1,2,3 , so make three another rows        
        $row = $table.NewRow()
        $row.$group = $vm
        $table.Rows.Add($row)
   }

For a fix, first add a row, then add several data columns into one. Like so,

$t =  New-Object system.Data.DataTable 'sample'
$groups = 'prod', 'env', 'app'

foreach($g in $groups) { 
 $col = New-Object system.Data.DataColumn $g,([string])
 $t.Columns.Add($col)
}

for($i=0; $i -lt 3; ++$i) {
  $r = $t.NewRow()
  
  foreach($g in $groups){$r[$g] = "vm-"+$g+"-"+$i}
    $t.rows.add($r)
}

$t
# Output
prod      env      app
----      ---      ---
vm-prod-0 vm-env-0 vm-app-0
vm-prod-1 vm-env-1 vm-app-1
vm-prod-2 vm-env-2 vm-app-2

Theo · Answer 2 · 2022-05-02T13:44:21.697

Why the use of a DataTable at all, when eventually all you want is to create a CSV file?

One way is to make use of cmdlets ConvertFrom-Csv and Export-Csv

$groups = 'prod', 'env', 'app'
$vms    = '1', '2', '3'

# create an in-memory csv
# create an in-memory csv
$data = foreach ($vm in $vms) {
    $(foreach ($group in $groups) {
        # output a formatted string we combine with a comma afterward
        'vm-{0}-{1}' -f $group, $vm
    }) -join ','
}

# now convert this data including the $groups header to an object array
$data = $data | ConvertFrom-Csv -Header $groups

# show as table on screen
$data | Format-Table -AutoSize

# to output as CSV file, just use export-csv
$data | Export-Csv 'X:\Somewhere\SampleTable.csv' -NoTypeInformation

Output:

prod      env      app     
----      ---      ---     
vm-prod-1 vm-env-1 vm-app-1
vm-prod-2 vm-env-2 vm-app-2
vm-prod-3 vm-env-3 vm-app-3

iRon · Answer 3 · 2022-05-02T13:58:29.743

Using this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?) which is not limited to string types (As ConvertTo-Csv) and has a lot more joining features:

1,2,3 |Join 4,5,6 |Join 7,8,9 -Name prod, env, app

prod env app
---- --- ---
   1   4   7
   2   5   8
   3   6   9

If you do already have a tables (arrays of objects with one or more properties):

$Prod = ConvertFrom-Csv @'
prod
1
2
3
'@

$Env = ConvertFrom-Csv @'
env
4
5
6
'@

$App = ConvertFrom-Csv @'
app
7
8
9
'@

$Prod |Join $Env |Join $App

prod env app
---- --- ---
1    4   7
2    5   8
3    6   9

PowerShell create table issue

3 Answers3