Hello everybody,
I am getting unexpected result while trying to merge two files. Files are like
$ cat one

10,1,2
20,4,5
30,7,8
ID,A,B

$ cat two

3,10
6,20
9,30
C,ID

While paasing the command "join -t, -1 1 -2 2 -o 1.1,1.2,1.3,2.1 one two", I am getting

ID,A,B,C

where as I am supposed to get

10,1,2,3
20,4,5,6
30,7,8,9
ID,A,B,C

Can anyone explain me why I am getting this unexpected result? Let me tell I am running the script in SunOS.

Many regards,
Rink..

Hi Rink,
you can use several ways to join 2 flat files. In ur join command first add a sed which will put a column separator in the file and then based on the separator you can join either equi join, or outer join as per your requirement.

Use this sed
Cat file1
10,1,2
20,4,5
30,7,8
ID,A,B
sed 's/^\(..\)\(.*\)$/\1\|\2/g' file1 > file1.tmp

cat file1.tmp
10|,1,2
20|,4,5
30|,7,8
ID|,A,B

put the nbr of characters that would be a part of your keys in the first bracket.
Same way for the second file

sed 's/^\(..\)\(.*\)$/\1\|\2/g' file2 > file2.tmp
cat file2
10,3
20,4
30,5
C,ID

NOTE:
Since your file2 has keys at the end use a cut command to bring it as column one.
Cat file2.tmp
10|,3
20|,4
30|,5
C,|ID

then put a equi join on this


join -j1 1 -j2 1 -t'|' file1.tmp file2.tmp > file.out


cat file.out
10|,1,2|,3
20|,4,5|,4
30|,7,8|,5

For outer join use
join -j1 1 -j2 1 -t'|' -v 1 file1.tmp file2.tmp > file1.out

cat file1.out
ID|,A,B

and finally delete “|” character from the file.

sed 's/\|//g' file.out

cat filename

10,1,2,3
20,4,5,4
30,7,8,5


Note : If you have only one separator as”,” you can use without going for sed . In such case use”,” in place of "|" in the join command

Hope this would help you.

-Manish

Thanks Manish for your response.
As the two files are comma delimited, so we dont need any other delimiter for the purpose. Let me say, the position of the key field used for joining/merging, does not matter. They can be at any place in the corresponding files. All we need to do is, pass the exact field number in the switch (-1 pos_field1 -2 pos_field2) for joing command.
My code is working fine. I had missed to sort the two files which is mandatory before going to merge. But thanks for the effort you put.