PowerShell - Export-CSV on multiple unknown custom values

Question

I need help getting started on this script and will do the best to explain what I am trying to accomplish. I have a set of clusters, some have no custom config, some have one custom config, some have two, and other have more. I would to export these custom configs into a CSV, with column headers like custom1, custom2, custom3, etc.

So I need the script to create new column headers based on how many custom config a cluster might have, while either adding NULL or leaving blank cluster that don't have such config. Here is an example of a layout in my head.

ClusterName Custom1 Custom2 Custom3 Custom4
ABC         123     456     NULL    NULL
DEF         NULL    NULL    NULL    NULL
GHI         123     456     789     abc

I don't want to statically create the column before hand, because the custom config could really vary and I need to programmatically allow the script to create the columns based on the data retrieved. I hope all this makes sense and thanks for any help.

Nothing yet as I am trying to figure out where to begin. I believe a for loop would work, however I wanted to ask the question here first before starting. — jrob24
– jrob24, Commented Mar 2, 2014 at 13:49

Frode F. · Accepted Answer · 2014-03-02 16:19:48Z

2

Get a list of all propertynames using Get-Member and use Select-Object + Export-CSV to export the objects using a common header. Objects missing a value will set it to null.

$a = @()
$a += [pscustomobject]@{ClusterName="ABC";Custom1=123;Custom2=456}
$a += [pscustomobject]@{ClusterName="DEF"}
$a += [pscustomobject]@{ClusterName="GHI";Custom1=123;Custom2=456; Custom3=789;Custom4="abc"}    

$properties = $a | ForEach-Object { 
    $_ | Get-Member -MemberType Property, NoteProperty
} | Select-Object -ExpandProperty Name -Unique

$a | Select-Object $properties | Export-Csv test.csv -NoTypeInformation

test.csv

"ClusterName","Custom1","Custom2","Custom3","Custom4"
"ABC","123","456",,
"DEF",,,,
"GHI","123","456","789","abc"

answered Mar 2, 2014 at 16:19

Frode F.

55.4k9 gold badges104 silver badges118 bronze badges

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

2 Comments

jrob24 Over a year ago

In your example you are defining Custom1-Custom4, in my situation I will not know how many Custom configs exist. So how would I go about creating CustomX, not knowing how many will be defined?

Frode F. Over a year ago

The Custom1-4 is just sample data. Everything before $properties = $a ... is just creating sample data with different properties. The code works without knowing anything about the property names. They could be Example1,Test2,Unknown10 for all it cares. The solution you need is $properties = ... and the rest of the script. Just replace $a with the variable where you collection of objects are stored.

Jimbo · Accepted Answer · 2014-03-02 00:56:37Z

0

Your can dynamically create any object within your script. That object can then be piped out to export-csv, whatever columns have been created will get exported.

answered Mar 2, 2014 at 0:56

Jimbo

2,53719 silver badges22 bronze badges

2 Comments

jrob24 Over a year ago

Yes, however how to do dynamically create them when don't know how many to create because it will vary by object?

alroc Over a year ago

By creating them as you encounter them in the course of processing the data.

KERR · Accepted Answer · 2017-11-22 05:14:06Z

0

I had to do something similar - where I was reporting on hard disks for VMs. Some VMs had up to 15 HDDs, while others only had a couple. The CSV was only creating 5 columns.

I added an extra field in the script to count the number of disks for each VM, then at the end, sorted the data by number of disks (descending). Then I could send the output to Out-Gridview or ConvertTo-CSV.

Example count column:

$disks = $vm.guest.disk
$disks = $disks.count
$Details | Add-Member -Name NumberOfDisks -Value $disks -Membertype NoteProperty

Example data sort before piping elsewhere:

$MyCollection = $MyCollection | Sort-Object NumberOfDisks -Descending

$MyCollection piped to Out-Gridview - note the NULL/empty columns:

answered Nov 22, 2017 at 5:14

KERR

1,77822 silver badges15 bronze badges

3 Comments

Clijsters Over a year ago

First: $disks = $vm.guest.disk; $disks = $disks.count why not just $vm.guest.disk.count? 2nd: $Details is undefined. 3rd: $MyCollection is undefined. Oh yeah, $vm is also undefined.

KERR Over a year ago

The idea of my answer is to sort the data descending (by finding a way to count the columns required for each row - eg number of disks) before sending to CSV etc. None of the variables I described have anything to do with OP's question, they're just an example.

Clijsters Over a year ago

They are a non-working example. Is it so hard to just add a declaration with pseudo data to them? I like this idea. However, I just found that your answer could be improved.

Collectives™ on Stack Overflow

PowerShell - Export-CSV on multiple unknown custom values

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related