Solved

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

Posted on 2007-11-21
6
960 Views
Last Modified: 2010-05-18
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 !

0
Comment
Question by:dimple
6 Comments
 
LVL 6

Expert Comment

by:MorDrakka
ID: 20328176
I can write someting in VBscript. Are the codes always the same length ?
0
 
LVL 6

Expert Comment

by:MorDrakka
ID: 20328483
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
 
LVL 48

Assisted Solution

by:Tintin
Tintin earned 60 total points
ID: 20330557
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 84

Expert Comment

by:ozo
ID: 20341567
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
 
LVL 51

Expert Comment

by:ahoffmann
ID: 20343022
# 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
 
LVL 9

Accepted Solution

by:
ghostdog74 earned 65 total points
ID: 20345216
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This Windows batch file is useful for organizing image files from a digital camera or other source, but can have many other uses.  It simply renames the file(s) to match their create date.  For example, if you took a picture today at 1:40pm and the …
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now