Not able to add data to csv file powershell

Question

I am trying to add data to an csv file.

I am creating the csv with header first and then trying to add the rows. but it is returning blank csv file

                $props=[ordered]@{
                 ServerName=''
                 SystemFolderPath=''
                 IdenityReference=''
                 FileSystemRights=''
                 
            }
            New-Object PsObject -Property $props | 
                 Export-Csv "C:\status_report.csv" -NoTypeInformation


$serverlist = Get-Content -Path "C:\ServerList.txt"

foreach($server in $serverlist)
{

                $paths_list = $env:Path -Split ';'
                
            Foreach ($sys_Path in $paths_list)
            {

                $Permissions = Get-Acl -Path $sys_Path
                $Users_Permissions = $Permissions.Access  | Where-Object {$_.IdentityReference}
                #$Users_Permission
                        Foreach  ($user in $Users_Permissions)
                        {
                                $IdenityReference = $user.IdentityReference.Value
                                $FileSystemRights = $user.FileSystemRights
                                
                                $NewLine = "{0},{1},{2},{3}" -f $server,$sys_Path,$IdenityReference,$FileSystemRights
                                $NewLine | Export-Csv -Path "C:\status_report.csv" -Append -NoTypeInformation -Force

                        }

            }
}

Please let me know what I am doing wrong here

Answer 1

Santiago's answer is correct and contains all the required information for you to understand the issue you have here.

I just wanted to provide you with the minimum modifications to be done in your script:

• Replace the $props custom object by a function (i.e CreateCustomObject)

   function CreateCustomObject($val1, $val2, $val3, $val4) {
       $NewObject = New-Object PSObject ;
       Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "ServerName" -Value $val1 ;
       Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "SystemFolderPath" -Value $val2  ;
       Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "IdenityReference" -Value $val3  ;
       Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "FileSystemRights" -Value $val4  ;
       return $NewObject ;
  
   }
  

• Replace the String Variable $NewLine by an Array

   $NewLine = @()
   $NewLine += CreateCustomObject $server $sys_Path $IdenityReference $FileSystemRights
  

• Write to CSV only once data is collected (move the command to the end of the script)

So the final script will look something like that:

    function CreateCustomObject($val1, $val2, $val3, $val4) {
        $NewObject = New-Object PSObject ;
        Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "ServerName" -Value $val1 ;
        Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "SystemFolderPath" -Value $val2  ;
        Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "IdenityReference" -Value $val3  ;
        Add-Member -InputObject $NewObject -MemberType NoteProperty -Name "FileSystemRights" -Value $val4  ;
        return $NewObject ;

    }
    
    $serverlist = Get-Content -Path "C:\Temp\ServerList.txt"
    $NewLine = @()

    foreach($server in $serverlist) {

        $paths_list = $env:Path -Split ';'
            
        Foreach ($sys_Path in $paths_list) {
            $Permissions = Get-Acl -Path $sys_Path
            $Users_Permissions = $Permissions.Access  | Where-Object {$_.IdentityReference}
            #$Users_Permission
            Foreach  ($user in $Users_Permissions) {
                $IdenityReference = $user.IdentityReference.Value
                $FileSystemRights = $user.FileSystemRights
                $NewLine += CreateCustomObject $server $sys_Path $IdenityReference $FileSystemRights
            }

        }
    }

    $NewLine | Export-Csv -Path "C:\temp\status_report.csv" -NoTypeInformation -Force

Answer 2

The main reason why you're seeing this is because Export-Csv expects an object or object[] through the pipeline and you're passing a formatted string instead. This is specified on MS Docs:

Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties.

PS /> 'server01,C:\Windows,Computer\User,FullControl' | ConvertTo-Csv
"Length"
"45"

Instead of appending to a CSV which is quite inefficient, unless there is a specific need for this, what you will want to do is collect the results first and then export them.

I'm not too sure why | Where-Object { $_.IdentityReference } is needed, I left it there but I don't think it's needed.

Regarding $serverlist, if you will run this on remote hosts you would be better of using Invoke-Command since it allows parallel invocations. The outer loop wouldn't be needed in that case:

$serverlist = Get-Content -Path "C:\ServerList.txt"

# Collect results here
$result = Invoke-Command -ComputerName $serverlist -ScriptBlock {
    $paths_list = $env:Path -Split [System.IO.Path]::PathSeparator
    foreach($sys_Path in $paths_list)
    {
        $Permissions = (Get-Acl -Path $sys_Path).Access
        foreach($acl in $Permissions)
        {
            if(-not $acl.IdentityReference)
            {
                continue
            }   

            [pscustomobject]@{
                ComputerName     = $env:ComputerName
                SystemFolderPath = $sys_Path
                IdenityReference = $acl.IdentityReference.Value
                FileSystemRights = $acl.FileSystemRights
            }
        }
    }
} -HideComputerName

$result | Export-Csv -Path "C:\status_report.csv" -NoTypeInformation

Answer 3

Accept Santiago above but this is what I did with what you wrote.

$props = [ordered]@{
    ServerName       = ''
    SystemFolderPath = ''
    IdenityReference = ''
    FileSystemRights = ''
    
}
New-Object PsObject -Property $props | 
Export-Csv "C:\status_report.csv" -NoTypeInformation
$serverlist = Get-Content -Path "C:\ServerList.txt"
$result = $serverlist | ForEach-Object {

    foreach ($server in $_) {
        $paths_list = $null
        $paths_list = $env:Path -Split ';'
   
        Foreach ($sys_Path in $paths_list) {
            $Permissions = Get-Acl -Path $sys_Path
            $Users_Permissions = $Permissions.Access  | Where-Object { $_.IdentityReference }
            #$Users_Permission
            Foreach ($user in $Users_Permissions) {
                $IdenityReference = $null
                $FileSystemRights = $null
                $IdenityReference = $user.IdentityReference.Value
                $FileSystemRights = $user.FileSystemRights
                   
                [PSCustomObject]@{
                    Server     = $server
                    Sys_Path   = $sys_Path
                    Referecent = $IdenityReference
                    Rights     = $FileSystemRights
                }
                $sys_Path = $null
            }
        }
    }
}
$result | Export-Csv -Path "C:\status_report.csv" -NoTypeInformation