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


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Find string from file,search in table based on the string,then replace string with result in newfile
# 1  
Old 03-30-2020
Find string from file,search in table based on the string,then replace string with result in newfile

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
Code:
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
Code:
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
Code:
EMP_ID|EMP_NAME|EMP_DEP|EMP_SAL
001|XXXX|SSSS|1000
002|YYYY|TTTT|2000
003|XXXX|D6|3000
004|AAA|HHHH|4000

Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!

Last edited by vgersh99; 03-30-2020 at 05:05 PM.. Reason: code tags, please!
# 2  
Old 03-31-2020
Hi
I can give a hint for a start. Field separator in 'awk' can be set for each file individually
for instance:
Code:
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):
Code:
awk 'NR == FNR {if($3=="D5") A[0$1$2$3]=$4; next} END {for(i in A) print A[i]}' DEB_TABLE abc.dat

It remains to connect together and compare the data
# 3  
Old 03-31-2020
Do you want to only replace the D5 ?
The following merge script replaces D5 and D6 (all what matches);
Code:
#!/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
Code:
EMP_ID|EMP_NAME|EMP_DES|EMP_SAL
001|XXXX|SSSS|1000
002|YYYY|TTTT|2000
003|XXXX|CCCC|3000
004|AAA|HHHH|4000

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Search partial string in a file and replace the string - UNIX

I have the below string which i need to compare with a file and replace this string in the file which matches closely. Can anyone help me on this. string(Scenario 1)- user::r--,user::ourfrd:r-- String(Scenario 2)- user::r-- File **** # file: /local/Desktop/myfile # owner: me # group:... (6 Replies)
Discussion started by: sarathy_a35
6 Replies

2. UNIX for Dummies Questions & Answers

Search for a string,delete the line and replace with new string in a file

Hi Everyone, I have a requirement in ksh where i have a set of files in a directory. I need to search each and every file if a particular string is present in the file, delete that line and replace that line with another string expression in the same file. I am very new to unix. Kindly help... (10 Replies)
Discussion started by: Pradhikshan
10 Replies

3. Shell Programming and Scripting

Find and replace string based on entries on another file

I have a file1 with different with multiple fields and records File2 has 2 fields. I want to find and replace strings in file1 based on file2 values (I Want an exact match i.e. for example: when searching for DT:3, Substr of DT:34 should not be matched) File2: DT:3 foo_err DT:34 bar_frr... (8 Replies)
Discussion started by: aydj
8 Replies

4. Shell Programming and Scripting

find string and replace with string in other file

Dear all, I need your help, I have file like this: file1:23456 01910964830098775635 34567 01942809546554654323 67589 26546854368698023653 09778 58716868568576876878 08675 86178546154065406546 08573 54165843543054354305 . .file2: 23456 25 34567 26 67589 27 (2 Replies)
Discussion started by: attila
2 Replies

5. Shell Programming and Scripting

sed or awk command to replace a string pattern with another string based on position of this string

here is what i want to achieve... consider a file contains below contents. the file size is large about 60mb cat dump.sql INSERT INTO `table1` (`id`, `action`, `date`, `descrip`, `lastModified`) VALUES (1,'Change','2011-05-05 00:00:00','Account Updated','2012-02-10... (10 Replies)
Discussion started by: vivek d r
10 Replies

6. Shell Programming and Scripting

bash script to find date based on search string for continuesly updating file

Hi All, I am very new to UNIX and I have tried this for a longtime now and unable to crack it.... There is a file that is continuously updating. I need to search for the string and find the date @ which it updated every day..... eg: String is "work started" The log entry is as below: ... (1 Reply)
Discussion started by: Nithz
1 Replies

7. UNIX for Dummies Questions & Answers

Search a string in the file and then replace another string after that position

Hi I am looking for a particular string in a file.If the string exists, then I want to replace another string with some other text.Once replaced, search for the same text after that character position in the file. :wall: E.g: Actual File content: Hello Name: Nitin Raj Welcome to Unix... (4 Replies)
Discussion started by: dashing201
4 Replies

8. Shell Programming and Scripting

Search, replace string in file1 with string from (lookup table) file2?

Hello: I have another question. Please consider the following two sample, tab-delimited files: File_1: Abf1 YKL112w Abf1 YAL054c Abf1 YGL234w Ace2 YKL150w Ace2 YNL328c Cup9 YDR441c Cup9 YDR442w Cup9 YEL040w ... File 2: ... ABF1 YKL112W ACE2 YLR131C (9 Replies)
Discussion started by: gstuart
9 Replies

9. UNIX for Dummies Questions & Answers

Search for a string and replace the searched string in the same position in samefile

Hi All, My requisite is to search for the string "0108"(which is the year and has come in the wrong year format) in a particular column say 4th column in a tab delimited file and then replace it with 2008(the correct year format) in the same position where 0108 was found in the same file..The... (27 Replies)
Discussion started by: ganesh_248
27 Replies

10. Shell Programming and Scripting

appending string to text file based on search string

Hi, I need to append string "Hi" to the beginning of the lines containing some specific string. How can I achieve that? Please help. Malay (1 Reply)
Discussion started by: malaymaru
1 Replies
Login or Register to Ask a Question