Extract columns from text based table output

Question

qfarm /load command shows me the load from my servers. Output:

PS> qfarm /load

Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons

I need to display only first column (Server Name) and the second one (Server Load) and loop through them, in order to make some logic later, but it seems the powershell doesn't see it as object with properties:

PS> qfarm /load | Select -ExpandProperty "Server Name"
Select-Object : Property "Server Name" cannot be found.

Is there any other possibility, like a table or something?

run `(qfarm /load).GetType()` to see what datatype PowerShell sees it as. I assume String. — G42, Nov 16 '17 at 17:32
_Very_ close duplicate in my mind: https://stackoverflow.com/questions/29125337/split-text-by-columns-in-powershell — Matt, Nov 16 '17 at 19:18

Bill_Stewart · Accepted Answer · 2017-11-16T18:43:42.193

One way to do this is to build objects out of the command's output. Tested the following:

#requires -version 3

# sample data output from command
$sampleData = @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@ -split "`n"

$sampleData | Select-Object -Skip 2 | ForEach-Object {
  $len = $_.Length
  [PSCustomObject] @{
    "ServerName"         = $_.Substring(0,  22).Trim()
    "ServerLoad"         = $_.Substring(22, 13).Trim() -as [Int]
    "LoadThrottlingLoad" = $_.Substring(35, 22).Trim()
    "LogonMode"          = $_.Substring(57, $len - 57).Trim()
  }
}

In your case, you should be able to replace $sampleData with your qfarm load command; e.g.:

qfarm /load | Select-Object -Skip 2 | ForEach-Object {
...

Of course, this is assuming no blank lines in the output and that my column positions for the start of each item is correct.

PowerShell version 2 equivalent:

#requires -version 2

function Out-Object {
  param(
    [Collections.Hashtable[]] $hashData
  )
  $order = @()
  $result = @{}
  $hashData | ForEach-Object {
    $order += ($_.Keys -as [Array])[0]
    $result += $_
  }
  New-Object PSObject -Property $result | Select-Object $order
}

# sample data output from command
$sampleData = @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@ -split "`n"

$sampleData | Select-Object -Skip 2 | ForEach-Object {
  $len = $_.Length
  Out-Object `
    @{"ServerName"         = $_.Substring(0,  22).Trim()},
    @{"ServerLoad"         = $_.Substring(22, 13).Trim() -as [Int]},
    @{"LoadThrottlingLoad" = $_.Substring(35, 22).Trim()},
    @{"LogonMode"          = $_.Substring(57, $len - 57).Trim()}
}

Wouldn't a better practice be to type-cast in front and error out rather than `-as [type]` and end up with an empty property? — Maximilian Burszley, Nov 16 '17 at 18:05
Possibly; that's a judgment call that depends on the output of the command. — Bill_Stewart, Nov 16 '17 at 18:07
Unfortunately my version is 2. The PSCustomObject is not recognized. — catalin, Nov 16 '17 at 18:40
Do you remember [this](https://stackoverflow.com/a/29128154/3829407) Bill? Sure that could work here as well — Matt, Nov 16 '17 at 19:19
@Bill_Stewart yes, this works on v2 also. Now I'm trying to understand all the code, so I can extract just the Server Name column and the Server Load column, so I can place them into arrays and make logic with them later. — catalin, Nov 16 '17 at 19:21
Put the code in its own script and run the script. You can then pipe the script's output to `Select-Object`, `Measure-Object`, `ForEach-Object`, etc. because you now have objects. — Bill_Stewart, Nov 16 '17 at 19:23
@Matt it would need some changes because the header column names here contain spaces. But the principles are there... — Bill_Stewart, Nov 16 '17 at 19:24
@cata great! Don't forget to accept the answer if it works for you. — Bill_Stewart, Nov 16 '17 at 20:02

iRon · Answer 2 · 2018-11-18T21:52:05.627

You can easily convert your table to PowerShell objects using the ConvertFrom-SourceTable cmdlet from the PowerShell Gallery:

$sampleData = ConvertFrom-SourceTable @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@

And than select your columns like:

PS C:\> $SampleData | Select-Object "Server Name", "Server Load"

Server Name Server Load
----------- -----------
SERVER-01   400
SERVER-02   1364
SERVER-03   1364
SERVER-04   1000
SERVER-05   700
SERVER-06   1200

For details see: ConvertFrom-SourceTable -?

_{The ConvertFrom-SourceTable cmdlet is available for download at the PowerShell Gallery and the source code from the GitHub iRon7/ConvertFrom-SourceTable repository.}

score 1 · Answer 3 · answered Nov 16 '17 at 17:38

1

Command-line utilities return their outputs as a string array. This should work:

qfarm /load | ForEach-Object { $_.Substring(0,33) }

answered Nov 16 '17 at 17:38

Maximilian Burszley

18,243
4
34
63

score 0 · Answer 4 · answered Nov 16 '17 at 19:17

I have answered something very similar to this in the past. I have a larger function for this but a simplified on work on left aligned string table just as you have shown in you example. See the linked answer for more explanation.

function ConvertFrom-LeftAlignedStringData{
    param (
        [string[]]$data
    )

    $headerString = $data[0]
    $headerElements = $headerString -split "\s{2,}" | Where-Object{$_}
    $headerIndexes = $headerElements | ForEach-Object{$headerString.IndexOf($_)}

    $results = $data | Select-Object -Skip 2  | ForEach-Object{
        $props = @{}
        $line = $_
        For($indexStep = 0; $indexStep -le $headerIndexes.Count - 1; $indexStep++){
            $value = $null            # Assume a null value 
            $valueLength = $headerIndexes[$indexStep + 1] - $headerIndexes[$indexStep]
            $valueStart = $headerIndexes[$indexStep]
            If(($valueLength -gt 0) -and (($valueStart + $valueLength) -lt $line.Length)){
                $value = ($line.Substring($valueStart,$valueLength)).Trim()
            } ElseIf ($valueStart -lt $line.Length){
                $value = ($line.Substring($valueStart)).Trim()
            }
            $props.($headerElements[$indexStep]) = $value    
        }
        New-Object -TypeName PsCustomObject -Property $props
    } 

    return $results
}


$qfarmOutput = qfarm /load
ConvertFrom-LeftAlignedStringData $qfarmOutput | select "Server Name","Server Load"

This approach is based on the position of the header fields. Nothing is hardcoded and it is all custom built based on those indexes and field names. Using those $headerIndexes we carve up every line and place the results, if present, into its respective column. There is logic to ensure that we don't try and grab any part of the string that might not exist and treat the last field special.

Results

Server Name Server Load
----------- -----------
SERVER-01   400        
SERVER-02   1364       
SERVER-03   1364       
SERVER-04   1000       
SERVER-05   700        
SERVER-06   1200

I think probably the `Server Load` property should be type `[Int]`. — Bill_Stewart, Nov 16 '17 at 20:13
Yes. That could be useful but depending on what you want you can rely on LHS typing or force it when you doing the calculation. That logic could certainly be useful but goes beyond the scope of my function. — Matt, Nov 16 '17 at 20:17

Extract columns from text based table output

4 Answers4

Results

Linked