Match values from file to array and extract other array column data powershell

Question

I have a file containing a list of jobs. I need to run through that file in a foreach loop and for each value I need to look for that in the first column of an array then check the value in the 3rd column and if that is N output the jobname.

So far I have,

$header = 1..4 | ForEach-Object { "h$_" }
$data = Get-Content F:\temp\Connect_Project\connect_jobs.txt

$data2 = "F:\temp\Connect_Project\alljobs.csv" | 
Import-Csv -Delimiter ',' -Header $header 
$stream = [System.IO.StreamWriter]     "F:\temp\Connect_Project\jobs_to_change.txt"
foreach ( $line in $data ) {  
    if ( $data2.h1 -match $line ) {
        if ( $data2.h3 -match 'N' ) {
        $stream.WriteLine($line)
    }}}
$stream.Close()

I know that's not working (although it is removing jobs that don't exist in the array) but I'm struggling to work out how to fix it.

Can anyone help?

To update based on comments,

I've also tried

$header = 1..4 | ForEach-Object { "h$_" }
$data = Get-Content F:\temp\Connect_Project\connect_jobs.txt

$data2 = "F:\temp\Connect_Project\alljobs.csv" | 
    Import-Csv -Delimiter ',' -Header $header 

$stream = [System.IO.StreamWriter] "F:\temp\Connect_Project\jobs_to_change.txt"
foreach ( $line in $data ) {
$state = ($data2 | Where-Object {$_.h1 -match $file} | Select-Object h3)c
$line
$state
if ( $state = 'N') {
    $stream.WriteLine($line)
    }}
$stream.Close()

I can see that I'm matching h1 but I'm getting all h3 values not just the one I want.

Please provide more detail about how it does not work. What is wrong? Does it throw errors? Output the wrong thing? Output nothing? Also what do the files look like? Ideally you should provide sample input (mockups of the file contents) and desired output examples. Please read Creating an MCV — EBGreen
– EBGreen, Commented Apr 16, 2018 at 15:06
Each row of connect_jobs.txt ($data) just contains 1 8 character jobname, each row of alljobs.csv contains 4 fields similar to "JOBNAME,ZOS,N,APPLICATION_NAME" — dazedandconfused
– dazedandconfused, Commented Apr 16, 2018 at 15:08
As far as I can tell it's reading through all of the $data2.h3 values and evaluating on the last one which just happens to be N. I need it to evaluate on the $data2.h3 that goes with the $data2.h1 that it has matched. — dazedandconfused
– dazedandconfused, Commented Apr 16, 2018 at 15:10
Well, unless there is only one line in allJobs.csv, data2 will be an array. I suspect that you want to user Where-Object to select only the row where h1 matches $line. — EBGreen
– EBGreen, Commented Apr 16, 2018 at 15:14
data2 is an array and therein lies my issue. I can find the h1 value that matches but I can't select the corresponding h3, i get them all. — dazedandconfused
– dazedandconfused, Commented Apr 17, 2018 at 8:07

dazedandconfused · Accepted Answer · 2018-04-17 09:13:56Z

1

Well that was obvious once I spotted it. Thanks to EBGreen for the where-object tip. Second version worked once I realised I'd put $file in the match and not $line. Corrected it to $line and it's doing what I wanted.

$header = 1..4 | ForEach-Object { "h$_" }

$data = Get-Content F:\temp\Connect_Project\connect_jobs.txt

$data2 = "F:\temp\Connect_Project\alljobs.csv" | 
    Import-Csv -Delimiter ',' -Header $header 

$stream = [System.IO.StreamWriter] "F:\temp\Connect_Project\jobs_to_change.txt"

foreach ( $line in $data ) {

    $state = $data2 | Where-Object {$_.h1 -match $line} | Select-Object h3

    if ( $state -match 'N') {
    $stream.WriteLine($line)
    }}


$stream.Close()

edited Apr 17, 2018 at 9:13

answered Apr 17, 2018 at 8:25

dazedandconfused

859 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Match values from file to array and extract other array column data powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related