How to format output of a DataTable object in Powershell

Question

So I was trying to format some output to the console using colors based on different conditions using plain ol' spaces and tabs and quickly found that I was creating so many "if" and "elseif" statements. It was not pretty. Nor did I finish it because there are so many "columns" in the data table, and the length property of the value of each column in the table can vary anywhere between 6 and 20+ characters. It really was ugly. So that's when I started to try using a DataTable object. I have something like this:

$queueTable = New-Object System.Data.DataTable
$Queue.Columns.Add("Identity",[string]) | Out-Null
#same thing for 5 more columns, just different column names and types

Now add data to the table:

$queues = Get-Queue
foreach ($queue in $queues) {
    $NewRow = $queueTable.NewRow()
    $NewRow.Identity = $queue.Identity
    #And so for the next 5 columns
    $queueTable.Rows.Add($NewRow)
}

Now what I'm trying to do is output the DataTable object to the screen, but formatted with color based on condition. Here's what I have for that piece:

foreach ($row in $queueTable) {
    if ($row.item("MessageCount") -gt 1) {Write-Host -ForegroundColor Red $row
    else {Write-Host $row}
}

The output for that code is simply "System.Data.Row" and not the data inside the row. I also tried using Write-Output, and that actually outputs the data, but not pretty and it also does not allow formatting with color.

When I run $queueTabel | Format-Table, it gives the output in a table format like I'm looking for, but it doesn't allow me to do conditional formatting.

Remember, I'm trying to write this to the console, so Out-GridView, DataGrid or DataGridView won't work.

I believe you have to loop like this: foreach ($row in $queueTable.Rows) {. . .} — Theo
– Theo, Commented Jan 15, 2019 at 13:38
Try Write-Host $row.ItemArray or if you'd like the values separated by a comma, something like this: Write-Host ($row.ItemArray -join ', ') — Theo
– Theo, Commented Jan 15, 2019 at 14:27
The "ItemArray" attempt does indeed write it out, but not in a pretty format. I really need it to be a table, or at least that's the goal. — Joseph
– Joseph, Commented Jan 15, 2019 at 14:58

reverpie · Accepted Answer · 2019-01-15 20:48:47Z

2

As I have understood, we have to color specific rows based on values in a column. The solution proposed below creates a parallel "array of strings" to be use to create the colored output. Maybe the code in the loop can be optimized as well

# Creating the table and adding rows
$queueTable = New-Object System.Data.DataTable
$queueTable.Columns.Add("Identity",[string]) | Out-Null
$queueTable.Columns.Add("MessageCount",[int]) | Out-Null

$queueTable.Rows.Add("test1",1) | Out-Null
$queueTable.Rows.Add("test2",2) | Out-Null
$queueTable.Rows.Add("test3",3) | Out-Null

# convert the datatable to an array of strings
$tableTest = $queueTable | Format-Table | Out-String -Stream

# the first two lines are the header and separator to be printed in any case
$tableTest[0..2] | Write-Host 

# then we process the original datatable rows
# if a row matches the criteria, then we print the related line in $tableText
# using the correct format
for ($i=0; $i -lt $queueTable.Rows.Count; $i++) {
    if ($queueTable.Rows[$i].MessageCount -gt 1) { 
        $tableTest[$i+3] | Write-Host -ForegroundColor Red -NoNewline; Write-Host }
    else { $tableTest[$i+3] | Write-Host -NoNewline; Write-Host }
}

answered Jan 15, 2019 at 20:48

reverpie

3912 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joseph Over a year ago

This will be interesting. I will give it a shot and let you know.

Joseph Over a year ago

It was the $tableTest = $queueTable | Format-Table | Out-String -Stream line that makes the difference! Thanks! I'll have to look more into what that means.

Joseph Over a year ago

It also works with the original $queues object, an an FYI to anyone else reading this post. So you could forego the creating a new table section of code and use $tableTest = $queues | Format-Table | Out-String -Stream

Collectives™ on Stack Overflow

How to format output of a DataTable object in Powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related