Powershell pscustomobject with array properties to Csv file

Question

I got an $Object with a ton of properties which look like this:

IsSynchronized   : { False, False }

What I want is to do something like :

$Object | Export-Csv C:\Test\Merge.csv -Delimiter ';'

To get a CSV containing :

IsSynchronized
--------------
False
False

But as expected I get

IsSynchronized
---------------
System.Object[]

Is there a good way to get a ton of the object's properties in a .CSV ?

I have 6 .csv files with values and I try to add them in to one big .csv for further processing.

Edit: I asked about this Topic yesterday but i need to make more clear what i want.

I create a PSCustomObject and fill it with Arrays:

$Object = New-Object -TypeName PSCustomObject

$Object | add-member -membertype NoteProperty -name "CPUHost"  -value $global:CPUHost
$Object | add-member -membertype NoteProperty -name "NumCpu"   -value $global:NumCpu
$Object | add-member -membertype NoteProperty -name "MemoryMB"  -value $global:MemoryMB
$Object | add-member -membertype NoteProperty -name "CPU Usage (Average), Mhz"  -value  $global:CPUUsageAverageMhz
$Object | add-member -membertype NoteProperty -name "CPU Usage (Average), %"  -value  $global:CPUUsageAverage
$Object | add-member -membertype NoteProperty -name "Memory Usage (Average), %"  -value $global:MemoryUsageAverage
$Object | add-member -membertype NoteProperty -name "Network Usage (Average), KBps"  -value  $global:NetworkUsageAverageKBps
$Object | add-member -membertype NoteProperty -name "Disk Usage (Average), KBps"  -value  $global:DiskUsageAverageKBps
...

All of these Global Variables are Arrays because i never know how many Values i get in the first place. They are filled by lopping through 6 CSV Files i will allways get.

After running this bit i will have a Object looking like this:

CPUHost                        : {xxxx}
NumCpu                         : {20}
MemoryMB                       : {36094}
CPU Usage (Average), Mhz       : {3914,33}
CPU Usage (Average), %         : {8,91}
Memory Usage (Average), %      : {70,17}
Network Usage (Average), KBps  : {439,68}
Disk Usage (Average), KBps     : {1994,93}
...

What i want is to Export that in to a CSV Displayed like :

CPUHost NumCPU MemoryMb CPUUsage  ...
------- ------ -------- ---------
xxxx     20      36094    3914      
                          33

With every Value in its own Cell.

What i get is instead of the values : System.Type.[] which is technically correct but not what i need.

I allready tryed to -join the values but that will leave me with the values in the same cell

@BjörnTrier You seem to be mixing presentation and storage formats - if you store the data like you suggest in a CSV file and someone sorts the rows, your data will be meaningless. Who is the intended audience for the stacked view? — Mathias R. Jessen
– Mathias R. Jessen, Commented Sep 8, 2020 at 9:15

AdminOfThings · Accepted Answer · 2020-09-07 13:04:07Z

1

You state that you have one object with multiple properties like what was posted. I assume you have an object that looks like the following:

IsSynchronized Property2 Property3
-------------- --------- ---------
{False, False} {1, 2, 3} {string1, string2, string3, string4}

You could do the following:

$loopmax = $object[0].psobject.properties |% {($_.Value | measure-object).Count} | Sort -desc | Select -First 1
$newobject = for ($i = 0; $i -lt $loopmax; $i++) {
    $hash = [ordered]@{}
    foreach ($p in $object[0].psobject.properties.name) {
        $hash[$p] = $object.$p[$i]
    }
    [pscustomobject]$hash
}
$newobject | convertto-csv -notype

answered Sep 7, 2020 at 13:04

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

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

Comments

Steven · Accepted Answer · 2020-09-10 15:20:18Z

1

This doesn't seem wise to do. CSV isn't very good for representing or storing rich or hierarchical objects. The problem is even worse when the property is an object, in your case it's a flat array. You can output as you suggest / request, and other answers have demonstrated:

IsSynchronized
--------------
False
False

However, this disrupts the property's relationship to the other properties. If you have other types like [String] & [Int], or even varying numbers of elements in array typed properties things are going to get weird quickly!

If you must stick with CSV you can sub-delimit the field. A great example of this is Exchange Message Tracking logs. They are CSV files delimited on the typical ",", but the recipients field is sub-delimited on a ";".

An example in code might look something like this:

$Object = 
[PSCustomObject]@{
    Prop1 = "one"
    Prop2 = "two"
    Arr1  = @( 1,2,3,4 )
}

$Object | 
Select-Object Prop1, Prop2,
    @{ Name = 'Arr1'; Expression = {$_.Arr1 -join "," } } |
ConvertTo-Csv -Delimiter ";"

Results:

"Prop1";"Prop2";"Arr1"
"one";"two";"1,2,3,4"

Note: To use full fidelity data in a later process would require appropriate handling on input. However, if you follow other solutions and said weirdness occurs you'll be left with a similar issue; having to handle on the input side everywhere you intend to use that data.

Given CSV's shortcomings JSON may be a better choice to store & reuse full fidelity objects. Export/Import CliXML are interesting for this.

edited Sep 10, 2020 at 15:20

answered Sep 7, 2020 at 14:26

Steven

7,1271 gold badge17 silver badges16 bronze badges

4 Comments

Björn Trier Over a year ago

The thing is in a later step i have VBA Macro in a Word File which i programmed to read CSV Data and Fill Tables within the Wordfile with the Data. But the Conditions changed, they want a Second Function to do the same with diffrent Data which they give me in 6 CSV Files which i now try to merge in to one. Due to the fact that the Values are not allways the same lenght like there can be 1-6 lines i cant place them underneath eatch other because in the VBA Macro i grab certaint Colums and say take the header as sepreate factor and the values as array to later fill them in with these information

Björn Trier Over a year ago

I think of creating a empty CSV and i allready got every value in a array. I think i could give them a static name and add the values dynamic through looping all csv files and writing there propertys as arrays in variables

Björn Trier Over a year ago

Exporting it as XML still has no Values just : System.Object[]

Steven Over a year ago

I didn't test the Export-CliXML command, but if it's anything like ConvertTo-Json` you may need to play with the -Depth parameter. That said, it sounds like you'll need to rewrite the VBA/Word side regardless...

mclayton · Accepted Answer · 2020-09-07 12:54:39Z

0

To answer the literal question asked (although I'm not sure that's what the OP actually wants)...

If you have a single object with a property that contains an array of values, you can expand them out into a new array and then convert that to csv as follows:

$obj = new-object psobject -Property @{
    "IsSynchronized" = @( $false, $false )
}

$obj
# IsSynchronized
# --------------
# {False, False}

$data = $obj.IsSynchronized | foreach-object {
    new-object pscustomobject -Property @{ "IsSynchronized" = $_ }
}

$csv = $data | ConvertTo-Csv -NoTypeInformation

$csv
# "IsSynchronized"
# "False"
# "False"

answered Sep 7, 2020 at 12:54

mclayton

10.4k3 gold badges26 silver badges36 bronze badges

Collectives™ on Stack Overflow

Powershell pscustomobject with array properties to Csv file

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related