Create/populate a csv file with Powershell

To complement Martin Brandl's helpful answer with an explanation of your symptoms (emphasis added):

I tried using $output += $row again to add more rows, but it does not work at all and causes some strange errors to happen (if I write to a row, it writes to all rows, probably because it's all the same object).

Indeed, that is what happened: In .NET terms, type (class) [pscustomobject] is a reference type rather than a value type - as evidenced by [pscustomobject].IsValueType returning $false.

If you add a given instance (object) of a reference type to an array multiple times, all such elements point to the very same instance.

Here's brief demonstration.

$obj = [PSCustomObject] @{
    'Example Header 1' = $null
    'Example Header 2' = $null
}

$array = @()
foreach ($ndx in 1..2) {
  # By working with the original $obj every time, you
  # keep modifying the same instance's property values.
  $obj.'Example Header 1' = "h1-$ndx"
  $obj.'Example Header 2' = "h2-$ndx"
  # Adding $obj to an array does NOT create a COPY of $obj
  # but stores a REFERENCE directly to $obj in the array
  # (similar to storing a pointer in unmanaged languages such as C++).
  $array += $obj
}

# Output the array.
$array

This yields the following:

Example Header 1 Example Header 2
---------------- ----------------
h1-2             h2-2
h1-2             h2-2

As you can see, only the last values assigned to .Example Header 1 and .Example Header 2 took effect, because both array elements reference the very same object.

Martin's approach is the simplest way to solve this problem: create a new instance of the custom object in every iteration (casting a hashtable literal to [pscustomobject], as now shown in the question itself: $array += [pscustomobject] @{ ... }).

If you don't want to or cannot recreate your instances from scratch inside the loop, you have two basic choices:

• Clone a template custom object in every loop iteration or simply use [pscustomobject] @{ ... } object creation inside the loop, which implicitly creates a new instance every time:

  • See this answer

• PSv5+ alternative: Define a custom class and instantiate it in every loop iteration - see below.

Class-based Solution

In PSv5+, a custom class allows for an elegant solution that also performs better than creating instances in-loop using literal syntax.

# Define a custom class that represents the rows of the
# output CSV.
# Note: [object] is being used here as the properties' type.
#       In real life, you'd use more specific types such as [string]
#       or [int].
class CsvRow {
  [object] ${Example Header 1}
  [object] ${Example Header 2}
}

$array = @()
foreach ($ndx in 1..2) {
  # Instantiate the custom class.
  $rowObj = [CsvRow]::new()
  # Set the values.
  $rowObj.'Example Header 1' = "h1-$ndx"
  $rowObj.'Example Header 2' = "h2-$ndx"
  # Add the instance to the array.
  $array += $rowObj
}

# Output the array.
$array

Performance Considerations

Two factors determine performance:

• How quickly the array is extended in each loop iteration:

  • Extending an array element by element with $array += ... is very convenient, but is slow and inefficient, because a new array must be created every time (arrays are fixed-size collections and cannot be directly extended).

  • For small iteration counts that may not matter, but the higher the number, the more performance will suffer, and at some point this approach becomes infeasible.

  • The next best solution is to use a [System.Collections.Generic.List[object]] instance to build the array instead - such lists are designed to be efficiently extended.

  • The best and simplest solution, however, is to simply let PowerShell collect multiple outputs from a loop-like statement in an array, simply by assigning to a variable - see below.

• How quickly the new object is instantiated in each loop iteration:

  • Instantiating an instance of the custom class is faster than creating an instance via the hashtable literal, but only if [CsvRow]::new() is used for instantiation; the functionally equivalent New-Object CsvRow is much slower, due to involving a cmdlet call.

The following variant of the custom-class solution uses implicit array creation to ensure acceptable performance even with higher iteration counts:

# Define the custom class.
class CsvRow {
  [object] ${Example Header 1}
  [object] ${Example Header 2}
}

# Determine the iteration count.
$count = 1000

# Loop and let PowerShell collect the outputs
# from all iterations implicitly in variable $array
[array] $array = foreach ($ndx in 1..$count) {
  # Instantiate the custom class.
  $rowObj = [CsvRow]::new()
  # Set the values.
  $rowObj.'Example Header 1' = "h1-$ndx"
  $rowObj.'Example Header 2' = "h2-$ndx"
  # Simply output the row object
  $rowObj
}

# Output the array.
$array

Note: The [array] type constraint is only needed if you need to ensure that $ToWrite is always an array; without it, if there happened to be just a single loop iteration and therefore output object, $ToWrite would store that output object as-is, not wrapped in an array (this behavior is fundamental to PowerShell's pipeline).