Remove a row from a powershell array/collection/hashtable

Question

I have a large array (imported from an Excel spreadsheet) that has a "total" row that I want to delete. It is always the last item in the array. I've tried to find a way to just delete the last array item, but can't. Data and code sample below:

$data = Import-XLSX -Path "C:\Pathtofile\spreadsheet.xlsx" -Sheet "SheetName" -RowStart 3
$data | ? {$_.Server -eq "Total"}


Server        : Total
VLAN          :
IP Address    :
CPU           : 84
RAM           : 313
C:\           : 2840
D:\           : 17950
OS Version    :
OS Edition    :
SQL Version   :
SQL Edition   :
Datastore     :
Reboot        :
Notes         :

I'd like to be able to delete this row from the array. I've tried the following:

$data.Remove("Total")
Exception calling "Remove" with "1" argument(s): "Collection was of a fixed size."
At line:1 char:1
+ $ImportCSV.Remove("Total")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : NotSupportedException

If I check the array type, I get the following:

$data.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

If I retype the object like so, I get the following:

$data = {$data}.Invoke()
$data.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Collection`1                             System.Object

$data.Remove("Total")
False

This does not delete the item. What am I missing here?

Answer 1

Arrays are immutable in PowerShell, you can't add or remove elements from them. What you can do is:

a) Make a new array and filter out the one you don't want (e.g. with -ne):

$data = $data | ? {$_.Server -ne "Total"}

or

b) Select everything up to the last element by index (might be resource hungry on a big array, I don't know):

$data = $data[0..($data.Count-2)]

or

c) Cast it to [System.Collections.ArrayList], which is mutable, then remove an element from it:

$data = [System.Collections.ArrayList]$data
$data.RemoveAt($data.Count-1)

Answer 2

To complement TessellatingHeckler's helpful answer with analysis and background information:

• As stated, PowerShell arrays (.NET type [System.Object[]]) are fixed-size - you cannot add or remove elements (you can, however, change the value of existing elements).

  • When you "add" elements to a PowerShell array with + or +=, a new array is created behind the scenes, which is a copy (shallow clone) of the original.

  • The only reason arrays have .Add(), .Remove(), and .RemoveAt() methods is that they implement the general-purpose System.Collections.IList interface, which is implemented by many different types of collections, some of which are resizable.
    Attempting to call these methods on a fixed-size collection such as an array results in the "Collection was of a fixed size." error message you saw.

• Sending anything through the pipeline that outputs more than 1 object comes out as a (new) array, irrespective of the type of the input collection. (A single result object is output as itself, not wrapped in a collection.)

  • Therefore, filtering out elements of a collection with commands such as $data | ? {$_.Server -ne "Total"} creates a new collection that is always an array ([System.Object[]], if there's more than 1 output object).

---

Why your attempts failed:

$data.Remove("Total") failed fundamentally, because arrays do not support removal, as mentioned.

But even if $data were a resizable collection (such as [System.Collections.ArrayList]), passing "Total" wouldn't have worked, because the IList.Remove method you tried to call requires that you pass the element to remove itself, which in your case would be the object whose .Server property contains "Total", most easily accessed as $data[-1].

Also note that passing a value that isn't an element of the list is a quiet no-op (if the collection is resizable).

Given that you wanted to remove the last element, the index-based IList.RemoveAt method would have been the simpler choice, however.

---

{$data}.Invoke() indeed performs a retyping of sorts, but in an arcane fashion that is best avoided:

At first glance, conceptually, that command should be a no-op: you're simply enclosing a variable reference in a script block ({ ... }), which you then invoke, which should simply outputs the variable's value.

The reason it isn't a no-op is that you're bypassing PowerShell's normal handling of script blocks by invoke it via the .NET .Invoke() method instead of how script blocks are normally called: with call operator &.

If you use .Invoke() on a script block, you always get a [System.Collections.ObjectModel.Collection[psobject]] instance (even if it contains only 1 object).

By contrast, using & does additional work after invoking .Invoke() behind the scenes: if the collection contains just 1 element, that element is returned directly (the collection is unwrapped). Otherwise, the collection is converted to a PowerShell array ([System.Object[]]; I'm unclear on the specifics of this conversion).

Since you used .Invoke() directly, your call to .Remove() operated on [System.Collections.ObjectModel.Collection[psobject]], which is resizable, and has its own .Remove() method whose removal logic is the same as that of IList.Remove(), which it shadows.

However, it differs from IList.Remove() in that returns a [bool] reflecting success of the removal attempt, which is why you got a false on your 2nd attempt.

Unlike in the 1st attempt, however, this time the failure wasn't fundamental, and passing in the object (row) that the last element contained would have worked; similarly, .RemoveAt() with the highest index would have worked:

# Convert to [System.Collections.ObjectModel.Collection[psobject]]
$dataResizable = { $data }.Invoke()

# Remove last item by index.
$dataResizable.RemoveAt($dataResizable.Count-1)

# Alternative: Remove last item by object reference / value.
# Returns [bool], which we suppress.
$null = $dataResizable.Remove($dataResizable[-1])

---

All that said, using .Invoke() on a script block just to get a resizable collection is ill-advised.
Instead, choose one of the methods demonstrated in TessellatingHeckler's answer.

Additionally, PSv5+ offers a simple way to remove the last element from a collection passed through a pipeline, Select-Object -SkipLast 1:

$data = Import-XLSX -Path "C:\Pathtofile\spreadsheet.xlsx" -Sheet "SheetName" -RowStart 3 |
  Select-Object -SkipLast 1