How to compare two columns in two files?

Hi All,

I have a.dat file with content

1,338,30253395122015103,2015103,UB0085000,STMT151117055527002,,,
1,338,30253395122015103,2015103,UB0085000,STMT151117055527001,,,

and b.da t having content

1,STMT151117055527001,a1.txt,b1.txt,c1.txt
1,STMT151117055527002,a2.txt,b2.txt,c2.txt

Final output should be(compare 6th column of a.dat with 2nd column of b.dat if matches then update columns 7,8,9 of a.dat with the values of column 3,4,5 of b.dat )

1,338,30253395122015103,2015103,UB0085000,STMT151117055527002,a2.txt,b2.txt,c2.txt
1,338,30253395122015103,2015103,UB0085000,STMT151117055527001,a1.txt,b1.txt,c1.txt

Please use code tags as required by forum rules!

Any attempts/ideas/thoughts from your side?

I am using awk to resolve this but not able to match the column value from both the files.

If you have any idea please share !

Why don't you show your attempts?

awk 'NR==FNR{A[$2]=$1; B[$2]=$3; next} {$3=A[$1]; $4=B[$1]}1' FS=, OFS=, a.dat b.dat

You are on the right track, but

• the order of input files is wrong
• the array indices for the first file are OK, for the second, NOK
• Didn't you want to replace THREE fields?
• Didn't you want the trailing fields (7, 8, ...) to be replaced?

Yes I want to replace 3 fields of file a.dat(7th,8th and 9th) which will be empty initially.
Can you please explain the code for this scenario ?

It's your code amended/extended:

awk 'NR==FNR{A[$2]=$3; B[$2]=$4; C[$2]=$5; next} {$7=A[$6]; $8=B[$6]; $9=C[$6]}1' FS=, OFS=, file2 file1

Yes .. Its working ..!! Thank you ... Can you please explain the logic ?

It's YOUR logic! Except for the index corrections.

---------- Post updated at 11:44 ---------- Previous update was at 11:41 ----------

From file2, the first in input stream, identified by NR == FNR, read the to-be-printed data into three arrays, indexed by field 2. Then, when file1 is dealt with, fill fields 7 - 9 from the arrays, this time indexed by field 6, and print the line.

---------- Post updated at 12:02 ---------- Previous update was at 11:44 ----------

This will work as well:

awk -F, 'NR==FNR {T[$2] = $3 FS $4 FS $5; next} {NF = 6; print $0, T[$6]}' file2 OFS=, file1

, although not on ALL awk versions; try

awk -F, 'NR==FNR {T[$2] = $3 FS $4 FS $5; next} {sub (/,*$/,""); print $0, T[$6]}' file2 OFS=, file1

then.

Hi all,
I have file a.dat with content

0,ID,,
1,001,2015060,UB0085000,Key2,abc,xyz
1,338,2015103,UB0085000,Key1,abc,xyz
1,338,2015103,UB0085000,Key5,abc,xyz
1,338,20017457,UB0085000,Key3,abc,xyz
1,338,20017457,UB0085000,Key4,abc,xyz
9,9,2016-03-17-09.03.13.131313

and file b.dat

1,Key1,a.txt,b.txt,c.txt
1,Key3,x.txt,y.txt,z.txt
1,Key5,p.txt,q.txt,r.txt
1,Key2,l.txt,m.txt,n.txt
1,Key4,j.txt,k.txt,l.txt
9,9,2016-03-17-09.03.13.131313

Final output should be(Print 1st and last line of a.dat as it is)

1,Key1,a.txt,b.txt,c.txt
1,Key3,x.txt,y.txt,z.txt
1,Key5,p.txt,q.txt,r.txt
1,Key2,l.txt,m.txt,n.txt
1,Key4,j.txt,k.txt,l.txt
9,9,2016-03-17-09.03.13.131313

Requirement :
I have to update file a.dat with help of b.dat having condition that search 4th column value of a.dat in b.dat. If matches then update column 6,7,8 of a.dat with the values of column 3,4,5 of b.dat and shift existing column 6,7,8 value
to next of a.dat

My Code :

awk 'NR==FNR{A[$2]=$3; B[$2]=$4; C[$2]=$5; next} {$6=A[$5]; $7=B[$5]; $8=C[$5]}1' FS=, OFS=, b.dat a.dat

My Output :

0,ID,,,,,,
1,001,2015060,UB0085000,Key2,l.txt,m.txt,n.txt
1,338,2015103,UB0085000,Key1,a.txt,b.txt,c.txt
1,338,2015103,UB0085000,Key5,p.txt,q.txt,r.txt
1,338,20017457,UB0085000,Key3,x.txt,y.txt,z.txt
1,338,20017457,UB0085000,Key4,j.txt,k.txt,l.txt
9,9,2016-03-17-09.03.13.131313,,,,, 

Current Problem
1st and last line also get proccessed and contain many `,`
existing values of a.dat of column 6,7,8 is not shifting next.

Please suggest solution to this problem ..

How about

awk 'NR==FNR{A[$2]=$3; B[$2]=$4; C[$2]=$5; next} $5 in A {$5 = $5 FS A[$5] FS B[$5] FS C[$5]} 1' FS=, OFS=, b.dat a.dat

Yes .. It worked thank you ..

Hi .. I want to use |��| this string as a delimiter in awk command.

How can I use |��| as a delimiter in awk command ?