AWK to match and merge data from 2 files into 1.

Shell Programming and Scripting
1

Hello, hopefully this is an easy on for the AWK guru's out there. I'm having some trouble figuring out how to match+merge data in 2 files into 1 single report.

I've got my 2 files filtered and delimited, just need to MATCH $3 in file1 to $1 in file2, then put $0 from File1 and $2+$3 from File2 into File3

File1

0623@1100@ABCXYZ
0624@1203@ABCXYZ
0624@1345@XYZ123
0624@1458@123456
0625@0900@123ABC
0625@0930@ABCXYZ

File2

ABCXYZ@APPLICATION@GROUP
XYZ123@APP1@GROUP1
123456@APP45@GROUP34
123ABC@APP2@GROUP2

File3

0623 1100 ABCXYZ APPLICATION GROUP
0624 1203 ABCXYZ APPLICATION GROUP
0624 1345 XYZ123 APP1 GROUP1
0624 1458 123456 APP45 GROUP34
0625 0900 123ABC APP2 GROUP2
0625 0930 ABCXYZ APPLICATION GROUP

Note that File1 will contain duplicate lines (which I want to show in file3) but file2 will never contain duplicate lines, if that matters.

2

Hi, try:

awk -F@ 'NR==FNR{A[$1=$1]=$0; next} $3=A[$3]' file2 file1
3 
awk -F@ 'NR == FNR {
  $1 = $1
  f2[$1] = $0
  next
  }
!d[$0]++ {
  print $1, $2, f2[$3]
  }' file2 file1
4

Hi right_coaster,

One more way:

$ cat script.awk
BEGIN {
        ## A space as Output File Separator.
        OFS = " "

        ## Field Separator.
        FS = "@"
}

## First input file. Save data in an array, field 1 as key and
## fields 2 and 3 as values.
FNR == NR {
        f2[ $1 ] = $2 " " $3
        next
}

## Second input file. If third field is found, do a no-op to 
## use OFS and print.
FNR < NR {
        if ( f2[ $3 ] ) {
                $1 = $1
                print $0, f2[ $3 ]
        }
}
$ awk -f script.awk file2 file1
0623 1100 ABCXYZ APPLICATION GROUP
0624 1203 ABCXYZ APPLICATION GROUP
0624 1345 XYZ123 APP1 GROUP1
0624 1458 123456 APP45 GROUP34
0625 0900 123ABC APP2 GROUP2
0625 0930 ABCXYZ APPLICATION GROUP
5

Thanks for the replies, they worked for my small example in the post. I tested it on a couple of test files. However when I applied it to my actual files, nothing is returned from the awk command.

I verified that they are formated exactly like the example, there is no whitespace in the files, etc. I tried every reply given here, all simply return me to the prompt with no output. I also tried swapping the order of file1 file2 in the command (just to make sure), and nothing.

Any ideas, i'm baffled? I've attached the real files, apps2.txt is file2 and notok.txt is file1.

6

I did find additional white space in my file which I removed with

tr -d ' '

Thanks again for the replies! I'm all set.

7

Try this ,

 awk -F"@" 'NR==FNR{a[$1]=$2 " " $3; next}
{gsub(" ","",$3); if(a[$3]) { print $0 , a[$3]}}' apps2.txt notok.txt