# What to do with a missing column?

**URL:** <https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545>\
**Category:** PowerShell Help\
**Created:** [June 9, 2022, 6:59pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545 "2022-06-09T18:59:09Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 9, 2022, 6:59pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/1 "2022-06-09T18:59:09Z")

</div>

So, I have this command I am trying to convertfrom-csv - but one entry can have a blank - which gets lost…  
The result will be like -

User Dept State Zip Time Location  
Me IT MO 123456 12:30 Main  
You MO 123456 1:30 Downtown

So,

```auto
Select-Object $Result -Skip 1 | ForEach-Object {($_ -replace '\s{2,}',',')}

```

That replaces each space with a comma and double commas with single…  
getting -

Me,IT,MO,123456,12:30,Main  
You,MO,123456,1:30,Downtown

Then  
Me,IT,MO,123456,12:30,Main  
You,MO,123456,1:30,Downtown

So… I would convert that from CSV - but the second line is missing a column!! Which throws everything off.

How to compensate for that missing column??

```auto
CommandToGetResult |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header 

```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 9, 2022, 7:58pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/2 "2022-06-09T19:58:26Z")

</div>

Bobby,  
Welcome to the forum. 👋🏽

If I got it right then the column is not missing - it is empty. And you should not mess with it as it makes your input CSV valid.

I’d recomend to make sure your input data comes from a reliable source with a reliable format instead of trying to fix or work around possible missing data later on.

BTW: When you post sample data or console output you should format it as code as well. This way we can copy this data to try to reproduce your situation.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 1:36am UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/3 "2022-06-10T01:36:55Z")

</div>

The result is from QUSER - Its not a CSV file, so I can’t control it.

The results are like:

```auto
PS C:\WINDOWS\system32> quser
 USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME
>bkearan console 1 Active 1+03:57 6/6/2022 2:41 PM
 bktest 2 Disc 1+03:57 6/8/2022 4:38 PM

```

So, the SESSION NAME portion is, in fact “missing”  
With the code posted at the end of the OP, it becomes:

```auto
bobbyk,console,1,Active,none,5/10/2022 10:47 PM
bktest,2,Disc,20.03,5/31/2022 7:53 AM

```

making the 2nd have invalid entries… ie, “2” is not the second session name.

Snippets from the actual code I have so far to get this output:

```auto
$Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
quser /server:$CN_Item |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header |
                    ForEach-Object {
                        #Write-Host $_.IdleTime
                        write-host $_
                        if (($_.IdleTime -eq 'none') -or ($_.IdleTime -eq '.') -or ($_.IdleTime -eq $Null) )
                            {
                            $IdleTime = $Null
                            }
                            else
                            {
                            If ($_.IdleTime -as [DateTime]) {
                                $IdleTime = New-TimeSpan -Start $_.IdleTime -End (Get-Date)
                                }
                                Else {
                                $IdleTime = [timespan]$_.IdleTime
                                }
                            }
                        [PSCustomObject]@{
                            ComputerName = $CN_Item
                            UserName = $_.UserName
                            SessionName = $_.SessionName
                            ID = $_.ID
                            State = $_.State
                            IdleTime = $IdleTime
                            LogonTime = $_.LogonTime
                            }
                        }                
                }

```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 6:48am UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/4 "2022-06-10T06:48:21Z")

</div>

> [@Graywalker](#):
>
> The result is from QUSER - Its not a CSV file, so I can’t control it.

Ah … I see … there’s an easy solution. Since `quser` outputs the table with fixed width I’d parse it with the `substring()` method instead. This way you’d get an empty cell for the line where the `SESSIONNAME` is missing. 😉

Depending on your actual goal you could use another way to query what you’re after. By WMI/CIM for example.

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 12:59pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/5 "2022-06-10T12:59:29Z")

</div>

I’m not sure how that would work - as user names are not always the same length, nor is “Active” and “Disc” the same length… there will not be a length that will match every output. ?

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 1:14pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/6 "2022-06-10T13:14:54Z")

</div>

Well, I got something that MAY work for all instances…

```auto
quser /server:$CN_Item |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{25}',',NoSession')} |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header |
                    ForEach-Object {

```

Not as elegant as I’d like, but if there are at least 25 spaces, its fairly safe (?) to assume that the “Session Name” is missing…

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 1:15pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/7 "2022-06-10T13:15:55Z")

