replace text in specific field with variables from loop

Question

I have a huge file to parse & need to search and replace text but in specific fields, sharing a small sample for reference named dest. first row is header for reference.

cat dest
ID|NAME|COMPANY|NUMBER
1001|Adam||15001
1002|eve|adam&eve|15002
1003|||
1004|||50000
1005|||50001

I have separate files that contain pattern to match, text-to-be-replaced & text-to-replace

cat src
1003||15003
1004|50000|15004
1005|50001|15005

so I can run a given below while loop with sed to achieve for last 2 rows from src file.

cat src | while IFS=$'|'; read id old new; do sed -i "/^${id}/s/${old}/${new}/" dest; done

but for ID=1003 I get empty string for $old, and it will replace all empty columns in dest file for that ID. Which I want to avoid. What I want to do is replace only last field.

DESIRED:

ID|NAME|COMPANY|NUMBER
1001|Adam||15001
1002|eve|adam&eve|15002
1003|||15003
1004|||15004
1005|||15005

I could go with awk as it is more granular, when comes to columnar data. but awk will print out multiple times to stdout, as much as I understand, which is not practical either in my case.

so is there a way that I can do it in smart & concise way?

Answer 1

awk 'BEGIN{ FS=OFS="|" }
 NR==FNR  { id[$1, $2]=$3; next }
          { $4=( ($1, $4) in id? id[$1, $4]: $4) } 1' src dest

• FS: Field Seperator

• OFS: Output Field Seperator

• NR==FNR: An always true condition idiom for the first input file.

  NR is present the total Number of Records that awk read;
  FNR is present each individual File's Number of Records.

• id[$1, $2]=$3: An associated awk array.

  Name: id
  key: column#1+column#2
  value: column#3

  first block only runs for the first input file, i. e, file src.

In this $4=($1, $4) in id? id[$1, $4]: $4, we are updating the value of the last column ($NF or $4 ) of the second file, i. e, file dest from the matched keys combination of the column#1+column#4 in id array, if found then return that value (id[$1, $4]), otherwise copy its current value.

Answer 2

Another awk solution, which assumes that the lines from src will be used exactly once each and in order. This allows us to only keep track of the next line from src until it has been used, and then read the next one in.

awk -F '|' '
    BEGIN { OFS=FS }
    ! have {
        getline line <"src"
        split(line, pat)
        have = 1
    }
    $1 == pat[1] {
        if ($4 == pat[2]) $4 = pat[3]
        have = 0
    }; 1' dest

If the flag have is not set, or zero, the next line from src is read into line and split up into the array pat. This is done in the ! have block.

If the current line of input from dest has a 1st field that is identical to the first element of pat, then we'll test the 4th field against pat[2] and replace it with pat[3] if they are the same. The have flag is then reset to zero to trigger reading a new line in from src.

The trailing 1 at the end of the awk program causes the (possibly modified) record to be outputted.

The output, given the data in the question:

ID|NAME|COMPANY|NUMBER
1001|Adam||15001
1002|eve|adam&eve|15002
1003|||15003
1004|||15004
1005|||15005