unix command for filtering matching data in 2files

Question

Below is the case:

File1.csv

1,2  
3,4  
5,6  
7,8  

File 2.csv

2  
4  
8  

Then I want to read File2.csv and compare it with second coloumn of file1.csv and if matched then take the complete row of file1 and put it in file3.

File3

1,2  
3,4  
7,8  

I am lloking for unix command to do so?

Answer 1

try

awk -F, 'FNR==NR { a[$1]=$1 ; } NR>FNR { if ( $2 in a ) print ;}' File2.csv File1.csv

where

• FNR==NR { a[$1]=$1 ; } select first file (File2.csv), and remember value in array a
• NR>FNR { if ( $2 in a ) print ;} for next file, if second column in array, print.

Answer 2

A (readable) perl version is:

#!/usr/bin/perl

use strict;

my %lookup;
open FILE, "File1.csv" or die "Couldn't open file: $!";
while (<FILE>){
    chomp;
    my ($left, $right) = split(',', $_);

    $lookup{$right} = $_;
}
close FILE;

open FILE, "File2.csv" or die "Couldn't open file: $!";
open OUT_FILE, ">File3.csv" or die "Couldn't open file: $!";
while (<FILE>){
    chomp;

    if (exists ($lookup{$_})) {
      print OUT_FILE "$lookup{$id}\n";
    }
}
close FILE;
close OUT_FILE;

Store the code e.g. as so_33254185.pl and call

$>perl so_33254185.pl

I know you asked for pure shell solution, but maybe this allows you to improve it yourself.

Answer 3

You can use:

awk -F, 'NR==FNR {seen[$1]; next} $2 in seen' file2.csv file1.csv > file3.csv
cat file3.csv
1,2
3,4
7,8