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
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
Rob, you’re KING!!! Thx a lot!!!
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 }
@Bob. Nice one. Didn’t know your resolve properties like that. It’s good to have options for resolving property values by name.