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

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 3 months ago.

  • Author
    Posts
  • #30304
    Profile photo of Guido Langendorff
    Guido Langendorff
    Participant

    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 😉

  • #30307
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
  • #30405
    Profile photo of Guido Langendorff
    Guido Langendorff
    Participant

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

  • #30407
    Profile photo of Bob McCoy
    Bob McCoy
    Participant

    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
    }
    
  • #30412
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

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

You must be logged in to reply to this topic.