Join

Shell Programming and Scripting
1

joining two files:

File1:
-----
1|M
2|M
3|F

File2:
-----
1|abc|def
3|xyz|pqr

join -t '|' 1.txt 2.txt gives(Itried many other ways)

1|M|abc|def
3|F|xyz|pqr

but I need the result like following

1|M|abc|def
2|M|||
3|F|xyz|pqr

any help is appreciated...Thanks

2

nawk ' BEGIN {
FS=OFS="|"
}

FNR == NR {
arr[$1] = $2
next
}

{
( $1 in arr ) ? $1 FS arr[$1] FS $2 FS $3 : OFS
print
} ' 1.txt 2.txt

3

Ahmad,

it gives only the following

1|abc|def
3|xyz|pqr

Thanks for your reoply though...

4

use this

nawk '
BEGIN {
FS=OFS="|"
}
NR==FNR {
a[$1]=$2FS$3 ; next
}
{
print c = ($1 in a) ? $0 FS a[$1] : $0 OFS
}
' 2.txt 1.txt

5

Ahmad,

That works great, thank you so much, one last questionif you don't mind

now I have,

File 1:
-----
1|M|abc|def
2|M|
3|F|xyz|pqr

File2:
-----
1|MD|
1|MBBS|
2|NR
2|FR|
3|DN|

result should be

1|M|abc|def|MD|MBBS|
2|M|||NR|FR|
3|F|xyz|pqr|DN||

thanks for your help...

6

nawk '
BEGIN {
FS=OFS="|"
}
NR==FNR {
a[$1]=a[$1]$2FS$3FS$4 ; next
}
{
print c = ($1 in a) ? $0 FS a[$1] : $0 OFS
}
' 2.txt 1.txt

7

Ahmad,

sorry to bother you,...your code works great, but i have little tricky need here...please look inot this if you have few mins

file1:
----

1|abc|def|ghi|
2|aaa|bbb|ccc|

file2:
-----
1|P|1111111111|
1|P|2222222222|
1|F|3333333333|
2|P|7777777777|
1|P|8888888888|

after join I should get

1|abc|def|ghi|1111111111|3333333333|
2|aaa|bbb|ccc|7777777777||

basically from second file I took the first record as phone(P) and 3 record as FAX(F) for 1 and just phone for 2 as there is no fax for 2 in file 2...

hope I am clear...thanks again

8

nawk -f green.awk file2 file1

green.awk:

BEGIN {
  FS=OFS="|"
}
FNR == NR {
    if ($2 =="P" && !($1 in phone))
       phone[$1]=$3
    if ($2 =="F" && !($1 in fax))
       fax[$1]=$3
    next
}
{
   printf("%s%s%c%s%s\n", $0, ($1 in phone)? phone[$1]:"", OFS,($1 in fax)? fax[$1]:"", OFS)
}
9

vgersh99,

It works...my apologies for not putting the data right.. a small change please...

file1:
----
1|abc|def|ghi|
2|aaa|bbb|ccc|
file2:
-----
1|abc|P|1111111111|
1|abc|P|2222222222|
1|bbc|F|3333333333|
2|aaa|P|7777777777|

after join I should get
1|abc|def|ghi|1111111111||
2|aaa|bbb|ccc|7777777777||

file 1 and file should be matched on first and second fields instead of just matching on the first one...Thanks for your help

10 
BEGIN {
  FS=OFS="|"
}
{idx=$1 SUBSEP $2}
FNR == NR {
    if ($3 =="P" && !(idx in phone))
       phone[idx]=$4
    if ($3 =="F" && !(idx in fax))
       fax[idx]=$4
    next
}
{
   printf("%s%s%c%s%s\n", $0, (idx in phone)? phone[idx]:"", OFS,(idx in fax)? fax[idx]:"", OFS)
}
11

one last try please...not sure what i messed up...here is the real data I am trying to merge

file1:
----
1|4535|78456|abc|def|ghi|
2|4673|73766|aaa|fgh|dfg|
3|7834|94583|bbb|tyr|yut|
file2:
-----
1|abc|P|1111111111|
1|abc|P|2222222222|
1|bbc|F|3333333333|
2|aaa|P|7777777777|
3|bbb|P|1234567890|
3|bbb|F|9876543211|

to be joined on comparing 2 fields (1 &4 in first file / 1 & 2 in second file)

after join I should get
1|4535|78456|abc|def|ghi|1111111111||
2|4673|73766|aaa|fgh|dfg|7777777777||
3|7834|94583|bbb|tyr|yut|1234567890|9876543211|

with previous code I'm getting

1|4535|78456|abc|def|ghi|||
2|4673|73766|aaa|bbb|ccc|||
3|7834|94583|bbb|tyr|yut|||

any help is appreciated...Thanks

12

To be honest with you...... by now you should be able to adjust the posted solution ANY WAY you'd like to fit your changing requirements - this is REALLY easy given you understand what's being done (which you should - otherwise there's no point going through this exercise endlessly).

BEGIN {
  FS=OFS="|"
}
FNR == NR {
    idx=$1 SUBSEP $2
    if ($3 =="P" && !(idx in phone))
       phone[idx]=$4
    if ($3 =="F" && !(idx in fax))
       fax[idx]=$4
    next
}
{
   idx=$1 SUBSEP $4
   printf("%s%s%c%s%s\n", $0, (idx in phone)? phone[idx]:"", OFS,(idx in fax)? fax[idx]:"", OFS)
}
13

vgersh99,

It works perfectly...I really appreciate your help...I'm not a big UNIX guy...

Thank you so much...

14

I have 2 files

1.txt
-----

13
14
16
3

2.txt
-----

13|BV1876032
13|BV9252040
14|MR0316097
17|MH0839742

The following

nawk '
BEGIN {
FS=OFS="|"
}
NR==FNR {
a[$1]=$2 $3 ; next
}
{
print c = ($1 in a) ? $0 FS a[$1] : $0 OFS
}
' 2.txt 1.txt

gives

13|BV9252040
14|MR0316097
16|
3|

Missing the second entry in 2.txt file...output should be

13|BV9252040
13|BV1876032
14|MR0316097
16|
3|

any help is appreciated

Thanks
[/SIZE][/SIZE][/SIZE][/SIZE]

15

I don't quite understand your implementation.
What's the meaning of 'a[$1]=$2 $3' in context of file2?
file2 has only TWO fields?

nawk '
BEGIN {
  FS=OFS="|"
}
FNR==NR {
  a[$1]=($1 in a)?a[$1] ORS $0:$0
  next
}
{
   print ($1 in a)?a[$1]:$1 OFS
}' file2 file1