[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-11-21
6
Medium Priority
?
972 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 240 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 85

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 260 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

873 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