asked on

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 !

MorDrakka

I can write someting in VBscript. Are the codes always the same length ?

MorDrakka

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

SOLUTION