CODEWITHSUNDEEP
shellawk
Samrat Saha
Samrat Saha

Reputation: 99

Keep rows from file 1 matching values from file 2 using awk

Contents of the Main File-

$ cat Sort_File2.csv 

'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5O'|'5042449534546015801549'   
'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5O'|'5042449534546015801549'    
'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5Q'|'5042449536906016501541'    
'SR'|'2017-09-01 00:19:20'|'+05:30'|'1A3LA7015L6I'|'5042449603146028701548'

Contents of the File to be Matched to - 

$ cat DuplicatesEqTo1_f2.csv 

1|'5042449536906016501541'    
1|'5042449603146028701548'

I want the Awk Statement to Store in File the Rows from Sort_File2.csv matching with the values from in the file DuplicatesEqTo1_f2.csv.

Output I want - 

'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5Q'|'5042449536906016501541'    
'SR'|'2017-09-01 00:19:20'|'+05:30'|'1A3LA7015L6I'|'5042449603146028701548'

Note I tried the Below Statement its not working and not returning anything--

awk -F'|' 'NR==FNR{++a[$2];next} $1 in a' DuplicatesEqTo1_f1.csv Sort_File1.csv

Upvotes: 1

Views: 65

Answers (2)

ctac_
ctac_

Reputation: 2471

You can use join for this job.

var=5;join -t '|' -1 "$var" -2 2 -o 1.1 1.2 1.3 1.4 1.5 Sort_File2.csv DuplicatesEqTo1_f2.csv

Upvotes: 0

John1024
John1024

Reputation: 113814

Try:

$ awk -F'|' 'NR==FNR{a[$2];next} $NF in a' DuplicatesEqTo1_f1.csv Sort_File1.csv
'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5Q'|'5042449536906016501541'
'SR'|'2017-09-01 00:19:20'|'+05:30'|'1A3LA7015L6I'|'5042449603146028701548'

Notes

  1. The field that you want to match is the last on the line, $NF, not the first. Thus replace $1 in a with $NF in a.

  2. It does no harm but it isn't necessary to increment a[$2]. Simply referencing a[$2] creates the key in array a which is all you need for in order to use the test $NF in a.

Matching against a column specified by a shell variable

Let' define a shell variable, var1, and match against column number $var1:

$ var1=5
$ awk -F'|' -v col="$var1" 'NR==FNR{a[$2];next} $col in a' DuplicatesEqTo1_f1.csv Sort_File1.csv
'SR'|'2017-09-01 00:19:13'|'+05:30'|'1A3LA7015L5Q'|'5042449536906016501541'
'SR'|'2017-09-01 00:19:20'|'+05:30'|'1A3LA7015L6I'|'5042449603146028701548'

Upvotes: 1

Related Questions