Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Join using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join using awk
# 1  
Old 12-31-2009
Join using awk
Hi -

I want to join 2 files on matching keys ( column 1 in both files ) and check for unmatched columns, If for each record print the first unmatched column from both files and then proceed to next record and do the same.

File 1:

Code:
123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45

File2:
Code:
123|Ray|jj|20/07/2009|25.48
125|Victor|kick|30/07/2009|34.56
234|Nag|aa|15/03/2009|434.45
132|rita|bb|15/03/2009|134.45

Output:
Code:
123|Roy|Ray
125|kk|kick
234|234.45|434.45

I was able to do following but couldn't get to stop processing after first mismatched column for each record. this is what I did

Code:
awk -v File1="$File1" -v File2="$File2" -v key="$key" -v col="$col" -F "|" '
NR==FNR  { PE[$key] = $col ;next } 
{
        if ( $key in PE && $col!="1")
        { if (PE[$key]!=$col ) 
                { print $key"|"$col"|"PE[$key] }
                } 
        else  {
        print "key: "$key " is missing in " File2
        }
}' $File2 $File1

# 2  
Old 12-31-2009
Hi, perhaps you need to switch the reading order of $File1 and $File2 ?
Did you inititialize the shell variables correctly?
I am unsure what you mean by "I couldn't get to stop processing after first mismatched column for each record""
Last edited by Scrutinizer; 12-31-2009 at 03:17 AM..
# 3  
Old 12-31-2009
perl:
Code:
open FH,"<1.txt";
while(<FH>){
	chomp;
	my @tmp = split("[|]",$_);
	$hash{$tmp[0]}=[@tmp[1..$#tmp]];
}
LEO:
while(<DATA>){
	chomp;
	my @tmp = split("[|]",$_);
	my @pre=@{$hash{$tmp[0]}};
	my @cur=@tmp[1..$#tmp];
	for (my $i=0;$i<=$#pre;$i++){
		if($pre[$i] ne $cur[$i]){
			print $tmp[0]," ",$pre[$i]," ",$cur[$i],"\n";
			next LEO;
		}
	}
}
__DATA__
123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45

# 4  
Old 12-31-2009
use nawk,gawk or /usr/xpg4/bin/awk.

code:-

Code:
nawk -F"\|"  '
NR==FNR{for (i=1;i<=NF;i++) {a[$1]=a[$1]" "$i} ; next  }
($1 in a){
split(a[$1],b," ")
for (j=1;j<=NF;j++) {
if ( $j != b[j] ) {print $1,b[j],$j ;next}
}
}
' OFS="\|" File1 File2

Code:
File 1:
123|Roy|jj|20/07/3000|25.48
125|Victor|kk|30/07/2009|34.56
234|Nag|aa|15/03/2009|234.45

Code:
File2:

123|Ray|jj|20/07/2009|25.48
125|Victor|kick|30/07/2009|34.56
234|Nag|aa|15/03/2009|434.45
132|rita|bb|15/03/2009|134.45

Code:
o/p:-

123|Roy|Ray
125|kk|kick
234|234.45|434.45

SmilieSmilieSmilie
SmilieSmilieSmilie
# 5  
Old 12-31-2009
Quote:
Originally Posted by ahmad.diab
Code:
NR==FNR{for (i=1;i<=NF;i++) {a[$1]=a[$1]" "$i} ; next  }
($1 in a){
split(a[$1],b," ")

What about :
Code:
NR==FNR{a[$1]=$0}
($1 in a){
split(a[$1],b,"|")

# 6  
Old 12-31-2009
It will work as well....SmilieSmilie
# 7  
Old 12-31-2009
Thanks a lot it worked like a charm.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Shell Programming and Scripting

Join two files using awk

Hello All; I have two files: File1: abc def pqr File2: abc,123 mno,456 def,989 pqr,787 ghj,678 (6 Replies)
Discussion started by: mystition
6 Replies

3. UNIX for Dummies Questions & Answers

Join with awk

I have file 1 (f1) 531902021342|610231416030043 531902021421|610231415853764 531902021460|610231406819593 531902021477|610231410151075 531902021481|610230208405255 531902021484|0 531902021557|610231407330625 531902021558|610230208106654 531902021564|610230204101407 file 2(f2)... (7 Replies)
Discussion started by: radius
7 Replies

4. Shell Programming and Scripting

awk join 2 files

Hello All, file1 A1;B1;C1;D1;E1;F1;G1;H1;III1;J1 A2;B2;C2;D2;E2;F2;G2;H2;III2;J2 A3;B3;C3;D3;E3;F3;G3;H3;III3;J3 A4;B4;C4;D4;E4;F4;G4;H4;III4;J4file2 III1 ZZ1 S1 Y 1 P1 None NA III2 ZZ2 S2 Y 3 P2 None NA III3 ZZ3 S2 Y 5 ... (2 Replies)
Discussion started by: vikus
2 Replies

5. UNIX for Dummies Questions & Answers

Join with awk different column

hi guys, i need help I need to join file2 to file1 when column 3 in my file1 and column 1 in my file2 in the same string file1 AA|RR|ESKIM RE|DD|RED WE|WW|SUPSS file2 ESKIM|ES SUPSS|SS Output AA|RR|ESKIM|ES RE|DD|RED| WE|WW|SUPSS|SS (3 Replies)
Discussion started by: radius
3 Replies

6. UNIX for Dummies Questions & Answers

Join or Awk

Is is possible to join on unsorted files by a string? Don't want to sort because there is other text that is already in a good format. I want to replace or join RBOSK-374 in file 2 with NB2781 FGH in file 1. Any help would be appreciated. a.log: RBOSK-374 AAA-B04-D16-K01 a.log: 0 XXX 602... (1 Reply)
Discussion started by: jimmyf
1 Replies

7. Shell Programming and Scripting

left join using awk

Hi guys, I need AWK to merge the following 2 files: file1 1 a 1 1 2 b 2 2 3 c 3 3 4 d 4 4 file2 a a/a c/c a/c c/c a/a c/t c c/t c/c a/t g/g c/c c/t desired output: 1 a 1 1 a/a c/c a/c c/c a/a c/t 2 b 2 2 x x x x x x 3 c 3 3 c/t c/c a/t g/g c/c c/t 4 d 4 4 x x x x x x (2 Replies)
Discussion started by: g1org1o
2 Replies

8. Shell Programming and Scripting

how to join two files with awk.

Hi, Unix Gurus, I need to compare two file based on key value and load result to different files. requirement as following: file1 1, abc 2, bcd 4, cdefile2 1, aaaaa 2, bbbbb 5, ccccckey value is first column for both file. I need generate following files; records_in_1_not_2.txt 4,... (6 Replies)
Discussion started by: ken002
6 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

10. Shell Programming and Scripting

awk for join

Is it possible to do this with awk command?? sort1.txt a 10 b 20 sort2.txt a b c d join command join sort1.txt sort2.txt && join -v1 sort2.txt sort1.txt output a 10 b 20 (1 Reply)
Discussion started by: repinementer
1 Replies
Login or Register to Ask a Question