PowerShell - Add variables with multiple values to CSV column

Question

I have a weird scenario where I am trying to populate a .CSV file using PowerShell, but it's not a traditional .CSV file. I want to have two columns with multiple values in a single cell.

Desired CSV Out:

Team Name           Team Users           Team Access
Team 1              User 1               App 1
                    User 2               App 2
                    User 3               App 3 
Team 2              User 4               App 1
                    User 5               App 2
                    User 6               App 3

So essentially, for the above, I would only want two rows with all that information.

When I try to use Export-CSV, with defined variables for the Users and Apps, my CSV looks like this:

Team Name           Team Users           Team Access
Team 1              System.Object[]      System.Object[] 
Team 2              System.Object[]      System.Object[]

So I'm wondering how I can either take the object values as literal values or use something like Add-Content / Add-Member to add values based on the CSV headers?

This is my current code:

$RBAC_Groups = Get-ADGroup -Filter {Name -like "RBAC*"} -SearchBase "DC=example,DC=com"


ForEach ($Group in $RBAC_Groups) {
    
    #Get AD Group Members
    $Users = Get-ADGroupMember -Identity $Group.Name

    #Get AD Group Member Of 
    $Membership = Get-ADPrincipalGroupMembership $Group.Name

    $Final = New-Object psobject

    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Group" -Value $Group.Name
    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Users" -Value $Users.Name
    $Final | Add-Member -MemberType NoteProperty -Name "RBAC Access" -Value $Membership.Name

    $Final | Export-Csv -Path C:\Temp\RBAC_Weekly_Audit.csv -Append -NoTypeInformation
}

Thanks,

Are you sure you want it to look like that? As you mentioned, it's not traditional and will also be incompatible with Excel filters. — Santiago Squarzon, Dec 23 '21 at 17:55
Hi @iRon, I have added my code to the question, let me know if this helps. — script_newbie129, Dec 23 '21 at 18:31
@SantiagoSquarzon, yeah its a strange ask, but essentially we want to audit / create a report for an RBAC group, the members in the group, and the access they have. So the group name would only ever be 1 value, while the users/access will be multiple values. — script_newbie129, Dec 23 '21 at 18:31
I honestly think you will be better off with a (somewhat) normalized CSV file. That is, produce a file with six rows, where each row has the team name explicitly in the first field. You will be able to read this CSV with excel, SQL Server, or even Poweshell very easily. You can form multivalued groups at report generation time. It's just simpler data management. — Walter Mitty, Dec 24 '21 at 12:21

Santiago Squarzon · Accepted Answer · 2021-12-23T19:00:52.563

You just need to convert the array properties Team Users and Team Access into multi-line strings (being arrays is also why you see System.Object[] when you export your object).

How can to convert the array into multi-line string?

$array -join [environment]::NewLine
$array -join "`r`n"
($array | Out-String).TrimEnd()

And others...

$RBAC_Groups = Get-ADGroup -Filter "Name -like 'RBAC*'" -SearchBase "DC=example,DC=com"
$export = foreach($group in $RBAC_Groups)
{
    $members = @(Get-ADGroupMember -Identity $Group.Name).Where({
        $_.ObjectClass -eq 'user'
    })
    $principals = Get-ADPrincipalGroupMembership $Group.Name

    [pscustomobject]@{
        'RBAC Group'  = $group.Name
        'RBAC Users'  = $members -join [System.Environment]::NewLine
        'RBAC Access' = $principals -join [System.Environment]::NewLine
    }
}
$export | Export-Csv -Path C:\Temp\RBAC_Weekly_Audit.csv -NoTypeInformation

Using script block on the -Filter parameter for the AD cmdlets is not recommended and can bring problems.

How do I know if my property is an array?

When displaying the object in the console the values of your array property will be displayed between brackets {...}. .GetType() method and Get-Member can be used too to identify them:

PS /> $obj[0]

Team Name Team Users               Team Access
--------- ----------               -----------
Team 1    {User 1, User 2, User 3} {App 1, App 2, App 3}

PS /> $obj[0].'Team Users'.GetType()

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

PS /> $obj[0] | Get-Member -MemberType Properties

   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Team Access NoteProperty Object[] Team Access=System.Object[]
Team Name   NoteProperty string Team Name=Team 1
Team Users  NoteProperty Object[] Team Users=System.Object[]

@script_newbie129 happy to help, I would advise your leadership that this is not a good way to export data but they probably will not listen hehe — Santiago Squarzon, Dec 23 '21 at 20:25

PowerShell - Add variables with multiple values to CSV column

1 Answers1