Awk command help

Wissam · March 7, 2012, 10:21am

Hi

Iam trying to do a similar functionality as vlookup in excel between two files, but my problem is that its not a column to column in match its a text within a column:

File 1:

1  yanki disable xyz123 link=xyz123
2 bravo enable asd123 link=asd123

File 2:

1  sample total lab_xyz123 lab=xyz123
4 fault total lab_qwe123 lab=qwe123

Result should be:

xyz123 lab_xyz123
asd123 Not Found

birei · March 7, 2012, 12:24pm

Hi Wissam,

Try to explain it better. I don't know what vlookup is in excel.

Wissam · March 7, 2012, 12:35pm

Hi

I want to match xyz123A from file 1 with lab_xyz123 in file 2, and if not found to put for example not found.

all purpose is to print both files next to next, i.e:

xyz123A    lab_xyz123
xyz123B    lab_xyz123
ced123A    Not Found
ced123C    Not Found

etc...

Hope its clear

Corona688 · March 7, 2012, 1:11pm

So the files come in the exact same order?

How is it to tell that xyz123A in file1 belongs with lab_xyz123 in file2? There's several possible columns which could tell it and I'm not sure which I'm supposed to use. Maybe if your sample data didn't have identical everything everywhere....

rangarasan · March 7, 2012, 1:20pm

Hi,
Try this one,

for read line;
do
   f4=`eche "$line" | cut -d" " -f4`
   awk -v r="$f4" c="lab_$f4" '{if(c == $4){print r,c;t=0;}}END{if(t != 0){print r,"Not Found";}}' file2
done <file1

Cheers,
Ranga:-)

Wissam · March 8, 2012, 10:48am

To make it more clear i exported the results using awk on one column to be more easy to match them:

File 1:

 xyz033E
 xyz033F
 xyz033G
 xyz177E
 xyz177F
 xyz177G
 xyz181A
 xyz181B
 xyz181C
 xyz181E
 xyz181F
 xyz181G

File 2:

Iub_xyz032
Iub_xyz033
Iub_xyz069
Iub_xyz070
Iub_xyz071
Iub_xyz074
Iub_xyz077
Iub_xyz088
Iub_xyz113
Iub_xyz114_PAT
Iub_xyz177_2nd_INT
Iub_xyz178
Iub_xyz179
Iub_xyz180
Iub_xyz181_INT
Iub_xyz189
Iub_xyz191
Iub_xyz197_2nd
Iub_xyz198_2nd_INT
Iub_xyz199
Iub_xyz589
Iub_xyz654
Iub_xyz681_2nd_PAT
Iub_xyz817
Iub_xyz818
Iub_xyz861
Iub_xyz952
Iub_xyz984
Iub_xyz988

Result should be:

 xyz033E	Iub_xyz033
 xyz033F	Iub_xyz033
 xyz033G	Iub_xyz033
 xyz177E	Iub_xyz177_2nd_INT
 xyz177F	Iub_xyz177_2nd_INT
 xyz177G	Iub_xyz177_2nd_INT
 xyz181A	Iub_xyz181_INT
 xyz181B	Iub_xyz181_INT
 xyz181C	Iub_xyz181_INT
 xyz181E	Iub_xyz181_INT
 xyz181F	Iub_xyz181_INT
 xyz181G	Iub_xyz181_INT

---------- Post updated 03-08-12 at 10:48 AM ---------- Previous update was 03-07-12 at 03:34 PM ----------

Any help please

balajesuri · March 8, 2012, 11:14am

A bit crude, but works.

#! /bin/bash
while read x
do
    y=${x%?}
    grep -q "$y" output
    [ $? -eq 0 ] && echo -e "$x\t$(grep $y file2)"
done < file1

birei · March 8, 2012, 11:15am

One way using perl:

$ cat file1
 xyz033E
 xyz033F
 xyz033G
 xyz177E
 xyz177F
 xyz177G
 xyz181A
 xyz181B
 xyz181C
 xyz181E
 xyz181F
 xyz181G
$ cat file2
Iub_xyz032
Iub_xyz033
Iub_xyz069
Iub_xyz070
Iub_xyz071
Iub_xyz074
Iub_xyz077
Iub_xyz088
Iub_xyz113
Iub_xyz114_PAT
Iub_xyz177_2nd_INT
Iub_xyz178
Iub_xyz179
Iub_xyz180
Iub_xyz181_INT
Iub_xyz189
Iub_xyz191
Iub_xyz197_2nd
Iub_xyz198_2nd_INT
Iub_xyz199
Iub_xyz589
Iub_xyz654
Iub_xyz681_2nd_PAT
Iub_xyz817
Iub_xyz818
Iub_xyz861
Iub_xyz952
Iub_xyz984
Iub_xyz988
$ cat script.pl
use warnings;
use strict;

