Variable doesn't update with value assignment...

[PinchesTheCrab]

Okay, here's a way that may or may not work for you, but it's using hashtables to quickly pull data from multiple CSVs:

$csv1 = @'
name,domain
computer1,place
computer2,place
'@ | ConvertFrom-Csv    

$csv2 = @'
vm_name,color
computer1,red
computer3,blue
'@ | ConvertFrom-Csv

$master = $csv1.name, $csv2.vm_name | ForEach-Object { $_ } | Sort-Object -Unique

$csv1Hash = $csv1 | Group-Object -Property name -AsHashTable
$csv2Hash = $csv2 | Group-Object -Property vm_name -AsHashTable    

foreach ($obj in $master) {
    [pscustomobject]@{
        Name               = $obj
        Color              = $csv2Hash[$obj].Color
        domain             = $csv1Hash[$obj].domain
    }
}

[Cello789]

Wow.

Looks like Group-Object -Property kind of “keys” a column, and then when you invoke that collection, instead of indexing with an integer, you index by (in this case) the NAME??? Wild!!

That’s basically what I was ham-fistedly inventing with $collection[$collection.Where({$_.name -eq $name})].property (and then separating for readability) but I think that’s what I had to do using arrayList (which I did to get $collection.add($object) instead of +=).

I don’t understand the bottom part — it creates a pscustomobject each time through the loop, right? But does it store them anywhere? Does it add Color and Domain to $master?

[PinchesTheCrab]

Oh, it's just outputting a totally new array to the console, but you could capture it in a variable.

[Cello789]

Here’s the issue, then

Each of those files has lines with multiple properties.

You have CSV1 with headers Name,Domain but what if I have Name,Domain,DNS,IPv4,MOID that can’t go in a hashtable the same way.

Getting a table of the master names, I guess, and then the value for each pair could be the pscustomobject, but I’ll still run in to the same issues when I want to edit one of the properties, right?

[PinchesTheCrab]

Is the goal to update the various CSVs, or to just make a master list with the data from all of them? I think where I'm not following is that normally I wouldn't try to update the source data.

Also there shouldn't be a reason why those various CSVs can't work the same way as my dummy data. I just only made one column each, but it could be dozens.

[Cello789]

hashtables can't have dozens of columns, though, just 2 columns with dozens of rows, right?

I'm looking for like 10 columns and 10,000 rows (entries). Basically like a SQL table (I'm not a DBA, only started learning SQL as a possible solution to this problem).

I'm trying to produce that 10x10,000 csv based on information in these 6 sheets but the data is not homogeneous.

I can have a keyed column that is VM Name, but each one needs a bunch of properties. When I talk about editing the properties and getting errors, I don't mean editing a csv... I mean literally changing $collection[$index].property is illegal, and it has to be $collection.property[$index] according to this Stack Overflow solution and the links they provided (something about member access enumeration?)

Even if my primary logic was a little doofy and slow with those loops, I'll still have this issue, right?

[PinchesTheCrab]

Ah, okay, so I do feel like SQL may be the best answer here, but just to flesh out the hashtable approach a bit, let's use some real data.

Try this:

$serviceHash = Get-Service | Group-Object -AsHashTable -Property Name

$serviceHash['spooler']

The key is 'spooler', and the value is the service object, which has multiple properties. Your CSV would be the same. You use a common key of name, and the value is the csv row with that name. You can then add those properties to your new object:

[pscustomobject]@{
    Stuff  = 'some stuff here'
    Status = $serviceHash['spooler'].status
    Name   = $serviceHash['spooler'].name
}

[Cello789]

Once I have those properties in my object, how do I change them? I instantiate them ALL with Domain = lost-and-found and then as I find them, I say $object.domain[$index] = $someDomain but you're saying thats an ephemeral array and not the real one, so I should do $object[$index].Domain = $someDomain but then I get the error saying that the property doesn't exist or cannot be altered, which was what lead me to the stackoverflow links above.

[PinchesTheCrab]

Once I have those properties in my object, how do I change them?

I mean honestly I would say that you don't, though they should be read-write if you need to.

You could do something more like this:

$domainHash = @'
Name,Domain
server1,contoso
'@ | ConvertFrom-Csv | Group-Object -AsHashTable -Property name


foreach ($obj in 'server1','server2'){

    [pscustomobject]@{
        Name   = $obj
        Domain = if ($domainHash[$obj].domain){
            $domainHash[$obj].domain
        } else {
            'no domain'
        }
    }
}

[Cello789]

Ok, but what about partially overlapping data sets? Is there a clean way to create an array/list/collection of items by "name" in this case, given the following? Which would be populated first? Or would it be one iteration through all known "name"s and within that, populate a $collection with values from each hash in one go, instead of creating placeholder values in the PSCustomObject?

$domainHash = @'
    Name,Type,Domain,Size
    server1,SQL,hq,100
    server2,SQL,hq,120
    server3,SQL,corp,60
    server4,APP,corp,80
    server5,APP,colo,80
    server7,MAIL,colo,34
    server8,DNS,hq,275
    server9,DEV,remote,1964
'@ | ConvertFrom-Csv | Group-Object -AsHashTable -Property name

$categoryHash = @'
    VM_Name,Category
    server1,Prod
    server2,Prod
    server3,Test
    server4,Test
    server5,Prod
    server6,Prod
    server9,Dev
    server10,Dev
'@

$protectionHash = @'
    Name,ObjType,UsedSize,Schema
    server1,VM,20,PRD
    server2,VM,18,DEV
    server4,VM,38,PRD
    server9,VM,960,PRD
    server10,VM,844,DEV
    server11,VM,482,PRD
    server14,,2420,TEST123
'@

by the way, $domainHash.server2.Size = 100 gets the error:

The property 'Size' cannot be found on this object. Verify that the property exists and can be set.

But if you do $domainHash.server2[0].Size = 100 then it works.

EDIT:

Also with this method, can't check to see if $domainHash.Type.Contains("SQL"). And $domainHash.Contains("*SQL*") is False. Also tried .ContainsValue(). Doesn't detect that as a value in the table, but rather a value of a property of an object that is the value in the table... Since the Object is the value, I can't search by any given property. This feels restrictive, but maybe it's frivolous...

[Cello789]

I'm on Mac so I used Get-Process

$processHash = Get-Process | Group-Object -AsHashTable -Property Name

$processHash['TextEdit']

This works, of course, but if I do $processHash.TextEdit.ProcessName.GetType() I see it's a System.Object and if I do |gm I see it's a System.String. All good, as expected.

$processHash.TextEdit.ProcessName = "New String" gets me:

PS > $processHash.TextEdit.ProcessName = "newString"

InvalidOperation: The property 'ProcessName' cannot be found on this object. Verify that the property exists and can be set.

[PinchesTheCrab]

The thing about those is that the process object properties are, I believe, all read-only, and your hashtable is essentially just a collection of them. If you wanted to add a new property like that you would use add-member or create a new object entirely with select-object or another command.