</div>

> [@Graywalker](#):
>
> as user names are not always the same length, nor is “Active” and “Disc” the same length… there will not be a length that will match every output. ?

Since the table headers define the length of each column you simply use this and trim all unneeded spaces. No big deal. 😉

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 1:18pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/8 "2022-06-10T13:18:26Z")

</div>

> [@Graywalker](#):
>
> but if there are at least 25 spaces, its fairly safe (?) to assume that the “Session Name” is missing…

I wouldn’t like to depend on that. 🤔 But hey … it’s a free world. 😉 As long as it works for you. 👍🏽

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 1:33pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/9 "2022-06-10T13:33:07Z")

</div>

I just don’t see a reliable way to implement the substring() method given the actual output when having at least two logged on users.

I got started on this from : [https://www.reddit.com/r/PowerShell/comments/8r56tr/getting\_idle\_time\_for\_logged\_on\_domain\_users/](https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/)

I am encountering a lot of things that the posters of the original code found there didn’t anticipate.  
“Theory looks great until applied in practice.”

Like now, I am having to go back and put in something to account for if the Idle Time is more than 1 day - which ends up with an idle time of 1+15:39, which throws errors when converting to timespan -

```auto
PS C:\WINDOWS\system32> $Result
,USERNAME,,,,,,,,,,,,,,SESSIONNAME,,,,,,,,ID,,STATE,,,IDLE,TIME,,LOGON,TIME
>bkearan,,,,,,,,,,,,,,,console,,,,,,,,,,,,,1,,Active,,,,1+15:39,,6/6/2022,2:41,PM
,bktest,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,,Disc,,,,,,1+15:39,,6/8/2022,4:38,PM

```

What seems brilliant in the lab, doesn’t always work in the real world. The ‘code’ above shows the output with all the spaces replaced with commas… it doesn’t look like the headers define the column length. Just doesn’t match up.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 1:47pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/10 "2022-06-10T13:47:27Z")

</div>

