Find Multiple Values in an imported CSV Table Using Multiple Search Criteria

Welcome Forums General PowerShell Q&A Find Multiple Values in an imported CSV Table Using Multiple Search Criteria

This topic contains 6 replies, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #171172

    Participant
    Topics: 1
    Replies: 3
    Points: -2
    Rank: Member

    Here is a sample of my CSV table:

    Roadway_Width,Girder_Size,Skew,Min_Length,Max_Length,Standard

    24,Concrete I-Beam (TX28),0,40,75,SIG-24
    24,Concrete I-Beam (TX28),15,40,75,SIG-24-15
    24,Concrete I-Beam (TX28),30,40,75,SIG-24-30
    24,Concrete I-Beam (TX28),45,40,75,SIG-24-45
    24,Concrete I-Beam (TX34),0,40,85,SIG-24
    24,Concrete I-Beam (TX34),15,40,85,SIG-24-15
    24,Concrete I-Beam (TX34),30,40,85,SIG-24-30
    24,Concrete I-Beam (TX34),45,40,85,SIG-24-45
    28,Concrete I-Beam (TX28),0,40,70,SIG-28
    28,Concrete I-Beam (TX28),15,40,70,SIG-28-15
    28,Concrete I-Beam (TX28),30,40,70,SIG-28-30
    28,Concrete I-Beam (TX28),45,40,70,SIG-28-45
    28,Concrete I-Beam (TX34),0,40,85,SIG-28
    28,Concrete I-Beam (TX34),15,40,85,SIG-28-15
    28,Concrete I-Beam (TX34),30,40,85,SIG-28-30
    28,Concrete I-Beam (TX34),45,40,85,SIG-28-45

    I would like to get user input data for Roadway_Width, Skew, and Length. Roadway_Width and Skew will be equal to the values in the table, but Length >= Min_Length and Length < = Max_Length. Return all of the values under Girder_Size and Standard that meet these criteria into 2 separate columns as below.

    Girder_Size                                       Standard
    -------------- -------------
    Concrete I-Beam (TX34) SIG-28-45

    Thank you very much in advance as I have been racking my brain over this and I am getting a headache.

  • #171187

    Participant
    Topics: 1
    Replies: 3
    Points: -2
    Rank: Member

    Here is my code. It only outputs the same two values or no values at all.

    
    do{
    
    $Roadway_Width = @()
    $Girder_Size = @()
    $Skew = @()
    $Min_Length = @()
    $Max_Length = @()
    $Standard = @()
    
    Write-Host "
    
    Import-Csv 'T:\WFSDESGN\BRINSAP\List & Templates\Bridge Beam Lookups\Beam Span Length Lookup.csv' |`
    ForEach-Object {
    $Roadway_Width += $_.Roadway_Width
    $Girder_Size += $_.Girder_Size
    $Skew += $_.Skew
    $Min_Length += $_.Min_Length
    $Max_Length += $_.Max_Length
    $Standard += $_.Standard
    }
    
    $inputRoadway_Width = Read-Host -Prompt 'Roadway Width'
    $inputSkew = Read-Host -Prompt 'Bridge Skew'
    $inputLength = Read-Host -Prompt 'Bridge Span Length'
    
    Write-Host "
    
    if ($Roadway_Width -eq $inputRoadway_Width -and $Skew -eq $inputSkew -and $Min_Length -le $inputLength -and $Max_Length -ge $inputLength)
    {
    $Where = [array]::IndexOf($Roadway_Width, $Skew)
    Write-Host -ForegroundColor Green "Beam Type: " $Girder_Size[$Where]
    Write-Host -ForegroundColor Cyan "Standard: " $Standard[$Where]
    }
    
    Write-Host "
    
    $Response = Read-Host 'Would you like to re-run? Type Y or N'
    
    }
    while($Response -eq "Y")
  • #171211

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    You're actually not asking any question ... you're telling what you would like to do. 😉 This is not a script request forum. If you like to use structured data (tables, CSV files) you should read about cmdlets like Import-CSV and Foreach-Object. Please read the complete help including the examples to learn how to use it.

    • #171217

      Participant
      Topics: 1
      Replies: 3
      Points: -2
      Rank: Member

      Thanks for your help.

  • #171229

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    I'm not sure if I really got what you're trying to do but this might be helpful:

    $CSVFile = 'T:\WFSDESGN\BRINSAP\List & Templates\Bridge Beam Lookups\Beam Span Length Lookup.csv'
    $Data = Import-Csv -Path $CSVFile |
        Select-Object Girder_Size, Standard,
            @{Name = 'Roadway_Width'; Expression = { [INT]$_.Roadway_Width } },
            @{Name = 'Skew'; Expression = { [INT]$_.Skew } },
            @{Name = 'Min_Length'; Expression = { [INT]$_.Min_Length } },
            @{Name = 'Max_Length'; Expression = { [INT]$_.Max_Length } }
    
    $inputRoadway_Width = Read-Host -Prompt 'Roadway Width'
    $inputSkew = Read-Host -Prompt 'Bridge Skew'
    $inputLength = Read-Host -Prompt 'Bridge Span Length'
    
    $Data | 
        Where-Object {
            $_.Roadway_Width -eq [INT]$inputRoadway_Width -and
            $_.Skew -eq [INT]$inputSkew -and
            $_.Min_Length -le [INT]$inputLength -and
            $_.Max_Length -ge [INT]$inputLength
        } |
            Format-Table -AutoSize
    
    • #171463

      Participant
      Topics: 1
      Replies: 3
      Points: -2
      Rank: Member

      This was exactly what I needed. Thank you. Do you know why my code was only returning the same two options? I feel like it had something to do with my "IndexOf" statement.

  • #171466

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    Do you know why my code was only returning the same two options?

    To be really honest – No. I'm far away from beeing an expert in .Net-code or Powershell internals and I gave up thinking about code I don't understand. 😉

You must be logged in to reply to this topic.