Parsing PowerShell space-separated output as a table

Question

PowerShell gives me this output back when running a command:

Mod  Ports  Module-Type                         Model              Status
---  -----  -----------                         ------------------ ----------
1    48     2/4/8/10/16 Gbps Advanced FC Module DS-X9448-768K9     ok
2    48     2/4/8/10/16 Gbps Advanced FC Module DS-X9448-768K9     ok
3    0      Supervisor Module-3                 DS-X97-SF1-K9      ha-standby
4    0      Supervisor Module-3                 DS-X97-SF1-K9      active *

I'm having trouble trying to split it, because it's not tab-separated, is space-separated, therefore when i use .split("") the column Module-Type is being separated incorrectly because its content also has spaces.

I'm new at this, any help would be appreciated.

Here's my code:

$RS = .\Plink server@puttysession -pw "password" "command"

foreach ($RSln in $RS){
    $arr = $RSln.split('',[System.StringSplitOptions]::RemoveEmptyEntries)
    Write-Host $RSln "|" $arr.Length
}

This gives me the line and the length of the created array, the first two lines are arrays with 5 as length, the third and fourth have 9 items because of the spaces in ModuleType

Answer 1

Most PowerShell scriptwriters would probably say that it is bad idea to use any text tables similar to the Format-Table output as an input, but I like to challenge that because formatted source tables can be read by humans and if humans can read them and determine e.g. the layout with respect where a column starts and ends, a program should be able to do the same...

In some cases (e.g. for sample data at the StackOverflow site), it might even be handier than other formats like CSV (which is difficult to read and only supports string data), XML (which is very verbose and therefore also difficult to read) or JSON (or PSON, which is better for complex data but not for a list of objects that contain simple native properties)

Based on this thought I have written a reusable ConvertFrom-SourceTable cmdlet which is available for download at the PowerShell Gallery and the source code from the GitHub iRon7/ConvertFrom-SourceTable repository.

In your case the command to convert the table to an object is as simple as $RS | ConvertFrom-SourceTable, let me show that:

$RS = '
    Mod Ports Module-Type                        Model              Status
    --- ----- ---------------------------------- ------------------ -----------
    1   48    2/4/8/10/16 Gbps Advance FC Module DS-X9448-768K9     ok
    2   48    2/4/8/10/16 Gbps Advance FC Module DS-X9448-768K9     ok
    3   0     Supervisor Module-3                DS-X97-SF1-K9      ha-standby
    4   0     Supervisor Module-3                DS-X97-SF1-K9      active *
'

$RS | ConvertFrom-SourceTable

Status      : ok
Model       : DS-X9448-768K9
Ports       : 48
Mod         : 1
Module-Type : 2/4/8/10/16 Gbps Advance FC Module

Mod         : 2
Module-Type : 2/4/8/10/16 Gbps Advance FC Module
Ports       : 48
Model       : DS-X9448-768K9
Status      : ok

Mod         : 3
Module-Type : Supervisor Module-3
Ports       : 0
Model       : DS-X97-SF1-K9
Status      : ha-standby

Mod         : 4
Module-Type : Supervisor Module-3
Ports       : 0
Model       : DS-X97-SF1-K9
Status      : active *

And as all your data is left aligned (and every column would need a header), the columns layout can be determined from the start meaning that you might even stream this table input (presuming that .\Plink outputs each line separately) via the pipeline:

.\Plink server@puttysession -pw "password" "command" | ConvertFrom-SourceTable

_{(Meaning if you supply the source table as a multi-line string, the ConvertFrom-SourceTable cmdlet will determine column witches on the whole table, otherwise it is based on the header, past and current records)}

If source tables would have a better formatting convention like "non-string data (data that need to be interpreted) is right aligned" (as actually done by Format-Table). You could make an assumption (as @Ansgar Wiechers did) that Mod and Ports are integers a fact. For example:

$Object = ConvertFrom-SourceTable '
    Mod Ports Module-Type                        Model          Status
    --- ----- -----------                        -----          ------
      1    48 2/4/8/10/16 Gbps Advance FC Module DS-X9448-768K9 ok
      2    48 2/4/8/10/16 Gbps Advance FC Module DS-X9448-768K9 ok
      3     0 Supervisor Module-3                DS-X97-SF1-K9  ha-standby
      4     0 Supervisor Module-3                DS-X97-SF1-K9  ha-standby
'

_{(note that in this example $Object | Format-Table will give the same output as the input)}

For more examples use the help: Help ConvertFrom-SourceTable -Full

Answer 2

The recommended way of handling this kind of input in PowerShell is to parse it into custom objects. You can do that either with a regular expression:

$RS | Where-Object {
    $_ -match '(\d+)\s+(\d+)\s+(.*?)\s+([a-z]+(?:-[a-z0-9]+){2,})\s+(.*)'
} | ForEach-Object {
    New-Object -Type PSObject -Property @{
        'Mod'        = [int]$matches[1]
        'Ports'      = [int]$matches[2]
        'ModuleType' = $matches[3]
        'Model'      = $matches[4]
        'Status'     = $matches[5]
    }
}

or (if the columns are of fixed width) by extracting substrings at defined offsets:

$RS | Select-Object -Skip 2 | ForEach-Object {
    New-Object -Type PSObject -Property @{
        'Mod'        = [int]$_.Substring(0, 3).Trim()
        'Ports'      = [int]$_.Substring(5, 5).Trim()
        'ModuleType' = $_.Substring(12, 35).Trim()
        'Model'      = $_.Substring(48, 18).Trim()
        'Status'     = $_.Substring(67).Trim()
    }
}

The Select-Object -Skip 2 is needed to skip over the 2 header lines.

If all columns were separated by more than one space splitting via -split '\s\s+' (split at 2 or more consecutive whitespace characters) would work too. However, with your data that is not the case, since at least the first 2 data rows have only a single space between the 3rd and 4th column.