Shell script to replace fields in one file from a value in another file

I have file with a similar format
...
ABC|CODE1|12333442|002000211
BDF|CODE2|23343344|009928322
..
There are about 10,000 records with the same format as above.
There is another file which has the mapping of codes in the above file
..
CODE1|002001
CODE2|002002
..
I need to write a script that would replace all the fields in column 2 of the first file above with the second column (based on the mapping code) of the 2nd file.
For eg.. in the 1st file if the line was
ABC|CODE1|12333442|002000211
after the transformation
ABC|002001|12333442|002000211
It has replaced CODE1 with 002001 .

I tried to use awk and sed, but somehow am not able to get the solution.
Is there any better way to do it like using perl or any other technology.
Help is greatly appreciated !

dimpleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MorDrakkaCommented:
I can write someting in VBscript. Are the codes always the same length ?
0
MorDrakkaCommented:
Hi,

I have created a script and it works, I have tested it with input.txt which contains:
ABC|CODE1|12333442|002000211
BDF|CODE2|23343344|009928322
inputcode.txt which contains the codes:
CODE1|002001
CODE2|002002
My script just writes it to a new file called Output.txt:
ABC|002001|12333442|002000211
BDF|002002|23343344|009928322

See code snippet. Copy paste the text into a textfile. Rename the textfile to scriptname.vbs and put it into the same dir as your textfiles. You have to change the script with the names of your data and code files:

Hoe this helps, I had fun scripting it.
SET objFSO = CreateObject("Scripting.FileSystemObject")
SET objInputfile = objFSO.OpenTextFile("input.txt")
SET objOutputfile = objFSO.CreateTextFile("Output.txt", True)
DO WHILE NOT objInputfile.AtEndOfStream
	strInputtekst = objInputfile.readline
	arrCodenr = split(strInputtekst, "|")
	strSearchCode = arrCodenr(1)
	SET objInputCodefile = objFSO.OpenTextFile("inputcode.txt")
	DO WHILE NOT objInputCodefile.AtEndOfStream
	
	strInputCodeLine = objInputCodefile.readline
	IF Instr(strInputCodeLine, arrCodenr(1)) then
	arrCode = split(strInputCodeLine, "|")
	arrCodenr(1) = arrCode(1)
	End if
	LOOP
	strOutputtekst = join(arrCodenr, "|")
	objOutputfile.writeline strOutputtekst
	Set objInputCodefile = nothing
LOOP

Open in new window

0
TintinCommented:
The following code will print to the screen, so just direct it to a new file to save the output, eg:

script.pl >newfile
#!/usr/bin/perl
open F2, "file2" or die "Can not open file2 $!\n";
open F1, "file1" or die "Can not open file1 $!\n";
 
while (<F2>) {
  chomp;
  ($name,$value) = split /\|/;
  $code{$name}=$value;
}
 
while (<F1>) {
  s/(\w+\|?)(\w+)/$1$code{$2}/;
  print;
}

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ozoCommented:
can there be any codes in the
ABC|CODE1|12333442|002000211
file that do not appear in the
CODE1|002001
file?
If so, what should happen to them?
0
ahoffmannCommented:
# until ozo came up with a perl onliner ...
awk -F"|" '{print "sed -ne s/"$1"/"$2"/p other-file"}' above-file | sh
# to be improved in many ways ..
0
ghostdog74Commented:
here's an awk (GNU) solution that doesn't need to call other subprocesses.


awk 'BEGIN{OFS=FS="|"}
FNR==NR{
  a[$1]=$2 ; next
}
{
  $2=a[$2]
  $1=$1
  print 
}
' file2 file1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.