How use "Where-Object" condition with '[PScustomobject]'?

Question

I have some code:

$output = [PSCustomObject]@{
    Name       = $ws.UsedRange.Columns.Item(1).Value2
    Department = $ws.UsedRange.Columns.Item(3).Value2
}

$output | GM
TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition                                  
----        ----------   ----------                                  
Equals      Method       bool Equals(System.Object obj)              
GetHashCode Method       int GetHashCode()                           
GetType     Method       type GetType()                              
ToString    Method       string ToString()                           
Department  NoteProperty System.Object[,] Department=System.Object[,]
Name        NoteProperty System.Object[,] Name=System.Object[,]

I need to sort and filter my $output, but I can't. Nothing happens. Probably doing something wrong.

PS> $output

Name                             Department                                     
----                             ----------                                    
{Numbers, 1,2,3,4,5,6,7...}      {Sales,IT,Accounting,Developers...}

And my condition:

PS> $output | Sort-Object Department -Descending | Where-Object {$_.Department -eq "Sales"}

Name                             Department                                     
----                             ----------                                    
{Numbers, 1,2,3,4,5,6,7...}      {Sales,IT,Accounting,Developers...}

You've created a single PSCustomObject, with two fields Name and Department. Is this what you wanted to do? — Jeff Zeitlin
– Jeff Zeitlin, Commented Jan 11, 2018 at 12:36
I have excel file with 2 main 'Columns' (Number, Department) . ~ 660 rows. I will want extract these rows in [pscustomobject] and after use 'sort' and 'where' condition in order to be able to filter data by department. — Alex Snigirev
– Alex Snigirev, Commented Jan 11, 2018 at 12:40
You might want to consider whether it's not more appropriate to save the Excel file as a CSV, and then process it via Import-CSV piped to ForEach-Object. — Jeff Zeitlin
– Jeff Zeitlin, Commented Jan 11, 2018 at 12:43

Ansgar Wiechers · Accepted Answer · 2018-01-11 13:32:52Z

2

You created a single object with 2 properties, each of which contains all values of its associated column. Since Sort-Object and Where-Object sort and filter lists of objects by their properties there's nothing for these cmdlets to do here.

What you actually want to do is create one object per row.

$output = foreach ($row in $ws.UsedRange.Rows) {
    [PSCustomObject]@{
        Name       = $row.Columns.Item(1).Value2
        Department = $row.Columns.Item(3).Value2
    }
}

Untested, since I don't have MS Office at hand here.

answered Jan 11, 2018 at 13:32

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How use "Where-Object" condition with '[PScustomobject]'?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related