die qq[Usage: perl $0 <file1> <file2>\n] unless @ARGV == 2;

open my $fh1, qq[<], shift @ARGV or die;
open my $fh2, qq[<], shift @ARGV or die;

my (%file2_data, $re);

while ( <$fh2> ) {
        chomp;
        my $str = $_;
        s/\A[^_]*_//;
        s/_.*\Z//;
        $file2_data{ $_ } = $str;
}

{
        my $file2_regex = join qq[|], keys %file2_data;
        $re = qr/$file2_regex/;
}

while ( <$fh1> ) {
        chomp;
        if ( m/($re)/o ) {
                printf qq[%s\t%s\n], $_, $file2_data{ $1 };
        }
}
$ perl script.pl file1 file2
 xyz033E        Iub_xyz033
 xyz033F        Iub_xyz033
 xyz033G        Iub_xyz033
 xyz177E        Iub_xyz177_2nd_INT
 xyz177F        Iub_xyz177_2nd_INT
 xyz177G        Iub_xyz177_2nd_INT
 xyz181A        Iub_xyz181_INT
 xyz181B        Iub_xyz181_INT
 xyz181C        Iub_xyz181_INT
 xyz181E        Iub_xyz181_INT
 xyz181F        Iub_xyz181_INT
 xyz181G        Iub_xyz181_INT

Wissam · March 8, 2012, 11:52am

Thanks bala

can you please explain me the result of output after each line

---------- Post updated at 11:52 AM ---------- Previous update was at 11:34 AM ----------

Hi Beri

Thanks a lost it worked perfectly

but their is one drawback that if files not sorted it will miss them, is their a way so that a record in file1 will check all lines in file2 for a match ?

Thanks again.

birei · March 8, 2012, 11:55am

What do you mean with not sorted? Can you provide an example or give more detail?

balajesuri · March 8, 2012, 12:31pm

balajesuri:

#! /bin/bash
while read x
do
   y=${x%?} # Remove last character from each line of file2 which is contained in $x
   grep -q "$y" file2 # Search for $y in file2 but don't print the output
   [ $? -eq 0 ] && echo -e "$x\t$(grep $y file2)" # If $y found in file2 then print $x followed by line in file2 containing $y
done < file1

Wissam · March 8, 2012, 2:52pm

hi bala

but how the files will be run

for example if i put the code in a file called command in same directory where file1 and file2 are located.

can i do the following only ?

bash command

Corona688 · March 8, 2012, 3:44pm

Anything you can type into the shell, you can also put into a file and run with bash filename. There's no mysterious difference between the two.

Well, except aliases and history commands, but none of them are being used here.

Wissam · March 8, 2012, 3:47pm

hi corona

can you please explain it more

for example i put the code inside a file names command

so the only think i need to do in terminal is

bash command

is this right ?

Corona688 · March 8, 2012, 3:49pm

Put this

#! /bin/bash
while read x
do
    y=${x%?} # Remove last character from each line of file2 which is contained in $x
    grep -q "$y" file2 # Search for $y in file2 but don't print the output
    [ $? -eq 0 ] && echo -e "$x\t$(grep $y file2)" # If $y found in file2 then print $x followed by line in file2 containing $y
done < file1

in a file.

Run it with

bash filename

...that's all there is to it.

Wissam · March 8, 2012, 5:03pm

Hi

its giving below error:

guas1> bash commandbash
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .
grep: illegal option -- q
Usage: grep -hblcnsviw pattern file . . .

guas1> cat commandbash
#! /bin/bash
while read x
do
    y=${x%?} # Remove last character from each line of file2 which is contained in $x
    grep -q "$y" file2 # Search for $y in file2 but don't print the output
    [ $? -eq 0 ] && echo -e "$x\t$(grep $y file2)" # If $y found in file2 then print $x followed by line in file2 containing $y
done < file1

guas1>

Corona688 · March 8, 2012, 5:07pm

Your version of grep doesn't have -q. I'm guessing you're using solaris? You should probably tell people what your system is in your opening post.

Try instead:

grep "$y" file2 >/dev/null

Scrutinizer · March 10, 2012, 11:42am

Or if you are on solaris, use /usr/xpg4/bin/grep