0

I have 2 files which has to be joined on $1,$2 of 1st file and $1,$8 of 2nd file using awk.

file1:

111,123abc,eee,ttt,yyy
222,234bcd,ttt,yyy,333

file2:

111,hhhh,eeee,rere,,23,2014,123abc

222,jjkj,7878,uhjj,1,45,2013,234bcd

333,aaa,hhh,jjjj,2,78,590,567acd

output:

111,123abc,hhhh,eeee,rere,,23,2014
222,234bcd,jjkj,7878,uhjj,1,45,2013
3
  • Can you clarify your problem a little? For example, do the two files have the same number of lines, with exactly one $1,$8 in file2 for every $1,$2 in file1? Or might there be multiple “111,…,123abc” lines in file2 (or multiple “111,123abc,…” lines in file1)? Might a $1 value (e.g., 111) be paired with multiple $2 / $8 values (e.g., “111,123abc,…” and “111,123abe,…”), and vice versa? Are the input files in any particular order? Do you require the output to be in any particular order? Aug 26, 2014 at 20:52
  • Your example is inconsistent. In the output, the second line has field 8 of file2 present as both field 2 and field 9, whereas the first line has only 8 fields. Aug 26, 2014 at 20:53
  • Hi All,the question has been edited now. please share your answer as per the input and output.Thank you.
    – harinath
    Aug 27, 2014 at 7:51

2 Answers 2

2

Try:

$ awk -F, -v OFS=, 'NR==FNR{a[$1,$2]=1; next} ($1,$8) in a {print $1,$8,$2,$3,$4,$5,$6,$7}' file1 file2

which gives:

111,123abc,hhh,eeee,rere,,23,2014,123abc
222,234bcd,jjkj,7878,uhjj,1,45,2013,234bcd

This isn't exactly the same as your output as you have 8 columns in the first and 9 in the second. I've assumed that's a typo :-)

3
  • You’d get closer to the output the OP asked for if you said {print $1,$8,$2,$3,$4,$5,$6,$7,$8} (maybe leaving off the second ,$8). The example output doesn’t include any of the data from file1 other than the first two fields (i.e., the key fields): eee,ttt,yyy and ttt,yyy,333 do not appear there. Aug 26, 2014 at 20:53
  • @G-Man - so I would! I've fixed it. Thank you. Aug 27, 2014 at 5:34
  • Hey garthTheRed Thank you so much i got the desired output.Thanks for sharing the knowledge.Keep Rocking :)
    – harinath
    Aug 27, 2014 at 11:16
1

If the files are sorted on the join field (field #1) as shown in your example, then you could use the join command

join -t, -o 1.1,1.2,2.3,2.4,2.5,2.6,2.7,2.8 file1 file2
1
  • You’d get closer to the output the OP asked for if you said -o 1.1,1.2,2.2,2.3,2.4,2.5,2.6,2.7,2.8 (adding 2.2 after 1.2) and maybe leaving off the ,2.8. Aug 26, 2014 at 20:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .