How can I specify the column ordering when exporting to CSV in PowerShell?

Question

I'm writing a script in PowerShell that exports all security groups and their members from Active Directory. Now I want to format the output of the CSV file.

The code:

$Groups = Get-ADGroup -Properties * -Filter * -SearchBase "OU=SERVICES,DC=XXXXXX,DC=XXXXX" 

$Table = @()

$Record = @{
    "Group Name" = ""
    "Name" = ""
    "Username" = ""
}

Foreach($G In $Groups)
{
    $Arrayofmembers = Get-ADGroupMember -identity $G -recursive | select name,samaccountname
    Foreach ($Member in $Arrayofmembers) 
    {
        $Record."Group Name" = $G.Name
        $Record."Name" = $Member.name
        $Record."UserName" = $Member.samaccountname
        $objRecord = New-Object PSObject -property $Record
        $Table += $objrecord
    }
}

$Table | export-csv "C:\temp\SecurityGroups.csv" -NoTypeInformation

The result:

"Username","Name","Group Name"
"aman","Ani Manoukian","Commercial"
"adan","Aurelia Danneels","Commercial"
"kdeb","Kathleen De Backer","Commercial"
"TVGR","Thijs Van Grimbergen","Commercial"
"SVDE","Sofie Van den Eynde","Commercial"

Now I want the output formatted in this order:

"Group Name","Name","Username"

Instead of: "Username","Name","Group Name"

Toluca

For a more complete and flexible solution, please see this answer, taking a list and doing some additional CSV magic. – Sevilla

Answer 1

This should work...

$Table |
  Select-Object "Group Name", "Name", "Username" |
  Export-Csv "C:\temp\SecurityGroups.csv" -NoTypeInformation

Keneth

This answer also works when you don't control creation of the collection (or don't want to) – Lassie

Answer 2

gvee's helpful answer is a pragmatic solution that ensures that the columns appear in the desired order, because the order in which you pass property names to Select-Object is the order in which the properties are added to the resulting [pscustomobject] instances.

It is, however, inefficient, because the desired column order can be ensured at the time $Record is defined, without needing an additional pipeline stage that effectively duplicates the result objects:

Define $Record as an ordered hashtable as follows (requires PSv3+; optional quoting omitted; use of single quotes):

$Record = [ordered] @{
    'Group Name' = ''
    Name = ''
    Username = ''
}

This guarantees that the [pscustomobject] instances later created by the New-Object PSObject -property $Record calls contain properties in the same order as the keys were defined in $Record.

Two asides:

• New-Object PSObject -property $Record could be simplified to [pscustomobject] $Record (see below)
• Building up a large array incrementally is more efficiently handled with a [System.Collections.ArrayList] instance to which you add elements with .Add() rather than using PowerShell's built-in arrays with +=, which creates a copy of the array every time. Even better is to let PowerShell create the array for you, simply by capturing the output from your foreach loop in a variable ($Table = foreach ... - see this answer)

---

Supplemental information:

The source of the problem is that regular hashtables ([hashtable] instances) enumerate their keys in an effectively unpredictable order (the order is an implementation detail and not guaranteed), and when you create a [pscustomobject] from a hashtable, that unpredictable key ordering is reflected in the ordering of the resulting object's properties.

By contrast, in PSv3+ you can create an ordered hashtable by placing the [ordered] keyword before a hashtable literal, which results in a [System.Collections.Specialized.OrderedDictionary] instance whose keys are ordered based on the order in which they were added.
Creating a [pscustomobject] instance from an ordered hashtable then preserves that key ordering in the resulting object's properties.

Note that PowerShell v3+ offers a convenient shortcut for creating a [pscustomobject] instance from a hashtable using a cast; e.g.:

PS> [pscustomobject] @{ a = 1; b = 2; c = 3 } # key order is PRESERVED

a b c
- - -
1 2 3

Note how the key-definition order was preserved, even though [ordered] was not specified.
In other words: When you cast a hashtable literal directly to [pscustomobject], [ordered] is implied, so the above is equivalent to:

[pscustomobject] [ordered] @{ a = 1; b = 2; c = 3 }  # [ordered] is optional

Caveat: This implicit ordering only applies when a hashtable literal is directly cast to [pscustomboject], so the key/property order is not preserved in the following variations:

New-Object PSCustomObject -Property @{ a = 1; b = 2; c = 3 } # !! order NOT preserved

$ht = @{ a = 1; b = 2; c = 3 }; [pscustomobject] $ht # !! order NOT preserved

[pscustomobject] (@{ a = 1; b = 2; c = 3 }) # !! order NOT preserved, due to (...)

Therefore, when not casting a hashtable literal directly to [pscustomobject], define it with [ordered] explicitly.

Otherworldly