Shell Programming and Scripting

Hi,

I have following 2 CSV files

Code:

  
file1.txt
A1,B1,C1,D1,E1
A2,B2,C2,D2,E2
A3,B3,C3,D3,E3
....
 
file2.txt
A1,B1,P1,Q1,R1,S1,T1,U1
A1,B1,P2,Q2,R2,S2,T2,U2
A1,B1,P3,Q3,R3,S3,T3,U3
A2,B2,X1,Y1,Z1,I1,J1,K1
A2,B2,X2,Y2,Z2,I2,J2,K2
A2,B2,X3,Y3,Z3,I3,J3,K3
A2,B2,X4,Y4,Z4,I4,J4,K4
...

Only first 2 columns of the files match
file1.txt is smaller containig only one row per first 2 columns combination (A1,B1 ... A2,B2 ... only 1 row per combination)
file2.txt is bigger file with many rows per first 2 columns combinations (A1,B1 ... A2,B2 ... multiple rows per combination)

What i want to so is to combine 2 files such that per first 2 column combination in file2.txt it appends (with comma as separator) the corresponding data from file1.txt (minus the matching columns from file1.txt)

Code:

 
Example:
the output i want from above files is:
A1,B1,P1,Q1,R1,S1,T1,U1,C1,D1,E1
A1,B1,P2,Q2,R2,S2,T2,U2,C1,D1,E1
A1,B1,P3,Q3,R3,S3,T3,U3,C1,D1,E1
A2,B2,X1,Y1,Z1,I1,J1,K1,C2,D2,E2
A2,B2,X2,Y2,Z2,I2,J2,K2,C2,D2,E2
A2,B2,X3,Y3,Z3,I3,J3,K3,C2,D2,E2
A2,B2,X4,Y4,Z4,I4,J4,K4,C2,D2,E2
...

where data in red is from file2.txt and in blue from file1.txt

the output has each line from file2.txt (ex A1,B1,P1,Q1,R1,S1,T1,U1) and after comma the corresponding data from file1.txt (C1,D1,E1) without the matching column (A1,B1)

I do not want to use loop as it will be time consuming since the files are huge.

i used below awk cmd, but it doesn't give me desired output.

Code:

 
nawk -F"," 'FNR==NR{f1[$1,$2]=$0;next}{idx=$1 SUBSEP $2; if(idx in f1) $0=f1[idx] OFS $0}1' file1.txt file.txt

can someone pleas help me with this.

Thanks in advance.

Moderator's Comments:

Video tutorial on how to use code tags in The UNIX and Linux Forums.

Try:

Code:

awk -F, 'NR==FNR{for (i=3;i<=NF;i++) a[$1","$2]=a[$1","$2]","$i;next}{$0=$0""a[$1","$2]}1' file1.txt file2.txt

gr888888
Thanks a lot, it worked.