awk NR==FNR compare 2 files produce a 3rd

Shell Programming and Scripting
1

hi, i have two files, both with 3 columns, the 3rd column has common values between the two files and i want to produce a 3rd file with 4 columns.

file 1

a, ,b c

file 2

a, b ,d

I want to compare the 3rd value and if a match print to file 3 with the 3 columns from the first file plus the entitlement column from file 2 added as 4th column , delimited.

file 3

a, b, c, d

I have been trying things like this borrowed off other posts but its outputing completely wrong, ive never really used awk that much

awk 'NR==FNR{x[$3]=$2;next}$3 in x&&$2=x[$3]' OFS="," FS="," file2 file1

2 
nawk -F, '
{ if ( FILENAME == "file1" )
        _[$3]=$0
        else
        _[$3]=_[$3]","$1 }
END { for ( i in _ ) print _ }
' file1 file2
3

When I run this I get a syntax error, I only have awk on the server as well

awk -F, '{ if ( FILENAME == "file1.txt" )[$3]=$0 else _[$3]=[$3]","$1 }END { for ( i in _ ) print _ [i]}' file1.txt file2.txt
awk: cmd. line:1: { if ( FILENAME == "file.txt" )[$3]=$0 else _[$3]=[$3]","$1 }END { for ( i in _ ) print _ [i]}
awk: cmd. line:1: ^ syntax error

4

Try this:

awk -F, 'NR==FNR{a[$3]=$1;next}$3 in a {$4=a[$3]}1' OFS="," file2 file1

Use nawk or /usr/xpg4/bin/awk on Solaris if you get errors.

Regards

5 
$ cat a.txt
cust1, ac1, 100
cust2, ac2, 200
$ cat b.txt
100000, cust1, 100
2000000, cust2, 200
$ join -a 1 -a 2 -1 3 -2 3 -t"," -o 1.1 1.2 1.3 2.1 a.txt b.txt
cust1, ac1, 100,100000
cust2, ac2, 200,2000000

Mind you, both files need to be sorted on the key column (stbid). In this case a.txt and b.txt are already sorted.

6

franklin, ive realised i need to compare two columns, i.e. if both customer id and account id match in both files then append packageid to the end of the relevent line in the first file. Can that be done?

7

Post an example of both files. You have not specify a colomn for the account id in your second file.

Regards

8

franklin i have made a mistake before,

the format of the files is

file 1

a, b, c

file 2

a, b, d

file 3 should have

customer, account, c, d

9

Try this:

awk -F, 'NR==FNR{a[$1$2]=$3;next}a[$1$2]{$4=a[$1$2];print}' OFS="," file1 file2

Regards

10

i franklin thanks for your help on this it is coming together.

problem now is that if file1 contains more than 1 stbid for the same customer and account pair only the last stbid is written to file 3

e.g.

Is it possible to have the packs and stbids appended on the same line?

11

Something like this?

awk -F, 'NR==FNR{a[$1","$2]=a[$1","$2]?a[$1","$2]","$3:$3;next}
{b[$1","$2]=b[$1","$2]?b[$1","$2]","$3:$3}
END{for(i in b)print i "," b "," a}
' OFS="," file2 file1

Regards

12

franklin thats looks like it is doing the business. Thank you so much! your a genious with awk