CODEWITHSUNDEEP
linuxshelljoinawk
Mobile Softhelptricks
Mobile Softhelptricks

Reputation: 11

Merge two file based on common value in column

I have two files, file1 and file2.

File 1: 

00451367210;518       ;
00140913111;21        ;
00551360550;418       ;
00550362618;16        ;
00850362809;13        ;

File 2

00451367210;041;0
00140913111;021;0
00010010136;021;0
00210010157;041;1
00550362618;121;0
00850362809;021;0
00010010337;021;0
00551360551;021;0
00551360550;121;0

I would like to combine the columns of two files based on the common values of column 1 in file 1 and file 2

The result should be like this one below : 

00451367210;041;0;518       ;
00140913111;021;0;21        ;
00551360550;121;0;418       ;
00550362618;121;0;16        ;
00850362809;021;0;13        ;

I have tried this :

join -t";"  -o '0,1.2,1.3,2.2,2.3' File1 File2

But I have got this : 

00451367210;041;0;518       ;
00140913111;021;0;21        ;
join: file 2 is not in sorted order
join: file 1 is not in sorted order
00850362809;021;0;13        ;

Any idea how to get the wanted result, with awk or join ?

Upvotes: 1

Views: 754

Answers (4)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

join command approach:

join -t';' -j1 -o'1.1,2.2,2.3,1.2,1.3'  <(sort /tmp/file1) <(sort /tmp/file2)

The output(sorted):

00140913111;021;0;21        ;
00451367210;041;0;518       ;
00550362618;121;0;16        ;
00551360550;121;0;418       ;
00850362809;021;0;13        ;

Upvotes: 0

karakfa
karakfa

Reputation: 67507

without awk, if you want to preserve the order, which join may change, here is an approach

f() { nl -s';' $1 | sort -t';' -k2;}; 
join -t';' -j2 <(f file1) <(f file2) -o1.2,2.3,1.3,1.1 | 
sort -t';' -k4n | 
sed -r 's/[ 0-9]+$//'


00451367210;041;518       ;
00140913111;021;21        ;
00551360550;121;418       ;
00550362618;121;16        ;
00850362809;021;13        ;

Upvotes: 1

VIPIN KUMAR
VIPIN KUMAR

Reputation: 3137

Try this - 

$ awk -F';' 'NR==FNR{a[$1]=$2;next} $1 in a {print $0 FS a[$1] OFS FS}' f1 f2
00451367210;041;0;518        ;
00140913111;021;0;21         ;
00550362618;121;0;16         ;
00850362809;021;0;13         ;
00551360550;121;0;418        ;

Upvotes: 0

James Brown
James Brown

Reputation: 37404

Using awk for the job:

$ awk 'BEGIN{FS=OFS=";"}NR==FNR{a[$1]=$0;next}($1 in a)&&$1=a[$1]' file2 file1
00451367210;041;0;518       ;
00140913111;021;0;21        ;
00551360550;121;0;418       ;
00550362618;121;0;16        ;
00850362809;021;0;13        ;

Explained:

  BEGIN { FS=OFS=";" }      # set delimiters 
NR==FNR { a[$1]=$0; next }  # hash file 2 on first field to a 
($1 in a) && $1=a[$1]       # if file1 record is found in a output it

If you want to explore your join path, try to sort the data using process substitution:

$ join -t";"  -o '0,1.2,1.3,2.2,2.3' <(sort file1) <(sort file2)
00140913111;21        ;;021;0
00451367210;518       ;;041;0
00550362618;16        ;;121;0
00551360550;418       ;;121;0
00850362809;13        ;;021;0

Upvotes: 1

Related Questions