CODEWITHSUNDEEP
powershellexchange-server
Joseph
Joseph

Reputation: 676

How to format output of a DataTable object in Powershell

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.

Upvotes: 1

Views: 11260

Answers (1)

reverpie
reverpie

Reputation: 391

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 }
}

Upvotes: 2

Related Questions