Export-Csv Producing Empty Output File

Question

I've got a script to audit local user accounts. Everything works, except exporting to CSV. The output file is blank, except for the column headers:

Computer	Name	Enabled	PasswordChangeableDate	PasswordExpires	UserMayChangePassword	PasswordRequired	PasswordLastSet	LastLogon

What am I doing wrong here? The export portion is towards the bottom.

$Result = New-Object System.Collections.Generic.List[System.Object] #store local user data
$Errors = New-Object System.Collections.Generic.List[System.Object] #store any devices that could not be reached

$devices = Get-Content "list.txt" #read in all devices to check

#for progress bar - store current # and total
$length = $devices.Count
$i = 1

$displayOutputReport = $true

foreach ($device in $devices){
    Write-Progress -Activity "Retrieving data from $device ($i of $length)" -percentComplete  ($i++*100/$length) -status Running

    if (Test-Connection -ComputerName $device -Count 1 -Quiet){
        #Get account properties and add to list

         $temp = Invoke-Command -ComputerName $device -ScriptBlock {
             Get-LocalUser |  Select-Object -Property @{N="Computer"; E={$env:COMPUTERNAME}}, Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon
         }
         $Result.Add($temp)

    }else{
        $errors.Add($device)
        Write-Host "Cannot connect to $device, skipping." -ForegroundColor Red
    }
}

#display results
if ($displayOutputReport){
    if ($Result.Count -gt 0){
        $Result | Format-Table Computer,Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon -AutoSize
    }

    Write-Host ""

    if ($Errors.Count -gt 0){
        Write-Host "Errors:" -ForegroundColor Red
        $Errors
        Write-Host ""
    }
}

#export to CSV
$ScriptPath = $pwd.Path
$ReportDate = (Get-Date).ToString('MM-dd-yyyy hh-mm-ss tt')

if($Result.Count -gt 0){
    $reportFile = $ScriptPath + "\LocalUserAudit $ReportDate.csv"
    $Result | Select-Object Computer, Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon | Export-Csv $reportFile -NotypeInformation
    Write-Host "Report saved to $reportFile"
}

if ($Errors.Count -gt 0){
    $errorFile = $ScriptPath + "\LocalUserAudit ERRORS $ReportDate.txt"
    $Errors | Out-File $errorFile
    Write-Host "Errors saved to $errorFile"
}

No luck. It repeats the column headers 4 times vertically (since that's the # of devices in my test list.txt) — Daniel, Feb 26 '21 at 19:04

mklement0 · Accepted Answer · 2021-02-26T19:28:19.637

Change

$Result.Add($temp)

to

$Result.AddRange(@($temp))

to ensure that $Result ends up as a flat collection of objects.

As for what you tried:

$temp is likely to be an array of objects, as Get-LocalUser likely reports multiple users.

I you pass an array to List`1.Add(), it is added as a whole to the list, which is not your intent.

Therefore you ended up with a list of arrays rather than a flat list of individual objects. And because an array doesn't have the properties you were trying to select (Name, Enabled, ...) the column values for those properties in the output CSV ended up empty.

By contrast, List`1.AddRange() accepts an enumerable of objects (which an array implicitly is), and adds each enumerated object directly to the list.

The use of @(...) around $temp guards against the case where Get-LocalUser happens to return just one object - @(), the array-subexpression operator, then ensures that it is treated as an array.

A better alternative:

If you use the foreach loop as an expression, you can let PowerShell do the work of collecting all output objects in flat array, thanks to the pipeline's automatic enumeration of collections:

# Assign directly to $Result
$Result = foreach ($device in $devices){
    Write-Progress -Activity "Retrieving data from $device ($i of $length)" -percentComplete  ($i++*100/$length) -status Running

    if (Test-Connection -ComputerName $device -Count 1 -Quiet){
        #Get account properties and add to list

         # Simply pass the output of the Invoke-Command call through.
         Invoke-Command -ComputerName $device -ScriptBlock {
             Get-LocalUser |  Select-Object -Property @{N="Computer"; E={$env:COMPUTERNAME}}, Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon
         }

    }else{
        $errors.Add($device)
        Write-Host "Cannot connect to $device, skipping." -ForegroundColor Red
    }
}

This is not only more convenient, but also performs better.

Note: If you need to ensure that $Result is always an array, use [array] $Result = foreach ...

score 0 · Answer 2 · answered Feb 26 '21 at 19:13

0

Update: I've got it working, but can someone explain why this works instead? I'd prefer to avoid concatenating arrays when this will be run thousands of times.

I changed

$Result = New-Object System.Collections.Generic.List[System.Object]

to

$Result = @()

And

$temp = Invoke-Command -ComputerName $device -ScriptBlock {
         Get-LocalUser |  Select-Object -Property @{N="Computer"; E={$env:COMPUTERNAME}}, Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon
     }
     $Result.Add($temp)

To

 $Result += Invoke-Command -ComputerName $device -ScriptBlock {
         Get-LocalUser |  Select-Object -Property @{N="Computer"; E={$env:COMPUTERNAME}}, Name, Enabled, PasswordChangeableDate, PasswordExpires, UserMayChangePassword, PasswordRequired, PasswordLastSet, LastLogon
    }

answered Feb 26 '21 at 19:13

Daniel

476
6
21

As for why `+=` with an array works: `+`, when applied to two arrays performs _element-wise_ concatenation, so that `1,2 + 3, 4` creates a new, flat array `1, 2, 3, 4`. – mklement0 Feb 26 '21 at 19:25
That said, iteratively building up arrays with `+=` should be avoided for performance reasons, and simply using a `foreach` loop to let PowerShell collect all outputs in an array is both more convenient and much faster: see the bottom section of the accepted answer and, for more details, including performance measurements, [this answer](https://stackoverflow.com/a/60708579/45375). – mklement0 Feb 26 '21 at 19:33
In general, if you want to avoid the penalties of copying in memory, you can append incremental data to a file. – Feb 27 '21 at 09:35

Export-Csv Producing Empty Output File

2 Answers2