CODEWITHSUNDEEP
powershellexport-to-csv
Ranger
Ranger

Reputation: 1174

Powershell Export-to-CSV of array providing array properties

This code:

$concerningMachines = @()
foreach($aMachine in $machineRecords.GetEnumerator())
{
    if($aMachine.Value.ReviewScore -gt $averageAboveAverage)
    {
        $machine = New-Object -TypeName PSObject
        $machine | Add-Member -MemberType NoteProperty -Name MachineName -Value $aMachine.Key
        $machine | Add-Member -MemberType NoteProperty -Name ManagedBy -Value $aMachine.Value.ManagedBy
        $machine | Add-Member -MemberType NoteProperty -Name LastLogin -Value $aMachine.Value.LastLogin
        $machine | Add-Member -MemberType NoteProperty -Name ReviewScore -Value ($aMachine.Value.ReviewScore / $averageAboveAverage * 100)
        $concerningMachines += $machine
    }
}

Export-Csv -InputObject $concerningMachines -Path C:\DG\ConcerningMachines.csv -NoTypeInformation

Produces this .csv file:

"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized" "43","43","43","1","System.Object[]","False","True","False"

...Which is the properties of the array, and not the members of the array. Googling the only advice I find seems to either implement a solution that entire ignores Export-Csv, is dealing with strings (which while collections of characters, the solutions aren't applicable) or appear to be doing exactly what I'm already doing but produce a valid CSV file (I can find some links if needed, but they're forum conversations, not explicit guides).

How do you properly export an array of PSObjects into a CSV files, and avoid the above unintended output?

Upvotes: 3

Views: 6565

Answers (2)

mklement0
mklement0

Reputation: 437090

Use a pipeline to provide the input:

$concerningMachines| Export-Csv -Path C:\DG\ConcerningMachines.csv -NoTypeInformation

When you pass the same input to -InputObject, it is indeed the array as a whole that is interpreted as a single object to export.

This behavior affects all cmdlets and is apparently as designed:

InputObject is a single object to be processed. When used in a pipeline, InputObject is bound to each element in the pipeline and processed one at a time. If InputObject was processed as a collection, then each item from the pipeline would also be processed as a collection.

There are cmdlets where this behavior is useful: Get-Member, for instance, allows you to use -InputObject to inspect the type of a collection as a whole, whereas pipeline input inspects each element of the collection.

Neither Export-Csv nor ConvertTo-Csv fall into that category, and even though Get-Help ExportCsv currently misleadingly describes the -InputObject parameter thus (emphasis added):

Specifies the objects to export as CSV strings. Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to Export-CSV.

The pragmatic conclusion is to always use the pipeline (unless you truly want to pass a collection as a whole).

For a general discussion of this behavior, see GitHub issue #4242.

Upvotes: 6

thiyagu selvaraj
thiyagu selvaraj

Reputation: 100

One of the best and easiest ways to put data into an easy-to-read format is with a CSV (comma-separated values ) file. The CSV file will have a line of headers to indicate column name and subsequent values for each column. The structured data is required for positioning in the CSV file

#looping the concerningMachines details to print in the excel cell
$concerningMachines | foreach { Add-Content -Path $FilePathLocation -Value $_ }

Upvotes: 1

Related Questions