Hi all,

I need to join two columns from two different files file1.txt and file2.txt and append in a new file. Example :
$cat file1.txt
ABCD.ksh:010141
ABCD.ksh:010511
ABCD.ksh:010815
ABCD.ksh:011114
ABCD.ksh:011415
ABCD.ksh:011720
ABCD.ksh:012022
ABCD.ksh:012830
ABCD.ksh:014432
ABCD.ksh:020532

$cat file2.txt
ABCD.ksh:010148
ABCD.ksh:010522
ABCD.ksh:010849
ABCD.ksh:011124
ABCD.ksh:011421
ABCD.ksh:011731
ABCD.ksh:012511
ABCD.ksh:014012
ABCD.ksh:015512
ABCD.ksh:021010

I need to get the output of something like this (say with : separator)

ABCD.ksh 10141 10148
ABCD.ksh 10511 10522
ABCD.ksh 10815 10849
ABCD.ksh 11114 11124
ABCD.ksh 11415 11421
ABCD.ksh 11720 11731
ABCD.ksh 12022 12511
ABCD.ksh 12830 14012
ABCD.ksh 14432 15512
ABCD.ksh 20532 21010

A simple output of :

10141 10148
10511 10522
10815 10849
11114 11124
11415 11421
11720 11731
12022 12511
12830 14012
14432 15512
20532 21010

May also help.

I am trying $ join -t: file1.txt file2.txt
But it results the Cartesian product (10x10=100) rows.

Any help ??

~

These both work for me...but maybe I am not understanding you correctly.

$join -t: file1.txt file2.txt | more
ABCD.ksh:010141:010148
ABCD.ksh:010141:010522
ABCD.ksh:010141:010849
ABCD.ksh:010141:011124
ABCD.ksh:010141:011421
ABCD.ksh:010141:011731
ABCD.ksh:010141:012511
ABCD.ksh:010141:014012
ABCD.ksh:010141:015512
ABCD.ksh:010141:021010
ABCD.ksh:010511:010148
ABCD.ksh:010511:010522
ABCD.ksh:010511:010849
ABCD.ksh:010511:011124
ABCD.ksh:010511:011421
ABCD.ksh:010511:011731
ABCD.ksh:010511:012511
ABCD.ksh:010511:014012
ABCD.ksh:010511:015512
ABCD.ksh:010511:021010
ABCD.ksh:010815:010148
ABCD.ksh:010815:010522

$join -t: file1.txt file2.txt | cut -d: -f2,3 | more
010141:010148
010141:010522
010141:010849
010141:011124
010141:011421
010141:011731
010141:012511
010141:014012
010141:015512
010141:021010
010511:010148
010511:010522
010511:010849
010511:011124
010511:011421
010511:011731
010511:012511
010511:014012
010511:015512
010511:021010
010815:010148
010815:010522

Thanks. But..

while I am usine
$join -t: file1.txt file2.txt

I am getting the cartesian product - that is the all possible join combination 10x10 = 100 rows.

/home/mukher2> join -t: file1 file2|wc -l
100
/home/mukher2> wc -l file*
10 file1
10 file2
20 total
/home/mukher2>

I am using HP-UX B.11.11 and /usr/bin/join.


But the problem resolved when I used :

/home/mukher2> pr -tm file1 file2
ABCD.ksh:010141 ABCD.ksh:010148
ABCD.ksh:010511 ABCD.ksh:010522
ABCD.ksh:010815 ABCD.ksh:010849
ABCD.ksh:011114 ABCD.ksh:011124
ABCD.ksh:011415 ABCD.ksh:011421
ABCD.ksh:011720 ABCD.ksh:011731
ABCD.ksh:012022 ABCD.ksh:012511
ABCD.ksh:012830 ABCD.ksh:014012
ABCD.ksh:014432 ABCD.ksh:015512
ABCD.ksh:020532 ABCD.ksh:021010
/home/mukher2>

The desired output can be obtained by :

pr -tm file1 file2 |awk '{print $1":"$2}'

sabyasm,

thanks for the reply, I understand what you are saying and glad that you have found the solution...this is another variation which makes it more pretty for you (there is probably a better way of doing it using awk BUT this is just faster for me):

$pr -tm file1.txt file2.txt | awk '{print $1":"$2}' | cut -d: -f2,4
010141:010148
010511:010522
010815:010849
011114:011124
011415:011421
011720:011731
012022:012511
012830:014012
014432:015512
020532:021010

OR

$pr -tm file1.txt file2.txt | awk '{print $1":"$2}' | cut -d: -f2,4 | sed s/\:/\ /g
010141 010148
010511 010522
010815 010849
011114 011124
011415 011421
011720 011731
012022 012511
012830 014012
014432 015512
020532 021010

thanks a lot!

nawk -f syb.awk file1.txt file2.txt

syb.awk:

BEGIN {
  FS=":"
  OFS=" "
}

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