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
CodePudding user response:
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
CodePudding user response:
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
CodePudding user response:
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