3

I have imported an Excel sheet into PowerShell by using this module:

I want to count the number of columns so I used the below function:

 $columns = @(Import-Excel -Path "D:\Qlik\AccessRules\Roster\RosterTest-Jan.xlsx" -StartRow 1 -EndRow 1 -NoHeader)

Now the array has 51 keys I presume. But how do I count it? No matter what I try, all I see is 1.

I tried: 

$columns.Count
$columns.PSObject.BaseObject.Count

Nothing works, any ideas please.

mklement0
  • 382,024
  • 64
  • 607
  • 775
Siddharth Sheshadri
  • 93
  • 6
  • 1
    The title is IMO too specific, should be Count properties of an object. `$ColumnCount = ($CountColumns | Get-Member -MemberType NoteProperty).Count` –  Mar 17 '19 at 15:36
  • Good point, @LotPings - I've generalized the title. Your solution works, but is a bit heavy-handed. – mklement0 Mar 17 '19 at 21:46

2 Answers2

2

tl;dr:

@($columns.psobject.properties).Count

What you're looking for is the count of an object's properties, which can be done via the .psobject.properties collection, which is available on any object in PowerShell - and therefore also on the [pscustomobject] instances returned by Import-Excel - as a reflection feature that allows you to inspect a given object's properties.

Counting the properties is tricky, because the specific collection data type returned by .psobject.properties - [System.Management.Automation.PSMemberInfoIntegratingCollection[System.Management.Automation.PSPropertyInfo]] - unexpectedly implements neither a .Count nor a .Length property.

The workaround is to use @(...), the array subexpression operator, to collect the individual properties as a regular PowerShell array ([object[]]), on which you can then call .Count:

@($columns.psobject.properties).Count

See this GitHub issue, which asks for a .Count property to be implemented on the collection returned by .psobject.properties so as to render this workaround unnecessary.

mklement0
  • 382,024
  • 64
  • 607
  • 775
1

This seems to have done it. 

$columns = (($columns[0].psobject.properties).Count).Count
mklement0
  • 382,024
  • 64
  • 607
  • 775
Siddharth Sheshadri
  • 93
  • 6
  • While this solution works, it is conceptually obscure: (a) The `[0]` is unnecessary, because `$columns` contains only a _single_ object, a `[pscustomobject]` instance. (b) The inner `.Count` effectively uses [member enumeration](https://stackoverflow.com/a/44620191/45375) to call `.Count` on _each property definition_, which effectively returns an _array_ containing `1` for each property, given that [PowerShell (since v3) implements a `.Count` property even on _scalars_](https://stackoverflow.com/a/44035802/45375) (single objects). It is that array's elements that the outer `.Count` counts. – mklement0 Mar 17 '19 at 21:37