Powershell Export-to-CSV of array providing array properties

Question

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?

mklement0 · Accepted Answer · 2024-01-23 22:44:37Z

6

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.

edited Jan 23, 2024 at 22:44

answered May 25, 2017 at 17:30

mklement0

452k68 gold badges727 silver badges986 bronze badges

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

2 Comments

Matt Over a year ago

I can't up vote more. Answer is top notch like always. How dare you question the documentation though :)

not2qubit Over a year ago

Here's a related magic pipe solution, starting with a list and doing some additional CSV magic.

thiyagu selvaraj · Accepted Answer · 2018-11-21 04:52:54Z

1

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 $_ }

answered Nov 21, 2018 at 4:52

thiyagu selvaraj

1004 bronze badges

Collectives™ on Stack Overflow

Powershell Export-to-CSV of array providing array properties

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related