Next to the value, also in need of the "column-name" in CSV

PowerShell Help
1

Newbie here… I have a CSV file
Next to “Item”, every row has five columns named PS1-PS5
Not every “name” will have an “x” in every PS1-PS5 column
So, “name1” can have an “x” in PS1, PS2 and “name2” can have “x” in PS2 and PS3.
I am in need of the highest PSx column which still contains an “x”.
So, as in this example I need to get PS2 for “name1” and PS3 for “name2”
The following I came up with so far:
ForEach ($PSx in $RowOfTable)
{
If ($PSx = “x”)
{
$Max = “$PSx”
}
}
It will only show me the “x”. How do I get the column-name for that value?
Any suggestions to make this (real) sophisticated, also welcome :wink:

2

Hey Guido, welcome to Powershell.org!! What you are asking for is a bit complex, but certainly doable.

$csv = @()
$csv += New-Object -TypeName PSObject -Property @{
    Item = "Test1";
    PS1 = $null;
    PS2 = "x";
    PS3 = $null;
    PS4 = "x";
    PS5 = $null;
}
$csv += New-Object -TypeName PSObject -Property @{
    Item = "Test2";
    PS1 = "x";
    PS2 = $null;
    PS3 = $null;
    PS4 = $null;
    PS5 = $null;
}
$csv += New-Object -TypeName PSObject -Property @{
    Item = "Test3";
    PS1 = $null;
    PS2 = $null;
    PS3 = $null;
    PS4 = $null;
    PS5 = "x";
}

foreach ($row in $csv) {
    #You have 5 columns, so we are going to reference them by name
    #and increment 1 - 5 to get the Name and Value of that property
    for ($i = 1; $i -le 5; $i++) {
        #Basically, we are opening the PSObject and referencing the property
        #by name placing the number as the $i variable
        $Name = $row.PSObject.Properties["PS$i"].Name
        $Value = $row.PSObject.Properties["PS$i"].Value
        #Next all that we need to do is see if there is an 'x' and
        #and create a variable to hold the last value that had an 'x'
        if ($Value -eq "x") {
            $highestPS = $Name   
        }
    }
    #Last we display the Item value and the last PS value from 1 - 5 that had 'x'
    "{0} highest PS checked is {1}" -f $row.PSObject.Properties["Item"].Value, $highestPS
}

Output:

Test1 Highest PS checked is PS4
Test2 Highest PS checked is PS1
Test3 Highest PS checked is PS5

I don’t know how you are planning to use this data, but typically in Powershell a new object would be generated like so:

#Import CSV or use test CSV code above...

$results = foreach ($row in $csv) {
    #You have 5 columns, so we are going to reference them by name
    #and increment 1 - 5 to get the Name and Value of that property
    for ($i = 1; $i -le 5; $i++) {
        #Basically, we are opening the PSObject and referencing the property
        #by name placing the number as the $i variable
        $Name = $row.PSObject.Properties["PS$i"].Name
        $Value = $row.PSObject.Properties["PS$i"].Value
        #Next all that we need to do is see if there is an 'x' and
        #and create a variable to hold the last value that had an 'x'
        if ($Value -eq "x") {
            $highestPS = $Name   
        }
    }
    #Last we display the Item value and the last PS value from 1 - 5 that had 'x'
    New-Object -TypeName PSObject -Property @{
        "Item" = $row.PSObject.Properties["Item"].Value;
        "HighestPS" = $highestPS;
    }
}

$results | Format-Table -Property Item, HighestPS -AutoSize

Output:

Item  HighestPS
----  ---------
Test1 PS4
Test2 PS1
Test3 PS5
3

Rob, you’re KING!!! Thx a lot!!!

4

A slightly different approach. Assume column headings are Item, PS1, PS2, etc.

$results = Import-Csv -Path data.csv | foreach {
    for ($i = 5; $i -gt 0; $i--)
    {
        if ($_.$("PS$($i)"))
        {
            [PSCustomObject]@{
                Item = $_.Item
                PS = "PS$($i)"
            }
            break
        }
    }
}
$results | foreach {
    "{0} highest is {1}" -f $_.Item, $_.PS
}
5

@Bob. Nice one. Didn’t know your resolve properties like that. It’s good to have options for resolving property values by name.