Not able to add data to csv file powershell

January 3, 2022

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

>Solution :

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.

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

# Collect results here
$result = foreach($server in $serverlist)
{
    $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]@{
                ServerName       = $server
                SystemFolderPath = $sys_Path
                IdenityReference = $acl.IdentityReference.Value
                FileSystemRights = $acl.FileSystemRights
            }
        }
    }
}

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

Regarding $serverlist, if you will run this on remote hosts you would need to use Invoke-Command and the outer loop wouldn’t be needed:

# 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
                ServerName       = $server
                SystemFolderPath = $sys_Path
                IdenityReference = $acl.IdentityReference.Value
                FileSystemRights = $acl.FileSystemRights
            }
        }
    }
} -HideComputerName

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