Shell Programming and Scripting

I have 2 files pipe delimted and want to merge them based on a key

e.g file 1

123|xxx|yyy|zzz
345|xab|yzy|zyz
456|sss|ttt|foo

file 2

123|hhh|ggg|xxx
345|ddd|www|ddd|fff
456|ddd|sss|sss|eee

so if the key is the first field, and the result should be file 1 with field 2 from file 2 based on the key

i.e. result will be

123|hhh|yyy|zzz
345|ddd|ysy|zyz
456|ddd|ttt|foo

Supposing files are allways sorted bay the first field:

Code:

join  -j 1 -o 1.1,2.2,1.3,1.4  -t\| file1 file2

If the files are not sorted:

Code:

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

Use nawk or /usr/xpg4/bin/awk on Solaris.

You da man Radoulov, it seems to work

Thanks for your help.

I'll keep in mind the join function, cheers Kalshxx

Hi radoulov,
I need some help also, what if i have to marge similare files but:
e.g file 1
4~___~test1~aaa
2~___~test2~aaa
1~___~test3~aaa
1~___~test4~aaa
file 2
1~120~test1~aaa
1~124~test2~aaa
1~125~test3~aaa
1~126~test4~aaa

output:
4~120~test1~aaa # line from file 1 with $2 from file 2
2~124~test2~aaa # line from file 1 with $2 from file 2
rest of lines will be not in output becouse $1 and $3 from file 1 is same in file 2

Thanks & Regards
PP

Code:

awk 'NR == FNR { 
  x[$1,$3] 
  y[FNR]=$2 
  next 
}
!(($1,$3) in x) && $2 = y[FNR]
' FS="~" OFS="~" file2 file1

On Solaris use nawk or /usr/xpg4/bin/awk.

Thanks radoulov, It's working perfecly fine. But:
It doesn't show lines present only in file1, what to do if i want have also such lines in output ?

e.g file 1
4~___~test1~aaa
2~___~test2~aaa
1~___~test3~aaa
1~___~test4~aaa
file 2
1~120~test1~aaa
1~124~test2~aaa
1~125~test3~aaa
output:
4~120~test1~aaa # line from file 1 with $2 from file 2
2~124~test2~aaa # line from file 1 with $2 from file 2
1~___~test4~aaa # new line present only in file1

Best Regards
Peter