Powershell Foreach overwrites the output in excel

Question

** Update i made it work i needed to put the entire foreach inside a variable and it works

I have a script need help fixing it , the excel output is giving me the last output and overwrite the outputs , in powershell it gives the correct and shows all of the output

Import-Module ImportExcel
$FolderPath = "\\clalit\dfs$\HomeFS\Ram Idan Scripts"
$Acl = Get-Acl -Path $FolderPath
$AccessRules = $Acl.Access  
    foreach ($Rule in $AccessRules) {
    try{
        $Username = ($Rule.IdentityReference.Tostring().split("\")[1])
        $Permissions = ($Rule.FileSystemRights.Tostring().split(",")[0])
        $Email = (Get-ADUser $Username -Properties mail).mail
        $all = Get-ADUser $Username -Properties DisplayName,SamAccountName,mail  
        
    
        if( $Permissions -match "Modify"){
           
            $all | Select DisplayName,SamAccountName,mail 
            
            
        }
             
          

        else{}
            
        
       
    }catch{$nul}
   
    
}
 
$all | Select DisplayName,SamAccountName,mail | Export-Excel "$FolderPath\tests.xlsx" -FreezeTopRow -AutoSize

For starers, $Username = ($Rule. uses uninitialised variable. Fix that, and reformat the code, there's a lot of excess whitespace. — vonPryz
– vonPryz, Commented Jul 4, 2023 at 6:31
If I get what you are trying to accomplish, you want all the output that shows in the terminal? If so, you may need to have some type of array to add them into like $allOutput += $all and then export it to excel. — Kelv.Gonzales
– Kelv.Gonzales, Commented Jul 4, 2023 at 7:09
sorry for the question how do i exactly i am kinda new to powershell im learning on the job for fun — Mr.RamGoat
– Mr.RamGoat, Commented Jul 4, 2023 at 7:16

Kelv.Gonzales · Accepted Answer · 2023-07-07 06:13:28Z

I have roughly composed the concept I proposed and have yet to test it but hoped to guide you in your learning.

Import-Module ImportExcel
$FolderPath = "\\clalit\dfs$\HomeFS\Ram Idan Scripts"
$Acl = Get-Acl -Path $FolderPath
# This is the array of output where you will keep adding the results
$allOutput = @{}
$AccessRules = $Acl.Access  
foreach ($Rule in $AccessRules) {
try{
    $Username = ($Rule.IdentityReference.Tostring().split("\")[1])
    $Permissions = ($Rule.FileSystemRights.Tostring().split(",")[0])
    $Email = (Get-ADUser $Username -Properties mail).mail
    $all = Get-ADUser $Username -Properties DisplayName,SamAccountName,mail  

    if( $Permissions -match "Modify"){
       # Assuming this is the only output you want to export all the time,we place it into the array.
       $allOutput += $all
        
    }          

    else{}       
   
}catch{$nul}
}
# Then you will just have to export the array into the excel
$allOutput | Export-Excel "$FolderPath\tests.xlsx" -FreezeTopRow -AutoSize

Collectives™ on Stack Overflow

Powershell Foreach overwrites the output in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related