[SOLVED] Bash vlookup kind of solution

Issue

I have two files,
File 1

2,1,1,1,Test1,1540584051,52
6,5,1,1,Test2,1540579206,54
3,3,0,0,Test3,1540591243,36

File 2

2,1,0,2,Test1,1540584051,52
6,5,0,2,Test2,1540579206,54

i want to look up column 7 value from File 1 to check if it matches with column 7 value from File 2 and when matched, replace the that line in file 2 with corresponding line in file 1 So the output would be

2,1,1,1,Test1,1540584051,52
6,5,1,1,Test2,1540579206,54

Thanks in advance.

Solution

You can do that with the following script:

BEGIN { FS="," }

NR==FNR {
    lookup[$7] = $0
    next
}

{
    if (lookup[$7] != "") {
        $0 = lookup[$7]
    }
    print
}

END {
    print ""
    print "Lookup table used was:"
    for (i in lookup) {
        print "   Key '"i"', Value '"lookup[i]"'"
    }
}

The BEGIN section simply sets the field separator to , so individual fields can be easily processed.

The NR and FNR variables are, respectively, the line number of the full input stream (all files) and the line number of the current file in the input stream. When you are processing the first (or only) file, these will be equal, so we use this as a means to simply store the lines from the first file, keyed on field seven.

When NR and FNR are not equal, it's because you've started the second file and this is where we want to replace lines if their key exists in the first file.

This is done by simply checking if a line exists in the lookup table with the desired key and, if it does, replacing the current line the lookup table line. Then we print the (original or replaced) line.

The END section is there just for debugging purposes, it outputs the lookup table that was created and used, and you can remove it once you're satisfied the script works as expected.

---

You'll see the output in the following transcript, illustrating hopefully that it is working correctly:

pax$ cat file1
2,1,1,1,Test1,1540584051,52
6,5,1,1,Test2,1540579206,54
3,3,0,0,Test3,1540591243,36

pax$ cat file2
2,1,0,2,Test1,1540584051,52
6,5,0,2,Test2,1540579206,54

pax$ awk -f sudarshan.awk file1 file2
2,1,1,1,Test1,1540584051,52
6,5,1,1,Test2,1540579206,54

Lookup table used was:
   Key '36', Value '3,3,0,0,Test3,1540591243,36'
   Key '52', Value '2,1,1,1,Test1,1540584051,52'
   Key '54', Value '6,5,1,1,Test2,1540579206,54'

---

If you need it as a "short as possible" one-liner to use from your script, just use:

awk -F, 'NR==FNR{x[$7]=$0;next}{if(x[$7]!=""){$0=x[$7]};print}' file1 file2

though I prefer the readable version myself.

Answered By - paxdiablo

Answer Checked By - Willingham (WPSolving Volunteer)