Author Posts

August 15, 2016 at 9:32 pm

Hello all, I'm pretty new to powershell and losing my mind with this issue and I'm sure it's something simple.

I'm trying to match a list of users against 4 different systems. There's 5 .csv files with just the name in Last First:

Name
Doe Jane

$Users = Import-CSV -Path "users.csv"
$System1 = Import-CSV -Path "system1.csv"
$System2 = Import-CSV -Path "system2.csv"
$System3 = Import-CSV -Path "system3.csv"
$System4 = Import-CSV -Path "system4.csv"

$S1Results = Compare-Object -ReferenceObject $Users.Name -DifferenceObject $S1.Name -ExcludeDifferent -IncludeEqual
$S2Results = Compare-Object -ReferenceObject $Users.Name -DifferenceObject $S2.Name -ExcludeDifferent -IncludeEqual
$S3Results = Compare-Object -ReferenceObject $Users.Name -DifferenceObject $S3.Name -ExcludeDifferent -IncludeEqual
$S4Results = Compare-Object -ReferenceObject $Users.Name -DifferenceObject $S4.Name -ExcludeDifferent -IncludeEqual

$Results = @()
$Results = New-Object PSObject -Property @{
System1 = $S1Results.InputObject
System2 = $S2Results.InputObject
System3 = $S3Results.InputObject
System4 = $S4Results.InputObject
}

I would like this to be exported to a CSV, so it will look like this in Excel:

System1 System2 System3
Person 1 Person 1 Person 5
Person 2 Person 3
Person 3

But instead, it's outputting them as

System1
{Person1, Person 2, Person 3}

What am I doing wrong?

August 16, 2016 at 4:35 am

Unfortunately, it is not as easy as you think because you need to massage the data into the right format for an easy convert to CSV.

Check out my attempt to create a working solution:

August 16, 2016 at 6:51 pm

All we do is get the largest length of array and substitute the index of each item.


$array1 = (1..10)
$array2 = (11..25)
0..((($array1, $array2 | Measure-Object -Maximum -Property Count).Maximum)-1) | Select @{n="one";e={$array1[$_]}}, @{n="two";e={$array2[$_]}}

August 17, 2016 at 1:46 pm

Thanks for the very thorough response. I'll begin by saying I'm very new to powershell, so I'm sure this is just user error. I made a couple adjustments to what you built and it's almost working as intended. I have it pulling from .csv files and the output is correct, but it's not displaying all the users. For example,

System1 System2 System3 System4
------- ------- ------- -------
User01  User08  User03  User03 
User02  User09  User04  User04 
        User10  User05  User05 
                User06  User06 

Is the output, but there's actually 8 users that should have been displayed under System3 and 6 under System4. Here's the script:

$Users = Import-CSV -Path "H:\_Scripts\Daily Termed\Test\Users.csv"
$System1 = Import-CSV -Path "H:\_Scripts\Daily Termed\Test\System1.csv"
$System2 = Import-CSV -Path "H:\_Scripts\Daily Termed\Test\System2.csv"
$System3 = Import-CSV -Path "H:\_Scripts\Daily Termed\Test\System3.csv"
$System4 = Import-CSV -Path "H:\_Scripts\Daily Termed\Test\System4.csv"

$SystemData = @{
    'System1' = $System1.Name
    'System2' = $System2.Name
    'System3' = $System3.Name
    'System4' = $System4.Name
    }

# Determine highest number of users across all systems
$maxSystemUsers = 0
foreach ($array in $systemData) {
    if ($array.Count -gt $maxSystemUsers) {
        $maxSystemUsers = $array.Count
    }
}

# Get a sorted array of all system names
$systemNames = $systemData.Keys | Sort-Object

# Enumerate system users
$systemResults = for ($i = 0; $i -lt $maxSystemUsers ; $i++) {

    $tempRowDict = New-Object -TypeName System.Collections.Specialized.OrderedDictionary

    foreach ($systemName in $systemNames) {
        $value = $systemData[$systemName]
        if ($i -lt $value.Count) {
            $tempRowDict[$systemName] = $value[$i]
        }
    }

    New-Object -TypeName PSObject -Property $tempRowDict
}

# Output
$systemResults #| ConvertTo-Csv -NoTypeInformation

  • This reply was modified 2 years, 1 month ago by  Llamathrust.