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

Viewing 4 reply threads
  • 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: 2
      Replies: 1697
      Points: 3,385
      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: 2
      Replies: 1697
      Points: 3,385
      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: 2
      Replies: 1697
      Points: 3,385
      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. 😉

Viewing 4 reply threads
  • The topic ‘Find Multiple Values in an imported CSV Table Using Multiple Search Criteria’ is closed to new replies.