Awk replace specific value from another file only if key match

user_19401190 0
Viewed 76

I want to compare 2 files and have the 1st column of File1 and the 2nd column of File2 to be the keys in the array. Then if those match I want the 3rd column of File2 to replace the 3rd column of File1. The issue is that while my attempts do that, they also replace the other rows with nothing and just leave an extra space. Thank you for the help! P.S. Sorry I am new to stack overflow.

$ cat File1
1 name1 1652504401 14/05/2022 Last_server_login_unkown Expired 40 email1@email.com
2 name2 1655915259 22/06/2022 server1 Active 0 email2@email.com
3 name3 1655654205 19/06/2022 server1 Active 3 email3@email.com
4 name4 1652504401 14/05/2022 Last_server_login_unkown Expired 40 email4@email.com
5 name5 1655900988 22/06/2022 server2 Active 0 email5@email.com

$ cat File2
name1 1 1654250529 email1@email.com 19 New_User
name2 2 1654250334 email2@email.com 19 New_User

My attempt:

awk 'FNR==NR{a[NR]=$3;next}{$3=a[FNR]}1' File2 File1 > File3

Undesired output:

$ cat File3
1 name1 1654250529 14/05/2022 Last_server_login_unkown Expired 40 email1@email.com
2 name2 1654250334 22/06/2022 server1 Active 0 email2@email.com
3 name3  19/06/2022 server1 Active 3 email3@email.com
4 name4  14/05/2022 Last_server_login_unkown Expired 40 email4@email.com
5 name5  22/06/2022 server2 Active 0 email5@email.com

Desired output:

$ cat File3
1 name1 1654250529 14/05/2022 Last_server_login_unkown Expired 40 email1@email.com
2 name2 1654250334 22/06/2022 server1 Active 0 email2@email.com
3 name3 1655654205 19/06/2022 server1 Active 3 email3@email.com
4 name4 1652504401 14/05/2022 Last_server_login_unkown Expired 40 email4@email.com
5 name5 1655900988 22/06/2022 server2 Active 0 email5@email.com

EDIT: Kent fixed it! Nice job and thank you! How did you learn to AWK so well? I am always just Googling things and modifying what people post on stackoverflow, but not fully understanding.

sed substitution awk bash
1 Answers

You are close:

$ awk 'NR==FNR{a[$2]=$3;next}$1 in a{$3=a[$1]}1' f2 f1
1 name1 1654250529 14/05/2022 Last_server_login_unkown Expired 40 email1@email.com
2 name2 1654250334 22/06/2022 server1 Active 0 email2@email.com
3 name3 1655654205 19/06/2022 server1 Active 3 email3@email.com
4 name4 1652504401 14/05/2022 Last_server_login_unkown Expired 40 email4@email.com
5 name5 1655900988 22/06/2022 server2 Active 0 email5@email.com

The problem in your code is, that you missed the part of checking if the file2.key exists in file1. So, using the column value instead of the NR /FNR as the associative array's key would be easier.

user_1648350
Related