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

I am looking at this but I am curious if you have tried a write-host of $NewLine to be sure it has something to write? — dcaz
– dcaz, Commented Jan 3, 2022 at 14:38

Santiago Squarzon · Accepted Answer · 2022-01-03 15:11:44Z

3

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

edited Jan 3, 2022 at 15:11

answered Jan 3, 2022 at 14:46

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Empty Coder Over a year ago

Got that. appreciated.

Theo Over a year ago

Great edit! (+1)

Santiago Squarzon Over a year ago

@Theo thanks Theo, I saw your comment on dcaz's answer which made me realize my mistake.

Empty Coder Over a year ago

@SantiagoSquarzon: I am getting this error now in the script for remote servers

Cannot validate argument on parameter 'Path'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.     + CategoryInfo          : InvalidData: (:) [Get-Acl], ParameterBindingValidationException     + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.GetAclCommand     + PSComputerName

Santiago Squarzon Over a year ago

@EmptyCoder does the $env:PATH variable on that computer look ok? Try adding -ne '' to avoid possible empty paths $paths_list = $env:Path -Split [System.IO.Path]::PathSeparator -ne ''

dcaz · Accepted Answer · 2022-01-03 14:52:51Z

1

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

answered Jan 3, 2022 at 14:52

dcaz

91911 silver badges28 bronze badges

1 Comment

Theo Over a year ago

Everything above $serverlist = Get-Content -Path "C:\ServerList.txt" is redundant, but mainly: this is collecting info from the callers local machine only and has nothing to do with what is on the remote servers..

ZivkoK · Accepted Answer · 2022-01-04 15:40:22Z

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

Collectives™ on Stack Overflow

Not able to add data to csv file powershell

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related