How to include all properties of all array objects in csv in powershell

Question

I have a Powershell script that reads a text file with single line descriptions of shares and their properties.

It then, for each share, adds an object to an array, with properties.

I end up with an array of shares, many with common properties but also some that only appear on a few shares.

When I pipe the finished array to Export-CSV, the resulting CSV only has columns matching the properties of the first object in the array.

Any ideas/help gratefully received.

Here is the code:

$shares_only = gc \\server\share\SHARES.txt | where {$_ -match '^*TYPE = DISK'}

$share_index = @()

$shares_only |foreach {

    $sharename = ($_.Split(" ")[1])
    $_ -match '\([\w\s.,"=<>()\/\*\+\-]*\);'|out-null

    $shareparams = $matches[0]

    $paramlist = ($shareparams.Substring(1,($shareparams.Length -3))).Split(",")

    $obj = $null
    $obj = New-Object System.Object

    $obj | Add-Member -type NoteProperty -Name NAME -Value $sharename

    $paramlist | foreach {
        $obj `
        | Add-Member -type NoteProperty -Name ($_.Split("=")[0]) -Value ($_.Split("=")[1])
    }
    $share_index += $obj
}

$share_index | Select * | Export-CSV -notype \\server\share\Shares.csv

Here is the first two objects in the array as an example (using $share_index[0..2] to output to console):

NAME          : _ACCTEST_
TYPE          :  DISK 
PREFIX        :  (<USERCODE>) 
AREABYTES     :  184320 
ACCESS        :  ALL 
FAMILY        :  ACCTESTPACK 
DOWNSIZEAREA  :  TRUE 

NAME              : _HOME_
TYPE              :  DISK 
PREFIX            :  (<USERCODE>) 
COMMENT           :  Private user files 
AREABYTES         :  184320 
ACCESS            :  ALL -EXTRACT 
FAMILY            :  <FAMILY> 
DOWNSIZEAREA      :  TRUE 
ALLOWGUESTACCESS  :  TRUE

And here are the first 3 rows of the CSV:

"NAME","TYPE ","PREFIX ","AREABYTES ","ACCESS ","FAMILY ","DOWNSIZEAREA "
"_ACCTEST_"," DISK "," (<USERCODE>) "," 184320 "," ALL "," ACCTESTPACK "," TRUE "
"_HOME_"," DISK "," (<USERCODE>) "," 184320 "," ALL -EXTRACT "," <FAMILY> "," TRUE "

You can see that COMMENT and ALLOWGUESTACCESS properties are missing even although they are in the array for the _HOME_ share.

EDIT: Accepted slightly modified version of @JPBlancs answer, so last 2 lines of my code is now:

$fixed_index = $share_index | Sort-Object -Property @{expression={(($_.psobject.Properties)|Measure-Object).count}} -Descending | ConvertTo-CSV -notype
$fixed_index | ConvertFrom-CSV | Sort -Property Name | Export-CSV -notype \\server\share\Shares.csv

So, done as suggested, then converted to CSV in a new object. Then, convert that object back from CSV, retaining the new properties, sort again on Name to get the alphabetical list I need, then Export to CSV again.

Gives me, for example:

"NAME","TYPE","PREFIX","PUBLIC","COMMENT","AREABYTES","ACCESS","FAMILY","DOWNSIZEAREA","ALLOWGUESTACCESS"
"_ACCTEST_"," DISK "," (<USERCODE>) ","",""," 184320 "," ALL "," ACCTESTPACK "," TRUE ",
"_HOME_"," DISK "," (<USERCODE>) ",""," Private user files "," 184320 "," ALL -EXTRACT "," <FAMILY> "," TRUE "," TRUE "

JPBlanc · Accepted Answer · 2013-07-31 10:12:53Z

4

I did something like this

$a = "" | select "P1","P2"
$b = "" | select "P1","P2","P3"
$c = "" | select "P1","P2","P3","P4"
$array = $a,$b,$c
$array | Export-Csv c:\temp\t.csv

gives as you remark:

#TYPE Selected.System.String
"P1","P2"
,
,
,

So the idea is to sort the objects by the properties count :

$array | Sort-Object -Property @{expression={$_.psobject.properties.count}} -Descending| Export-Csv c:\temp\t.csv

It gives :

#TYPE Selected.System.String
"P1","P2","P3","P4"
,,,
,,,
,,,

In the case of the question (object created adding members to a System.Object) you can use :

Sort-Object -Property @{expression={(($_.psobject.Properties)|Measure-Object).count}} -Descending

edited Jul 31, 2013 at 10:12

answered Jul 31, 2013 at 7:12

JPBlanc

73.1k18 gold badges137 silver badges180 bronze badges

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

7 Comments

Graham Gold Over a year ago

Changed the last line of my code to

$share_index | Sort-Object -Property @{expression={$_.psobject.properties.count}} | Export-CSV -notype \\server\share\Shares.csv

but still the same result. $share_index.psobject.properties.count returns nothing, but if I try $share_index.psobject.properties I get: ReferencedMemberName : Length ConversionType : MemberType : AliasProperty TypeNameOfValue : System.Int32 IsSettable : False IsGettable : True Value : 165 Name : Count IsInstance : False

JPBlanc Over a year ago

In your case replace 'Sort-Object -Property @{expression={$_.psobject.properties.count}}' with 'Sort-Object -Property @{expression={(($_.psobject.Properties)|Measure-Object).count}}'.

Graham Gold Over a year ago

No joy, still the same, and if I enter this on it's own, returns 0: (($_.psobject.Properties)|Measure-Object).count. I'm running PS v2 if that makes any difference.

Graham Gold Over a year ago

This returns 8 (($share_index.psobject.Properties)|Measure-Object).Count so using the syntax you gave above, the sort should be based on 8 properties rather than the 7 available in the first object (unsorted) so don't know why it's not working

JPBlanc Over a year ago

What about (($share_index[0].psobject.Properties)|Measure-Object).Count

|

Andy Arismendi · Accepted Answer · 2013-07-31 00:42:09Z

4

Export-Csv and ConvertTo-Csv appear to only take the union of properties between objects sent to them. I think you will have to parse the text file and compile a list of all properties and when you create your objects set any missing property values to null.

answered Jul 31, 2013 at 0:42

Andy Arismendi

53.2k17 gold badges113 silver badges130 bronze badges

1 Comment

Graham Gold Over a year ago

I fear you may be right, just spotted this in the help for both Export-CSV and ConvertTo-CSV : "When you submit multiple objects to Export-Csv, Export-Csv organizes the file based on the properties of the first object that you submit. If the remaining objects do not have one of the specified properties, the property value of that object is null, as represented by two consecutive commas. If the remaining objects have additional properties, those property values are not included in the file."

iRon · Accepted Answer · 2023-09-27 11:30:58Z

JPBlanc's solution doesn't always work because the longest object array doesn't have to be a superset of the smaller arrays (e.g. it doesn't work for the example shown below where all objects have different properties but do not differ in the amount of properties).

I was able to generally workaround this as follows:

$a = "" | select "P1","P2"
$b = "" | select "P2","P3"
$c = "" | select "P3","P4"
$array = $a,$b,$c
$array | Select ($array | ForEach {$_ | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name} | Select -Unique) | Export-Csv ".\Union.csv"

Unfortunately this doesn't work for WMI queries that contain objects of different types but it does with the following cmdlet:

Function Union-Object {
    $Output = @()
    $Input | ForEach {
        If ($Output.Count) {$_ | Get-Member | Where {!($Output[0] | Get-Member $_.Name)} | ForEach {$Output[0] | Add-Member NoteProperty $_.Name $Null}}
        $Output += $_
    }
    $Output
}; Set-Alias Union Union-Object

Usage:

$array | Union | Export-Csv ".\Union.csv"

See also GitHub issue: #13906 Add -UnifyProperties parameter to Select-Object

Jojo · Accepted Answer · 2014-02-25 09:37:32Z

0

I use the following code:

$a = "" | select "P1","P2"
$b = "" | select "P1","P2","P3"
$c = "" | select "P1","P2","P3","P4"
$props=@{n='P1';e={$_.p1}},@{n='P2';e={$_.p2}},@{n='P3';e={$_.p3}},@{n='P4';e={$_.p4}}
$array = $a,$b,$c
$array | select $props|Export-Csv c:\temp\t.csv

edited Feb 25, 2014 at 9:37

Jojo

1,9433 gold badges29 silver badges30 bronze badges

answered Feb 25, 2014 at 9:19

Andrei Odegov

3,5722 gold badges18 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to include all properties of all array objects in csv in powershell

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related