PowerShell – How to break a row with array into mulitple rows

powershell

I am writing a PowerShell script to export users' information including the SharePoint Group(s) he or she belong to. Part of script show below:

$web = Get-SPWeb $webURL
$users = Get-SPUser -Web $web.Url
foreach($user in $users){
   $staffObject = New-Object PSObject -Property @{
                'UserID' = $user.LoginName
                'Groups' = $user.Groups
                'Join Date' = ""
                'Referal' = ""
            }
$resultsarray += $staffObject 
}

$resultsarray | Select UserID,@{Name="Groups";Expression={$_."Groups" -join '|'}},'Join Date','Referral' | Export-csv -path $sFileLocation -Append -NoTypeInformation

Right now the output is like:

"UserID","Groups","Join Date","Referal"
"123","Group A|Group B|Group C","",""

How can I change to:

"UserID","Groups","Join Date","Referal"
"123","Group A","",""
"123","Group B","",""
"123","Group C","",""

Many thanks.

Best Answer

Does this help?

$CSV = Import-Csv C:\Users\Ryan\Downloads\test.txt
$ReformattedUsers = @()
Foreach ($User In $CSV)
{
    Foreach ($Group In $User.Groups.Split('|'))
    {
        $ReformattedUsers += [PSCustomObject]@{ 'UserID' = $User.UserID; `
                                                'Groups' = $Group; `
                                                'Join Date' = $User.'Join Date'; `
                                                'Referal (sic)' = $User.Referal  }
    }
}

$ReformattedUsers | Format-Table -AutoSize

$ReformattedUsers | Export-Csv C:\Users\Ryan\Downloads\out.csv -NoTypeInformation

Output:

UserID Groups  Join Date Referal (sic)
------ ------  --------- -------------
123    Group A                        
123    Group B                        
123    Group C

The resulting CSV looks like:

"UserID","Groups","Join Date","Referal (sic)"
"123","Group A","",""
"123","Group B","",""
"123","Group C","",""
Related Topic