I have a file with long rows and a file with reference data. Flong, Fref.

My goal is to search specific field(s) from Flong rows compare them to all values from Fref and replace them with the given value if they match.

Basically search file Flong - row 1 - field 4 against Fref - all rows - field 1.
If it matches check field 6 against field 2, if that matches replace field 4 with field 3. If 1 or 2 don't match then ignore it, make no changes.

Flong contains data like:

Name|location|111|22|333|4444| |6666||8
Name|location| |56|67|| |6666||8

Some fields are empty and some have spaces but all are separated by '|'

Fref contains data like:



Name|location|111|2000|333|4444| |6666||8
Name|location| |56|67|| |6666||8

I know this is do-able with a function but I have been shocked by the complexity people can work into Awk so I have been trying to use that but any string search/editor is usable.


1 Answer 1


Based on your stated RETURN, what you appear to want to do is construct a hash (associative array) keyed on fields 1 and 2 of Fref, and then use fields 4 and 6 of Flong as a lookup key:

$ awk -F'|' '
    BEGIN{OFS = FS} 
    NR == FNR {a[$1 FS $2] = $3; next} 
    $4 FS $6 in a {$4 = a[$4 FS $6]} 
' Fref Flong
Name|location|111|2000|333|4444| |6666||8
Name|location| |56|67|| |6666||8
  • That is a great solution. Could you explain the OFS=FS part?
    – Gray_M
    Sep 27, 2019 at 17:33
  • @Gray_M OFS=FS just sets the output field separator to be the same as the input field separator (i.e. |, as given by the -F command line argument) Sep 27, 2019 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.