Split text by columns in PowerShell

Solution 1:

Honestly I'd look up a better way to do this, but you can fudge it with some text manipulation and the ConvertFrom-Csv cmdlet:

$(qwinsta.exe) -replace "^[\s>]" , "" -replace "\s+" , "," | ConvertFrom-Csv | select username

Firstly replace any leading spaces or > characters with nothing, then replace any white spaces with a comma. Then you can pipe to ConvertFrom-Csv and work with the data as an object.

EDIT

Actually, the above has some issues, mostly with the \s+ because if a column is blank it does not get correctly recognised as a blank field, and the next text is incorrectly promoted to the current field.

The below is a full blown parser for this command, and would probably work for any sort of tabulated output from a native windows exe:

$o = @()
$op = $(qwinsta.exe)

$ma = $op[0] | Select-String "(?:[\s](\w+))" -AllMatches
$ErrorActionPreference = "Stop"

for($j=1; $j -lt $op.length; $j++) {
    $i = 0
    $obj = new-object pscustomobject
    while ($i -lt $ma.matches.count) { 
      $prop = $ma.matches[$i].groups[1].value; 
      $substrStart = $ma.matches[$i].index 
      $substrLen = $ma.matches[$i+1].index - $substrStart
      try {
        $obj | Add-Member $prop -notepropertyvalue $op[$j].substring($substrStart,$substrLen).trim() 
      }
      catch [ArgumentOutOfRangeException] {
        $substrLen = $op[$j].length - $substrStart 
        if($substrLen -gt 0) {
          $obj | Add-Member $prop -notepropertyvalue $op[$j].substring($substrStart,$substrLen).trim()
        }
        else {
          $obj | Add-Member $prop -notepropertyvalue ""
        }
      }
      $i++
    }
    $o += ,$obj
}

$o | ? { $_.type -eq 'rdpwd'} | select username

USERNAME
--------
user.name1
user.name2
user.name3

Solution 2:

Can't tell for sure, but it sounds like you're trying to do a regex split using the string .split() method. That doesn't work. Use the Powershell -split operator to do a regex split:

(@'
SESSIONNAME       USERNAME        ID     STATE   TYPE      DEVICE
services                          0      Disc
console                           1      Conn
rdp-tcp#0         user.name1      2      Active  rdpwd
rdp-tcp#1         user.name2      3      Active  rdpwd
rdp-tcp#1         user.name3      4      Active  rdpwd
rdp-tcp                           65536  Liste
'@).split("`n") |
foreach {$_.trim()} | sv x


$x -match 'rdpwd' |
foreach { ($_ -split '\s+')[1] }

user.name1
user.name2
user.name3

Solution 3:

My take of the position based delimiter. All the other answers get you the information you are looking for but much like Arco I was looking for a PowerShell object based answer. This assumes $data is populated with new line delimeted text like you would get from get-content could easily split the output from qwinsta.exe ($data = (qwinsta.exe) -split "`r`n" for example)

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

$results = $data | Select-Object -Skip 1  | 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    
    }
    [pscustomobject]$props
} 

$results | Select-Object sessionname,username,id,state,type,device | Format-Table -auto

This approach is based on the position of the header fields. Nothing is hardcoded and it is all custom build 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 and part of the string that might not exist and treat the last field special.

$results would not contain your text as a custom psobject. Now you can do filtering like you would any other object collection.

Output from above sample

SESSIONNAME USERNAME   ID    STATE  TYPE  DEVICE
----------- --------   --    -----  ----  ------
services               0     Disc               
console                1     Conn               
rdp-tcp#0   user.name1 2     Active rdpwd       
rdp-tcp#1   user.name2 3     Active rdpwd       
rdp-tcp#1   user.name3 4     Active rdpwd       
rdp-tcp                65536 Listen             

Now we show all usernames where the type is rdpwd

$results | Where-Object{$_.type -eq "rdpwd"} | Select-Object -ExpandProperty username