In Powershell I'm trying to export a list of all users in an OU (and sub OU's) and which group memberships they have to a csv. I can get it exported, but it's not adding the commas between the groups. Here's my code, any suggestions would be appreciated.

Code:

$Path = 'C:\Users\myadmin\dl_finder.csv'
Get-ADUser -Filter * -SearchBase 'OU=Routine,OU=Disabled Users,OU=Site Users,DC=nunya,DC=com' -Properties MemberOf |Select-Object SamAccountName, (@{n='memberOf';','e={$_.memberOf -replace '^CN=([^,]+).+$','$1'}}) | Export-Csv -Path $Path –notypeinformation

Result:

"wakko","Group1 Group2 Group3 Group4 Group5 Group6 Group7 Group8"
"yakko","Group1 Group2 Group3 Group4 Group5 Group6 Group7 Group8 Group9 Group10"
"dot","Group1 Group2 Group3 Group4 Group5 Group6"

Expectation:

"wacko","Group1,Group2,Group3,Group4,Group5,Group6,Group7,Group8"
"yakko","Group1,Group2,Group3,Group4,Group5,Group6,Group7,Group8,Group9,Group10"
"dot","Group1,Group2,Group3,Group4,Group5,Group6"

In Powershell I'm trying to export a list of all users in an OU (and sub OU's) and which group memberships they have to a csv. I can get it exported, but it's not adding the commas between the groups. Here's my code, any suggestions would be appreciated.

Code:

$Path = 'C:\Users\myadmin\dl_finder.csv'
Get-ADUser -Filter * -SearchBase 'OU=Routine,OU=Disabled Users,OU=Site Users,DC=nunya,DC=com' -Properties MemberOf |Select-Object SamAccountName, (@{n='memberOf';','e={$_.memberOf -replace '^CN=([^,]+).+$','$1'}}) | Export-Csv -Path $Path –notypeinformation

Result:

"wakko","Group1 Group2 Group3 Group4 Group5 Group6 Group7 Group8"
"yakko","Group1 Group2 Group3 Group4 Group5 Group6 Group7 Group8 Group9 Group10"
"dot","Group1 Group2 Group3 Group4 Group5 Group6"

Expectation:

"wacko","Group1,Group2,Group3,Group4,Group5,Group6,Group7,Group8"
"yakko","Group1,Group2,Group3,Group4,Group5,Group6,Group7,Group8,Group9,Group10"
"dot","Group1,Group2,Group3,Group4,Group5,Group6"

• powershell
• active-directory

2 Answers 2

Use the -join operator to explicitly join multiple strings with a separator:

... |Select-Object SamAccountName,@{Name='memberOf';Expression={@($_.memberOf -replace '^CN=([^,]+).+$','$1') -join ','}}

• The object you're passing to Export-Csv has one array-valued property, .memberof

• Generally, Export-Csv (and its in-memory counterpart, ConvertTo-Csv) does not meaningfully serialize properties containing arrays to CSV, and it is up to you to choose a custom string representation (which the consumer of the CSV must be aware of).

  • It is only an implementation detail that, as a happy accident, happened to create a space-separated representation of your array,^[1] because you used a calculated property in your Select-Object to create the array property, which wrapped the array in an (otherwise invisible) [psobject] instance.^[2]

  • You cannot rely on such a wrapper always being present, however; for instance, creating a similar object via a [pscustomobject] literal and an unwrapped array causes the array to be uselessly serialized as verbatim System.Object[] in the CSV:

    # !! Property .Baz serializes uselessly as "System.Object[]"
    [pscustomobject] @{ Foo = 'bar'; Baz = @(1, 2, 3) } | ConvertTo-Csv
    

    • Simulating the [psobject] wrapper results in the space-separated serialization:

      # Due to the [psobject] wrapper, property .Baz now serializes as "1 2 3"
      [pscustomobject] @{ Foo = 'bar'; Baz = [psobject] @(1, 2, 3) } | ConvertTo-Csv
      

• Therefore, use -join, the string-joining operator operator with a separator of your choice to create a string representation of your array; since you want , as the separator, replace the following part of your calculated property:

  $_.memberOf -replace '^CN=([^,]+).+$', '$1'
  

  • with (note the addition of -join ','; (...) isn't strictly necessary here, but is used for clarity):

     ($_.memberOf -replace '^CN=([^,]+).+$', '$1') -join ','
    

^{[1] Note that this is the array stringification that you get by default in the context of PowerShell's string interpolation (expandable strings), e.g. $arr = @(1, 2, 3); "$arr", though you can override space as the separator via the $OFS preference variable. $OFS does not apply in the context of Export-Csv / ConvertTo-Csv, however.}

^{[2] See this answer for background information.}