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 "

score 4 · Answer 1 · answered Jul 31 '13 at 00:42

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 '13 at 00:42

Andy Arismendi

50,577
16
107
124

1

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." – Graham Gold Jul 31 '13 at 06:49

JPBlanc · Accepted Answer · 2013-07-31T10:12:53.203

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 '13 at 10:12

answered Jul 31 '13 at 07:12

JPBlanc

70,406
17
130
175

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 – Graham Gold Jul 31 '13 at 08:54
In your case replace 'Sort-Object -Property @{expression={$_.psobject.properties.count}}' with 'Sort-Object -Property @{expression={(($_.psobject.Properties)|Measure-Object).count}}'. – JPBlanc Jul 31 '13 at 09:07
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 Jul 31 '13 at 09:21
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 – Graham Gold Jul 31 '13 at 09:30
What about `(($share_index[0].psobject.Properties)|Measure-Object).Count` – JPBlanc Jul 31 '13 at 09:41
Nailed it, I needed to tell the sort to be descending so the first object had the most properties so that every object would have every property even if just null for that object. So really this is what I have in that line of code now: `$share_index | Sort-Object -Property @{expression={(($_.psobject.Properties)|Measure-Object).count}} -Descending | Export-CSV -notype \\server\share\Shares.csv` – Graham Gold Jul 31 '13 at 09:48
Thanks, much more elegant and efficient than any method I'd have come up with on my own to parse and gather all the properties :-) – Graham Gold Jul 31 '13 at 10:18

score 0 · Answer 3 · edited Feb 25 '14 at 09:37

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 '14 at 09:37

Jojo

1,875
3
29
29

answered Feb 25 '14 at 09:19

Andrei Odegov

2,925
2
15
21

iRon · Answer 4 · 2017-09-02T08:39:15.060

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"

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

4 Answers4

Linked