export to csv powershell script using multiple foreach statements

Question

I have following powershell script reading from csv and exporting to another csv. It's working in terms of basic functionality. Script below is currently exporting as such:

USERS
jdoe
mprice
tsmith

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue
# csv file name
[parameter(Mandatory=$false)][string]$CsvFilePath = ".\AllSiteCollectionsLocal.csv"
$csvItems = Import-Csv $CsvFilePath
$resultsarray = @()
$firstObject = New-Object PSObject

# iterate lines in csv
foreach($Item in $csvItems)
{   
    $site = new-object Microsoft.SharePoint.SPSite($Item.SiteCollection)
    $web = $site.openweb()
    $siteUsers = $web.SiteUsers
    Write-Host $Item.SiteCollection -ForegroundColor Green
    
    foreach($user in $siteUsers)
    {
        Write-Host $user.LoginName
        
        $loginnames = @{
            USERS = $user.LoginName
        }
        $resultsarray += New-Object PSObject -Property $loginnames
    }
    $web.Dispose()
    $site.Dispose()
    $resultsarray | export-csv -Path c:\temp\sitesandusers.csv -NoTypeInformation
}

I need to export as below. Note, I dont even need a header, but do need $Item.SiteCollection value to print out between each iteration of users under each site, so the outer foreach needs to print $Item.SiteCollection then the inner foreach would print $user.LoginName


http://test1.com
jdoe
mprice
http://test2.com
tsmith

Abraham Zinala · Accepted Answer · 2022-06-10 21:54:14Z

I'm guessing you wanted to do parameters for your script to be called from elsewhere? As of now, your metadata attribute on $CsvFilePath are redundant to what PowerShell already does for you.

As for your question, you would just have to append $Item.SiteCollection to your PSObject. This too isn't needed as PowerShell streaming capabilities allow you to assign directly to a variable; so no need for += - which can be computationally expensive on larger lists slowing overall performance. Now we end up with:

Param (
    [parameter(Mandatory=$false)]
    [string]$CsvFilePath = ".\AllSiteCollectionsLocal.csv"
) 
Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue

$csvItems = Import-Csv $CsvFilePath
$variable = foreach($Item in $csvItems)
{   
    $site = new-object Microsoft.SharePoint.SPSite($Item.SiteCollection)
    $web  = $site.openweb()
    $siteUsers = $web.SiteUsers
    Write-Host -Object $Item.SiteCollection -ForegroundColor Green
    Write-Output -InputObject $Item.SiteCollection
    
    foreach($user in $siteUsers)
    {
        Write-Host -Object $user.LoginName
        Write-Output -InputObject $user.LoginName
    }
    $null = $web.Dispose()
    $null = $site.Dispose()
}
$variable | Out-File -FilePath 'c:\temp\sitesandusers.csv'

Bypassing $variable you can assign the output directly to the file placing the export outside the first foreach statement.

This requires the use of a sub-expression operator $() to wrap around the loop.
Also added a Param ( ) statement for your parameter declaration.
- Didn't mess with the parameter attributes as it can show the Authors intentions regardless if it's needed or not.

Probably should add that, Write-Output will explicitly write to the success stream allowing the values to be assigned to the variable, whereas Write-Host writes to the information stream, so no object pollution (duplicates) occur.

Collectives™ on Stack Overflow

export to csv powershell script using multiple foreach statements

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related