Powershell export data to CSV with columns

Question

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!

score 1 · Accepted Answer · answered Jan 21 '22 at 11:50

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() 
        } 
    } 
}

I just had to add –UseCulture to the two export lines and then it got me the results I hoped for. Thanks @Mathias :-) $data | Export-Csv -LiteralPath $datafile -NoTypeInformation –UseCulture -Append — Bowerteng, Jan 24 '22 at 09:51

Powershell export data to CSV with columns

1 Answers1