Help with Awk

Shell Programming and Scripting
1

I have two files:
FileA:
INGROUP GROUP
xyz, abc
tst, def
dev, ghi
prd, jkl

FileB

GROUP ID
abc, developer
def, tester
ghi, integrator
jkl, analyst

I want the following output

File C

INGroup ID
xyz, developer
tst, tester
dev, integrator
prd, analyst

I tried this using join since the real files are really large there is some discrepancy going on during sort.
I want to do this using awk
The following is the code i developed but would need further help

awk -F',' 'FNR=NR {a[$2]=$1;next} {if (a[$1]) print ????

Your help is appreciated

2

Maybe try this?

awk >FileC 'NR==FNR{a[NR]=$2;next}{$2=a[FNR]}1' FileB FileA

3

This should work if the lines of both files are in the same order (parallel), otherwise:

awk -F", " 'NR==FNR{a[$2]=$1;next}a[$1]{print a[$1], $2}' OFS=", " FileA FileB

Regards

4

Thank you Franklin52 and giannicello

5 
awk -F", " 'NR==FNR{a[$2]=$1;next}a[$1]{print a[$1], $2}' OFS=", " FileA FileB

The issue with the above code i am getting is
If FileA has duplicate in second field i.e $2.How do i handle this.

FileA:
INGROUP GROUP
xyz, abc
ink,abc
iji,abc
klm,abc
tst, def
dev, ghi
prd, jkl
6

In that case:

awk -F", " 'NR==FNR{a[$1]=$2;next}a[$2]{print $1, a[$2]}' OFS=", " FileB FileA

Be sure you have the same fieldseparator (comma and a space).

Regards

7

This works good if FileB doesnt have duplicates in 2nd field. But data in FileB can come as duplicates as well.
How will i handle in this situation.
I am basically looking for a equi join between two files on field "group"
I tried sorting data and using join but that has its own issues and prefer doing it with awk.

FileB

GROUP ID
abc, developer
xyz, developer
tst, developer
klm, developer
def, tester
ghi, integrator
jkl, analyst

Appreciate your help

8

And what should be the desired output in this case?

FileA:

INGROUP GROUP
xyz, abc
ink, abc
iji, abc
klm, abc
tst, def
dev, ghi
prd, jkl

FileB:

GROUP ID
abc, developer
xyz, developer
tst, developer
klm, developer
def, tester
ghi, integrator
jkl, analyst
9

Output should be like this

10 
awk -F", " '
NR==FNR{a[$1]=$2;next}
a[$2]{print $2 FS $1 FS a[$2]}
' OFS=", " FileB FileA
11

Franklin52
awk was giving error used nawk on sun solaris

nawk -F", " 'NR==FNR{a[$1]=$2;next} a[$2]{print $2 FS $1 FS a[$2]}' OFS=", " fileB fileA

It returns nothing.

12

This is what I get:

$ cat FileA
xyz, abc
ink, abc
iji, abc
klm, abc
tst, def
dev, ghi
prd, jkl
$
$ cat FileB
abc, developer
xyz, developer
tst, developer
klm, developer
def, tester
ghi, integrator
jkl, analyst
$
$ awk -F", " '
NR==FNR{a[$1]=$2;next}
a[$2]{print $2 FS $1 FS a[$2]}
' OFS=", " FileB FileA
abc, xyz, developer
abc, ink, developer
abc, iji, developer
abc, klm, developer
def, tst, tester
ghi, dev, integrator
jkl, prd, analyst
$

Try /usr/xpg4/bin/awk.

Regards

13

Franklin52
Let take this files for better understanding.

FileA
xyz, abc
ink, abc
iji, abc
klm, abc
tst, def
dev, ghi
prd, jkl

FileB:
abc, developer
abc, manager
xyz, developer
tst, developer
klm, developer
def, tester
ghi, integrator
jkl, analyst

awk -F", " '
NR==FNR{a[$1]=$2;next}
a[$2]{print $2 FS $1 FS a[$2]}
' OFS=", " FileB FileA

Expected output:
abc, xyz, developer
abc, ink, developer
abc, iji, developer
abc, klm, developer
abc, xyz, manager
abc, ink, manager
abc, iji, manager
abc, klm, manager
def, tst, tester
ghi, dev, integrator
jkl, prd, analyst

This will be the result if i import the FileA and FileB in two tables and join them on 2nd filed of FileA and 1st Field of FileB.

The problem with the array is it gets overwritten when same value is encountered for which array is being created.

Please let me know how to handle this.

Thanks a lot.

14

Beware of the order of the files:

awk -F", " '
NR==FNR{a[$1]=$2; next}
{ for(i in a) {
    if(a==$1) {
      print $1, i, $2
    }
  }
}
' OFS=", " FileA FileB
15

The code is not working.Its not returning any thing.

nawk -F", " 'NR==FNR{a[$1]=$2; next} {for(i in a) {if(a==$1) {print $1, i, $2}}}' OFS=", " FileA FileB

Is there any other better way to do it.

Appreciate your response

16

Try to find out where it fails, with the given examples you should be able to adjust the code yourself. I get the desired output with the given input files:

$ cat FileA
xyz, abc
ink, abc
iji, abc
klm, abc
tst, def
dev, ghi
prd, jkl
$ cat FileB
abc, developer
abc, manager
xyz, developer
tst, developer
klm, developer
def, tester
ghi, integrator
jkl, analyst
$ awk -F", " '
NR==FNR{a[$1]=$2; next}
{ for(i in a) {
    if(a==$1) {
      print $1, i, $2
    }
  }
}
' OFS=", " FileA FileB
abc, ink, developer
abc, iji, developer
abc, klm, developer
abc, xyz, developer
abc, ink, manager
abc, iji, manager
abc, klm, manager
abc, xyz, manager
def, tst, tester
ghi, dev, integrator
jkl, prd, analyst
$