# Several replacing problems

**URL:** <https://www.ruby-forum.com/t/several-replacing-problems/171607>\
**Category:** Ruby\
**Created:** [July 6, 2009, 4:36pm UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607 "2009-07-06T16:36:12Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![brunik](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e95f7d/32.png) [@brunik](https://www.ruby-forum.com/u/brunik)\
**Post date:** [July 6, 2009, 4:36pm UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607/1 "2009-07-06T16:36:12Z")

</div>

Hello everyone,

Take 1 xls file witch contains 2 columns. column A contains several  
“keywords” lets say: today, today+1, today+2 etc. and column B witch  
contains dates in yyyymmdd format 20090706, 20090707, 20090708 etc.

i have another file (txt) witch contains a bunch of text… including our  
“keywords”

What i need to do is… search in the txt file for the “keywords” and  
replace them with the dates and write the new content to a different  
file (same name and extension) in a different folder.

The code is as follows:

require ‘rubygems’  
require ‘roo’

oo=Excel.new(“data.xls”)  
1.upto(56000) do |line|  
template = oo.cell(line,‘A’)  
date = oo.cell(line,‘B’)  
date = date.to\_s.gsub!(’-’,’’)

date\_template = { /\b#{template}\b(?=\s|$)/ =\> “#{date}”}

Dir[’\*.txt’].each do |file\_name|  
replace = File.read( file\_name )  
if template != nil  
date\_template.each do |template, date|  
replace.gsub!( template, date )  
end  
puts replace  
end

File.makedirs(“results”)  
File::open(“results/#{file\_name}”,“w”) do |file\_name|  
file\_name\<\<replace  
end  
end  
end

Problems I have:

#1 The script enters a loop, freezes after a number of loops, then stops  
with exit code: 0  
#2 ‘puts replace’ shows me that the first loop replaces the first  
“keyword” it finds, the next ones (10-20) don’t replace anything then it  
stars replacing again  
#3 the file created is the same as the original (no replace was made)  
#4 i don’t really know if the regex above is correct. I used it to do a  
“whole word matching” and it seems to be ok for now.  
#5 problems I have yet do discover

I know the code is a mess but bare with me, I’ve been studying ruby only  
for a month or so.

Any tips & tricks will be greatly appreciated

I have attached the txt file witch needs to be read and replaced.

Thank you.

AC

---

<div class="post-metadata">

**Author:** ![brunik](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e95f7d/32.png) [@brunik](https://www.ruby-forum.com/u/brunik)\
**Post date:** [July 6, 2009, 5:12pm UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607/2 "2009-07-06T17:12:06Z")

</div>

Are your loops nested right? You seem to have an outer loop which reads  
one line of the Excel file, and then after doing that you read through  
all the files in the directory. This means that you will be reading and  
writing each of your files 56,000 times.

Better to read the Excel file once into memory (e.g. a Hash structure),  
and then process each file once. For each line, if the key is found in  
the Hash, replace it with the value in the Hash.

Also, in ruby 1.8.x beware of code like this:

File::open(“results/#{file\_name}”,“w”) do |file\_name|  
file\_name\<\<replace  
end

The inner ‘file\_name’ block variable aliases to the outer ‘file\_name’.  
That is, if file\_name contained a String before executing this code, it  
will contain a File afterwards. Better:

File::open(“results/#{file\_name}”,“w”) do |f|  
f\<\<replace  
end

---

<div class="post-metadata">

**Author:** ![brunik](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e95f7d/32.png) [@brunik](https://www.ruby-forum.com/u/brunik)\
**Post date:** [July 7, 2009, 10:24am UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607/3 "2009-07-07T10:24:48Z")

</div>

Brian C. wrote:

> Better to read the Excel file once into memory (e.g. a Hash structure),  
> and then process each file once. For each line, if the key is found in  
> the Hash, replace it with the value in the Hash.

this is exactly what i’m trying to do but can’t seem to get it right.  
can you give me an example?

> Also, in ruby 1.8.x beware of code like this:
> 
> File::open(“results/#{file\_name}”,“w”) do |file\_name|  
> file\_name\<\<replace  
> end
> 
> The inner ‘file\_name’ block variable aliases to the outer ‘file\_name’.  
> That is, if file\_name contained a String before executing this code, it  
> will contain a File afterwards. Better:
> 
> File::open(“results/#{file\_name}”,“w”) do |f|  
> f\<\<replace  
> end

thank you, i changed this

---

<div class="post-metadata">

**Author:** ![brunik](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e95f7d/32.png) [@brunik](https://www.ruby-forum.com/u/brunik)\
**Post date:** [July 8, 2009, 1:58pm UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607/4 "2009-07-08T13:58:55Z")

</div>

I managed to figure out how to do the hash and get rid of those annoying  
loops. Everything works fine except one thing:

the XLS contains the following:

A B  
TODAY 20090708  
TODAY+1 20090709  
TODAY+10 20090718  
TODAY+19 20090727  
TODAY+2 20090710  
TODAY+20 20090728  
TODAY+24 20090801  
TODAY+25 20090802  
TODAY+4 20090712  
TODAY+5 20090713  
TODAY+6 20090714  
TODAY+7 20090715  
TODAY+8 20090716  
TODAY+9 20090717  
TODAY-1 20090707  
TODAY-10 20090628  
TODAY-14 20090624  
TODAY-15 20090623  
TODAY-19 20090619  
TODAY-2 20090706  
TODAY-20 20090618  
TODAY-28 20090610  
TODAY-29 20090609  
TODAY-30 20090608  
TODAY-9 20090629  
TODAY-5 20090703  
TODAY-25 20090613  
TODAY+11 20090719  
TODAY+1Y 20100708

after replacing is done i get the following result:

> ruby read\_xl.rb  
> 20090708 =\> +t\_todaya+  
> 20090708 =\>  
> 20090708+1 =\> tp1a,  
> 20090718 =\> tp10a,  
> 20090727 =\> tp19a,  
> 20090708+2 =\> tp2a,  
> 20090728 =\> tp20a,  
> 20090801 =\> tp24a,  
> 20090708+25 =\> tp25a,  
> 20090708+4 =\> tp4a,  
> 20090713 =\> tp5a,  
> 20090714 =\> tp6a,  
> 20090715 =\> tp7a,  
> 20090716 =\> tp8a,  
> 20090717 =\> tp9a,  
> 20090719 =\> tp11a,  
> 20100708 =\> tp365a,  
> 20090628 =\> tm10a,  
> 200907074 =\> tm14a,  
> 200907075 =\> tm15a,  
> 20090619 =\> tm19a,  
> 20090708-2 =\> tm2a,  
> 20090618 =\> tm20a,  
> 20090610 =\> tm28a,  
> 20090609 =\> tm29a,  
> 20090608 =\> tm30a,  
> 20090629 =\> tm9a,  
> 20090707 =\> tm1a,  
> 20090708-5 =\> tm5a,  
> 20090708-25 =\> tm25a  
> Exit code: 0

Can anyone help me with a regex to fix the problem?  
If there is any other way to fix it i would apreciate the feedback

thanks.

---

<div class="post-metadata">

**Author:** ![brunik](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e95f7d/32.png) [@brunik](https://www.ruby-forum.com/u/brunik)\
**Post date:** [July 8, 2009, 2:00pm UTC](https://www.ruby-forum.com/t/several-replacing-problems/171607/5 "2009-07-08T14:00:16Z")

</div>

forgot the new code 😃

require ‘rubygems’  
require ‘roo’

h = {}  
oo=Excel.new(“data.xls”)

1.upto(29) do |line|  
a = oo.cell(line,‘A’)  
date = oo.cell(line,‘B’)  
b = date.to\_s.gsub!(’-’,’’)  
h[a] = b  
end

Dir[’\*.txt’].each do |file\_name|  
replace = File.read(file\_name)  
h.each do |template, date|  
replace.gsub!(template, date)  
end  
puts replace  
File.makedirs(“results”)  
File::open(“results/#{file\_name}”,“w”) do |f|  
f\<\<replace  
end  
end