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

Welcome Forums General PowerShell Q&A 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

 
Participant
3 years, 2 months ago.

  • Author
    Posts
  • #30304

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    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

    Participant
    Points: 0
    Rank: Member

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

  • #30407

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

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

The topic ‘Next to the value, also in need of the "column-name" in CSV’ is closed to new replies.