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
Solution 4
Some of the answers here commendably try to parse the input into objects, which, however, is (a) a nontrivial effort and (b) comes at the expense of performance.
As an alternative, consider text parsing using PowerShell's -split
operator, which in its unary form splits lines into fields by whitespace similar to the standard awk
utility on Unix platforms:
On Windows, if you first install an awk
port such as Gawk for Windows, you could invoke awk
directly, as demonstrated in Ed Morton's answer. On Unix (using PowerShell Core), awk
is available by default.
The solution below is similar to Ed's, except that it won't perform as well.
qwinsta | % { if (($fields = -split $_)[4] -eq 'rdpwd') { $fields[1] } }
-split $_
splits the input line at hand ($_
) into an array of fields by runs of whitespace, ignoring leading and trailing whitespace.(...)[4] -eq 'rdpwd'
tests the 5th field (as usual, indices are0
-based) for the value of interest.In case of a match,
$fields[1]
then outputs the 2nd field, the (assumed to be nonempty) username.
Solution 5
Print field 4,5 and 6 in second column.
awk 'NR>3&&NR<7{print $2}' file
user.name1
user.name2
user.name3
SuaSwe
Updated on July 07, 2022Comments
-
SuaSwe almost 2 years
I'm a PowerShell novice (Bash is my thing normally) who's currently trying to obtain qwinsta output to show who is logged in as an 'rdpwd' (rdesktop) user so that I can check each username against a list of usernames, and if they do not match, log them off.
I am currently working through two problems:
- I am unable to split the qwinsta output to be left with only the username - I've tried the "split" function but so far am getting either syntax issues or weird results; one gripe seems to be that '\s+' matches the letter S instead of whitespace; other times I've managed to split to the second column, but only output from line 1 appears
- Whilst I'm not there yet, I sense that I will have problems with the second step as well, namely looping through the array of non-log-offable users (which are to be obtained from a local user group)
I'll focus on problem 1 for now!
The text I've got is:
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
The output I want is:
user.name1 user.name2 user.name3
(With the aim of then creating a loop that says, in brief terms, "foreach user in list, if not in localgroup, logoff user".)
So far, I've got as far as selecting text with 'rdpwd', but using all manner of variations on "split", I have not got further forward than that.
I'm happy to share what I've got already, but alas I don't think it'll help anyone!
Any assistance would be most appreciated. :)