Find string from file,search in table based on the string,then replace string with result in newfile

UNIX for Beginners Q & A
1

Could anyone please help me to get solution for my below requirement. As i'm beginner to shell scripting, i don't have complete idea to achieve solution for below requirement. i tried "cut" and awk to split the file, then after that how we can search in table and to use that result to replace the string in file.

I'm using ksh.

I'm having abc.dat file with the content as below

EMP_ID|EMP_NAME|EMP_DEP|EMP_SAL
001|XXXX|D5|1000
002|YYYY|D5|2000
003|XXXX|D6|3000
004|AAA|D5|4000

I'm having a table DEP_TABLE as below

DPT_ID EMP_NAME EMP_DEP EMP_DES
01          XXXX      D5               SSSS
02           YYYY      D5               TTTT
03           XXXX      D6               CCCC
04            AAA       D5                HHHH

Here i want to find the record which having EMP_DEP as D5 and need to select the coresponding EMP_des from DEP_TABLE (WHERE condition needs to use EMP_DEP, EMP_ID ) and that selected EMP_DES need to replace EMP_DEP IN file

1.find the record for a string in a field
2.search in table with the string
3. Replace the string with the new value found from table

EXPECTED OUTPUT
efg.dat file

EMP_ID|EMP_NAME|EMP_DEP|EMP_SAL
001|XXXX|SSSS|1000
002|YYYY|TTTT|2000
003|XXXX|D6|3000
004|AAA|HHHH|4000
2

Hi
I can give a hint for a start. Field separator in 'awk' can be set for each file individually
for instance:

awk '{print $1}' DEB_TABLE FS=\| OFS=\| abc.dat

You need to create an array from the data of the first file
and compare with the data of the second
for instance(array create):

awk 'NR == FNR {if($3=="D5") A[0$1$2$3]=$4; next} END {for(i in A) print A}' DEB_TABLE abc.dat

It remains to connect together and compare the data

3

Do you want to only replace the D5 ?
The following merge script replaces D5 and D6 (all what matches);

#!/bin/ksh
# also works with bash-4

# Merge script: the two input files must be in the appropriate order

# A universal print formatter
prjoin(){
  typeset d=$1; shift
  typeset outstr=$(printf "%s$d" "$@")
  printf "%s\n" "${outstr%$d}"
}

del="|"

while
  # from each file read a line, take the exit status from the latter
  read b1 b2 b3 b4 <&4
  IFS="$del" read a1 a2 a3 a4 <&3
do
  [ "$b3" = "$a3" ] && a3=$b4
  prjoin "$del" "$a1" "$a2" "$a3" "$a4"
done 3< abc.dat 4< DEP_TABLE

With your input files it gives the following output

EMP_ID|EMP_NAME|EMP_DES|EMP_SAL
001|XXXX|SSSS|1000
002|YYYY|TTTT|2000
003|XXXX|CCCC|3000
004|AAA|HHHH|4000