powershellsharepointsharepoint-2016

Powershell export data to CSV with columns


I want to export all users of a SharePoint Farm and found the code below. I'm new to PowerShell, so the solution is probably easy, but I struggle with it.

The code works well except that it outputs the data in a single column with each row entry with the data all listed after each other ("type,user,group,weburl,webtitle"). I'd love it when it would output it into 5 columns kinda like this

type user group weburl webtitle
type 1 user 1 group 1 weburl 1 webtitle 1
type 1 user 2 group 2 weburl 1 webtitle 1
type 1 user 3 group 1 weburl 2 webtitle 2
Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue 
    
$Currentime = get-date -format "yyyyMMdd_hhmmtt" 
$filename = "FarmUsers" 
$datafile = ("{0}{1}.csv" -f $filename, $Currentime) 
    
$headerfile = "type,user,group,weburl,webtitle" 
$headerfile | out-file -FilePath $datafile 
    
$iissitedata = get-spwebapplication  
foreach($farmsite in $iissitedata) 
{ 
    foreach ($SiteCollection in $farmsite.sites) 
    { 
        foreach ($web in $SiteCollection.Allwebs) 
        {  
             foreach ($usersite in $web.users) 
             {       
                    $data = ("RootUser,{0},-,{1},{2}" -f $usersite, $web.url,$web.name)  
                    $data | out-file -FilePath $datafile  -append 
                } 
     
             foreach ($group in $web.Groups) 
            { 
                 foreach ($user in $group.users) 
                 {                           
                          $data = ("GroupUser,{0},{1},{2},{3}" -f $user, $group, $web.url, $web.name) 
                          $data | out-file -FilePath $datafile  -append 
                 } 
            }    
            $web.Dispose() 
        } 
    
    } 
}

What changes do I have to make to the script, so that it outputs into columns?

Thanks in advance!


Solution

  • Instead of manually formatting each row in the CSV, you'll want to create a series of objects with properties corresponding to the column names you want, and then let Export-Csv take care of constructing the CSV file for you:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 
        
    $Currentime = Get-Date -format "yyyyMMdd_hhmmtt" 
    $filename = "FarmUsers" 
    $datafile = ("{0}{1}.csv" -f $filename, $Currentime) 
        
    $iissitedata = Get-SPWebApplication  
    foreach ($farmsite in $iissitedata)
    { 
        foreach ($SiteCollection in $farmsite.sites)
        { 
            foreach ($web in $SiteCollection.Allwebs)
            {  
                foreach ($usersite in $web.users)
                {
                    $data = [pscustomobject]@{
                        type     = "RootUser"
                        user     = $usersite
                        group    = '-'
                        weburl   = $web.url
                        webtitle = $web.name
                    }
                    $data | Export-Csv -LiteralPath $datafile -NoTypeInformation -Append
                } 
         
                foreach ($group in $web.Groups)
                { 
                    foreach ($user in $group.users)
                    {                           
                        $data = [pscustomobject]@{
                            type     = "GroupUser"
                            user     = $user
                            group    = $group
                            weburl   = $web.url
                            webtitle = $web.name
                        }
                        $data | Export-Csv -LiteralPath $datafile -NoTypeInformation -Append
                    } 
                }    
                $web.Dispose() 
            } 
        } 
    }