> [@Graywalker](#):
>
> I just don’t see a reliable way to implement the substring() method given the actual output when having at least two logged on users.

Look closer! 😉

For the Quser output you shared you could do it like this:

```auto
$QuserOutput = @'
 USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME
>bkearan console 1 Active 1+03:57 6/6/2022 2:41 PM
 bktest 2 Disc 1+03:57 6/8/2022 4:38 PM
'@ -split "`n" |
    Select-Object -Skip 1

$UserList =
foreach ($line in $QuserOutput) {
    [PSCustomObject]@{
        UserName = $line.Substring(1, 22).trim()
        SessionName = $line.Substring(23, 19).trim()
        ID = $line.Substring(42, 3).trim()
        State = $line.Substring(46, 8).trim()
        IdleTime = $line.Substring(54, 11).trim()
    }
}
$UserList | Format-Table -AutoSize

```

The output would look like this:

```auto
UserName SessionName ID State IdleTime
-------- ----------- -- ----- --------
bkearan console 1 Active 1+03:57
bktest 2 Disc 1+03:57

```

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 3:39pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/11 "2022-06-10T15:39:48Z")

</div>

Here is the final, working code

```auto
#
# Complied from pieces of code and additions by Bobby Kearan with inspiration from
# https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/
# and Thanks to Olaf (https://forums.powershell.org/u/Olaf) for helping with parsing the output of quser properly
#
#

function Get-QUserInfo
    {
    [CmdletBinding()]
    Param (
        [Parameter ()]
            [string[]]
            $ComputerName = $env:COMPUTERNAME
        )

    begin
        {
        $Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
        $No_Connection = '-- No Connection --'
        }

    process
        {
        foreach ($CN_Item in $ComputerName)
            {
            if (Test-Connection -ComputerName $CN_Item -Count 1 -Quiet)
                {
                $QuserOutput = quser /server:$CN_Item
                $QuserOutput -split "`n" | Select-Object -Skip 3
                    foreach ($line in $QuserOutput) {
                    
                    $IdleTime = $line.Substring(54, 11).trim()
                    #Write-Host $IdleTime
                    If (($IdleTime -eq 'none') -or ($IdleTime -eq '.') -or ($IdleTime -eq '$Null')) {
                    $IdleTime = "Not Idle"
                    }
                    IF ($IdleTime -like '*+*') {
                        $IdleTime = $IdleTime.replace("+",":")
                        #Write-Host $IdleTime " was the Plus sign replaced + ? "
                      }
              
                    If ($_.IdleTime -as [DateTime]) {
                          $IdleTime = New-TimeSpan -Start $IdleTime -End (Get-Date)
                          }
                        Else {

                          $IdleTime = [timespan]$IdleTime
                          }
                       
                    #Write-Host $IdleTime
                    $final = $line.length - 65
                    $Username = $line.Substring(1, 22).trim()
                    
                     [PSCustomObject]@{
                     UserName = $line.Substring(1, 22).trim()
                     SessionName = $line.Substring(23, 19).trim()
                     ID = $line.Substring(42, 3).trim()
                     State = $line.Substring(46, 8).trim()
                     IdleTime = $IdleTime
                     LogonTime = $line.Substring(65, $final).trim()
                        }

    }
                }
                else
                {
                [PSCustomObject]@{
                    ComputerName = $CN_Item
                    UserName = $No_Connection
                    SessionName = $No_Connection
                    ID = $No_Connection
                    State = $No_Connection
                    IdleTime = $No_Connection
                    LogonTime = $No_Connection
                    }                
                }
            } # end >> foreach ($CN_Item in $ComputerName)
        } # end >> process {}

    end {}

    } # end >> function Get-QUserInfo

    $Info = Get-QUserInfo -ComputerName localhost

    $state = "Active"
    While($state = "Active"){
        $Info = Get-QUserInfo -ComputerName LocalHost
        ForEach ($user in $Info) {
        $IdleHou = $user.IdleTime.Hours
        $IdleMin = $user.IdleTime.Minutes
        Write-Host $user.State " User " $user.UserName " has been idle " $IdleHou " hours, " $IdleMin " Minutes"
        If (($user.State.Trim() -ne "Active") -and ($IdleHou -ge 1)) {
        #Write-Host $user.UserName "Should be logged off"
        logoff $user.ID
        }
        }
    Sleep -Seconds 350
    }

```

Please feel free to share improvements!!

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 3:42pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/12 "2022-06-10T15:42:12Z")

</div>

Here is the working code -

```auto
#
# Complied from pieces of code and additions by Bobby Kearan with inspiration from
# https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/
# and Thanks to Olaf (https://forums.powershell.org/u/Olaf) for helping with parsing the output of quser properly
#
#

function Get-QUserInfo
    {
    [CmdletBinding()]
    Param (
        [Parameter ()]
            [string[]]
            $ComputerName = $env:COMPUTERNAME
        )

    begin
        {
        $Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
        $No_Connection = '-- No Connection --'
        }

    process
        {
        foreach ($CN_Item in $ComputerName)
            {
            if (Test-Connection -ComputerName $CN_Item -Count 1 -Quiet)
                {
                $QuserOutput = quser /server:$CN_Item
                $QuserOutput -split "`n" | Select-Object -Skip 1 |
                    ForEach-Object {
                    
                    $IdleTime = $_.Substring(54, 11).trim()
                    #Write-Host $IdleTime
                    If (($IdleTime -eq 'none') -or ($IdleTime -eq '.') -or ($IdleTime -eq '$Null')) {
                    $IdleTime = "Not Idle"
                    }
                    IF ($IdleTime -like '*+*') {
                        $IdleTime = $IdleTime.replace("+",":")
                        #Write-Host $IdleTime " was the Plus sign replaced + ? "
                      }
              
                    If ($_.IdleTime -as [DateTime]) {
                          $IdleTime = New-TimeSpan -Start $IdleTime -End (Get-Date)
                          }
                        Else {
                          If ($IdleTime -ne 'Not Idle') {
                          $IdleTime = [timespan]$IdleTime }
                          }
                       
                    #Write-Host $IdleTime
                    $final = $_.length - 65
                    $Username = $_.Substring(1, 22).trim()
                    
                     [PSCustomObject]@{
                     UserName = $_.Substring(1, 22).trim()
                     SessionName = $_.Substring(23, 19).trim()
                     ID = $_.Substring(42, 3).trim()
                     State = $_.Substring(46, 8).trim()
                     IdleTime = $IdleTime
                     LogonTime = $_.Substring(65, $final).trim()
                        }

    }
                }
                else
                {
                [PSCustomObject]@{
                    ComputerName = $CN_Item
                    UserName = $No_Connection
                    SessionName = $No_Connection
                    ID = $No_Connection
                    State = $No_Connection
                    IdleTime = $No_Connection
                    LogonTime = $No_Connection
                    }                
                }
            } # end >> foreach ($CN_Item in $ComputerName)
        } # end >> process {}

    end {}

    } # end >> function Get-QUserInfo

    $Info = Get-QUserInfo -ComputerName localhost

    $state = "Active"
    While($state = "Active"){
        $Info = Get-QUserInfo -ComputerName LocalHost
        ForEach ($user in $Info) {
        $IdleHou = $user.IdleTime.Hours
        $IdleMin = $user.IdleTime.Minutes
        Write-Host $user.State " User " $user.UserName " has been idle " $IdleHou " hours, " $IdleMin " Minutes"
        If (($user.State.Trim() -eq "Disc") -and ($IdleHou -ge 1)) {
        #Write-Host $user.UserName "Should be logged off"
        logoff $user.ID
        }
        }
    Sleep -Seconds 160
    }
    

```

Please feel free to share improvements!  
\*\* Edited to correct an issue… \*\*

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 6:53pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/13 "2022-06-10T18:53:40Z")

</div>

> [@Graywalker](#):
>
> As it still gets the header in the output…

Maybe you get it yourself … 👆🏽

Whatfor do you run this line of code:

```auto
$QuserOutput -split "`n" | Select-Object -Skip 3

```

?? 😉

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 7:11pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/14 "2022-06-10T19:11:35Z")

</div>

> [@Olaf](#):
>
> Whatfor do you run this line of code:
> 
> `$QuserOutput -split "`n" | Select-Object -Skip 3`

Well, for some reason, I was getting three blank lines in the output. ??? That line eliminates those blank entries. I adjusted it to also skip the Header line (see the edited reply).

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 10, 2022, 7:27pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/15 "2022-06-10T19:27:31Z")

</div>

Hmm … you changed much more than I would have. 🤷🏽‍♂️ And the pointless line of code is still there. 👆🏽

What I meant is you run this line of code but you don’t use the output of it. 😉 Probably you started with one line and increased it when it didn’t provided the output you expected, didn’t you? 😉 IF you want to shorten the content of the variable you need to assign it to itself like this:

```auto
$QuserOutput = $QuserOutput -split "`n" | Select-Object -Skip 3

```

In the previous version of your code I would have changed the line above to this:

```auto
$QuserOutput = quser /server:$CN_Item | Select-Object -Skip 1

```

and would have deleted the line we’re discussing here completely. 😉

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 10, 2022, 7:44pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/16 "2022-06-10T19:44:07Z")

</div>

Then I wonder why it was (and is) working??

So weird… took that line out and it works the same. Edited the code above…

---

<div class="post-metadata">

**Author:** ![MrAutomation](https://avatars.discourse-cdn.com/v4/letter/m/a6a055/32.png) [@MrAutomation](https://forums.powershell.org/u/MrAutomation)\
**Post date:** [June 12, 2022, 11:45am UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/17 "2022-06-12T11:45:03Z")

</div>

This is all you need : quser | ConvertFrom-String

Good luck with it,

Kind regard MrAutomation

---

<div class="post-metadata">

**Author:** ![Graywalker](https://avatars.discourse-cdn.com/v4/letter/g/4491bb/32.png) [@Graywalker](https://forums.powershell.org/u/Graywalker)\
**Post date:** [June 13, 2022, 12:43pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/18 "2022-06-13T12:43:02Z")

</div>

> [@MrAutomation](#):
>
> This is all you need : quser | ConvertFrom-String

That looked like it would be great - but has some problems of it’s own - like splitting up the string based on spaces, so “Logon Time” was split out to P8 : Logon P9 : Time  
 ![powershell_ise_bqHiFRqYVz](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/c/c3965774096bda8403948431dc37cda82b4f0797.png)

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:17pm UTC](https://forums.powershell.org/t/what-to-do-with-a-missing-column/19545/19 "2024-05-16T20:17:49Z")

</div>

Closed on May 16, 2024, 8:17pm UTC