Fastest way to find and replace in large file while replacing field present in another file

Question

I have two files. File1 is a csv with 60 fields:

111,Check1|^/h1/h2/h3,22062014184500,20,0.....
111,Check2|^/h43/h40/h9,22062014184500,4,.....
111,Check3|^/h1/h3/h4,22062014184500,0,0,.....

File2 is a mapping file:

OPUM04181,Check1|^/h1/h2/h3
OPUM04040235,Check3|^/h1/h3/h4
OPUM04051898,Check2|^/h43/h40/h9 

Now what I have to do is in field 2 of file 1 I have to replace the field with the field 1 of file 1 of matching field 2. Example:

Now what I have to do is replace field 2 of file1 with field 1 of the line in file2 matching field 2. Example:

In file1:

111,Check1|^/h1/h2/h3,22062014184500,20,0.....

will be

111,OPUM04181,22062014184500,20,0..... 

where OPUM04181 ↔ Check1|^/h1/h2/h3 is mapped in file2.

My constraint is that File1 and file2 don't have the same set of values; their count is different. Also, the line count is 3 million for both files.

This is what I was trying:

• I ran a loop taking field 2 in file1
• I was checking if that field is present in file2.
• If present, I took the field 1 in file2.
• I was replacing the field2 in file1 using sed s///g. But this is taking an enormous amount of time.

Also I can not sort File1.

What is a faster method?

Answer 1

awk -F, -v OFS=, '
    NR==FNR {opu[$2]=$1; next} 
    $2 in opu {$2 = opu[$2]; print}
' file2 file1

This reads the "mapping" file into memory, and then replaces the 2nd field for file1. Each file is processed exactly once.

The output goes to stdout, so to replace the file "in place" do

awk ... file2 file1 > tempfile && mv tempfile file1