# Awk command help **URL:** \ **Category:** Shell Programming and Scripting\ **Created:** [March 7, 2012, 10:21am UTC](https://community.unix.com/t/awk-command-help/306060 "2012-03-07T10:21:15Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 7, 2012, 10:21am UTC](https://community.unix.com/t/awk-command-help/306060/1 "2012-03-07T10:21:15Z")
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: ```nohighlight 1 yanki disable xyz123 link=xyz123 2 bravo enable asd123 link=asd123 ``` File 2: ```nohighlight 1 sample total lab_xyz123 lab=xyz123 4 fault total lab_qwe123 lab=qwe123 ``` Result should be: ```nohighlight xyz123 lab_xyz123 asd123 Not Found ``` ---
**Author:** ![birei](https://community.unix.com/letter_avatar/birei/32/5_5575768a8748004e209b776fc1b2916d.png) [@birei](https://community.unix.com/u/birei)\ **Post date:** [March 7, 2012, 12:24pm UTC](https://community.unix.com/t/awk-command-help/306060/2 "2012-03-07T12:24:58Z")
Hi Wissam, Try to explain it better. I don't know what vlookup is in excel. ---
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 7, 2012, 12:35pm UTC](https://community.unix.com/t/awk-command-help/306060/3 "2012-03-07T12:35:52Z")
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: ```nohighlight xyz123A lab_xyz123 xyz123B lab_xyz123 ced123A Not Found ced123C Not Found etc... ``` Hope its clear ---
**Author:** ![Corona688](https://community.unix.com/letter_avatar/corona688/32/5_5575768a8748004e209b776fc1b2916d.png) [@Corona688](https://community.unix.com/u/Corona688)\ **Post date:** [March 7, 2012, 1:11pm UTC](https://community.unix.com/t/awk-command-help/306060/4 "2012-03-07T13:11:51Z")
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.... ---
**Author:** ![rangarasan](https://community.unix.com/user_avatar/community.unix.com/rangarasan/32/2356_2.png) [@rangarasan](https://community.unix.com/u/rangarasan)\ **Post date:** [March 7, 2012, 1:20pm UTC](https://community.unix.com/t/awk-command-help/306060/5 "2012-03-07T13:20:11Z")
Hi, Try this one, ```nohighlight 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 **Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 8, 2012, 10:48am UTC](https://community.unix.com/t/awk-command-help/306060/6 "2012-03-08T10:48:26Z") To make it more clear i exported the results using awk on one column to be more easy to match them: File 1: ```nohighlight xyz033E xyz033F xyz033G xyz177E xyz177F xyz177G xyz181A xyz181B xyz181C xyz181E xyz181F xyz181G ``` File 2: ```nohighlight 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: ```nohighlight 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 ☹ ---
**Author:** ![balajesuri](https://community.unix.com/user_avatar/community.unix.com/balajesuri/32/1423_2.png) [@balajesuri](https://community.unix.com/u/balajesuri)\ **Post date:** [March 8, 2012, 11:14am UTC](https://community.unix.com/t/awk-command-help/306060/7 "2012-03-08T11:14:10Z")
A bit crude, but works. ```nohighlight #! /bin/bash while read x do y=${x%?} grep -q "$y" output [$? -eq 0] && echo -e "$x\t$(grep $y file2)" done < file1 ``` ---
**Author:** ![birei](https://community.unix.com/letter_avatar/birei/32/5_5575768a8748004e209b776fc1b2916d.png) [@birei](https://community.unix.com/u/birei)\ **Post date:** [March 8, 2012, 11:15am UTC](https://community.unix.com/t/awk-command-help/306060/8 "2012-03-08T11:15:21Z")
One way using perl: ```nohighlight $ 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 \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 ``` ---
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 8, 2012, 11:52am UTC](https://community.unix.com/t/awk-command-help/306060/9 "2012-03-08T11:52:44Z")
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. ---
**Author:** ![birei](https://community.unix.com/letter_avatar/birei/32/5_5575768a8748004e209b776fc1b2916d.png) [@birei](https://community.unix.com/u/birei)\ **Post date:** [March 8, 2012, 11:55am UTC](https://community.unix.com/t/awk-command-help/306060/10 "2012-03-08T11:55:48Z")
What do you mean with not sorted? Can you provide an example or give more detail? ---
**Author:** ![balajesuri](https://community.unix.com/user_avatar/community.unix.com/balajesuri/32/1423_2.png) [@balajesuri](https://community.unix.com/u/balajesuri)\ **Post date:** [March 8, 2012, 12:31pm UTC](https://community.unix.com/t/awk-command-help/306060/11 "2012-03-08T12:31:18Z")
> [@balajesuri](#): > > ```plaintext > #! /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](#): > > Thanks bala > > can you please explain me the result of output after each line ---
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 8, 2012, 2:52pm UTC](https://community.unix.com/t/awk-command-help/306060/12 "2012-03-08T14:52:15Z")
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 ---
**Author:** ![Corona688](https://community.unix.com/letter_avatar/corona688/32/5_5575768a8748004e209b776fc1b2916d.png) [@Corona688](https://community.unix.com/u/Corona688)\ **Post date:** [March 8, 2012, 3:44pm UTC](https://community.unix.com/t/awk-command-help/306060/13 "2012-03-08T15:44:05Z")
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. ---
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 8, 2012, 3:47pm UTC](https://community.unix.com/t/awk-command-help/306060/14 "2012-03-08T15:47:57Z")
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 ? ---
**Author:** ![Corona688](https://community.unix.com/letter_avatar/corona688/32/5_5575768a8748004e209b776fc1b2916d.png) [@Corona688](https://community.unix.com/u/Corona688)\ **Post date:** [March 8, 2012, 3:49pm UTC](https://community.unix.com/t/awk-command-help/306060/15 "2012-03-08T15:49:52Z")
Put this ```nohighlight #! /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 ```nohighlight bash filename ``` ...that's all there is to it. ---
**Author:** ![Wissam](https://community.unix.com/letter_avatar/wissam/32/5_5575768a8748004e209b776fc1b2916d.png) [@Wissam](https://community.unix.com/u/Wissam)\ **Post date:** [March 8, 2012, 5:03pm UTC](https://community.unix.com/t/awk-command-help/306060/16 "2012-03-08T17:03:59Z")
Hi its giving below error: ```nohighlight 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 . . . ``` ```nohighlight 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> ``` ---
**Author:** ![Corona688](https://community.unix.com/letter_avatar/corona688/32/5_5575768a8748004e209b776fc1b2916d.png) [@Corona688](https://community.unix.com/u/Corona688)\ **Post date:** [March 8, 2012, 5:07pm UTC](https://community.unix.com/t/awk-command-help/306060/17 "2012-03-08T17:07:29Z")
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: ```nohighlight grep "$y" file2 >/dev/null ``` ---
**Author:** ![Scrutinizer](https://community.unix.com/user_avatar/community.unix.com/scrutinizer/32/1216_2.png) [@Scrutinizer](https://community.unix.com/u/Scrutinizer)\ **Post date:** [March 10, 2012, 11:42am UTC](https://community.unix.com/t/awk-command-help/306060/18 "2012-03-10T11:42:55Z")
Or if you are on solaris, use /usr/xpg4/bin/grep