Csv nil check and update

Ruby
1

Greetings!

What I have is a .csv file (comma separated and quote delimited):

“BegDoc”,“EndDoc”,“New”
“Doc1BegDoc”,“Doc1EndDoc”,“Test1”
“Doc2BegDoc”,“Doc2EndDoc”,""
“Doc3BegDoc”,“Doc3EndDoc”,“Test2”
“Doc4BegDoc”,“Doc4EndDoc”,""
“Doc5BegDoc”,“Doc5EndDoc”,“New”

I can read the lines of the file with this:

require ‘CSV’
csvData = CSV.readlines(“C:\temp\geoff\filldown\filldown.txt”)

Now what I want to do is check for blanks and when I find one I want to
take the info from the entry directly above and fill down the column
until the next blank. Using the above example, I want the following
output:

“BegDoc”,“EndDoc”,“New”
“Doc1BegDoc”,“Doc1EndDoc”,“Test1”
“Doc2BegDoc”,“Doc2EndDoc”,“Test1”
“Doc3BegDoc”,“Doc3EndDoc”,“Test2”
“Doc4BegDoc”,“Doc4EndDoc”,“Test2”
“Doc5BegDoc”,“Doc5EndDoc”,“New”

Any help is greatly appreciated!

Thanks,

Geoff

2

On Mar 15, 2006, at 2:23 PM, Geoff wrote:

“Doc4BegDoc”,“Doc4EndDoc”,“Test2”
“Doc5BegDoc”,“Doc5EndDoc”,“New”

Any help is greatly appreciated!

See if this gives you some ideas:

Neo:~/Desktop$ ls
csv_filldown.rb data.csv
Neo:~/Desktop$ cat data.csv
“BegDoc”,“EndDoc”,“New”
“Doc1BegDoc”,“Doc1EndDoc”,“Test1”
“Doc2BegDoc”,“Doc2EndDoc”,""
“Doc3BegDoc”,“Doc3EndDoc”,“Test2”
“Doc4BegDoc”,“Doc4EndDoc”,""
“Doc5BegDoc”,“Doc5EndDoc”,“New”
Neo:~/Desktop$ cat csv_filldown.rb
#!/usr/local/bin/ruby -w

require “csv”

last = “”

CSV.foreach(ARGV.shift) do |row|
if row[-1].empty?
row[-1] = last
else
last = row[-1]
end

p row
end

END
Neo:~/Desktop$ ruby csv_filldown.rb data.csv
[“BegDoc”, “EndDoc”, “New”]
[“Doc1BegDoc”, “Doc1EndDoc”, “Test1”]
[“Doc2BegDoc”, “Doc2EndDoc”, “Test1”]
[“Doc3BegDoc”, “Doc3EndDoc”, “Test2”]
[“Doc4BegDoc”, “Doc4EndDoc”, “Test2”]
[“Doc5BegDoc”, “Doc5EndDoc”, “New”]

James Edward G. II

3

That does give me some ideas… thanks!

Now:

“Initialize” cannot convert nil to a string.

Any ideas on that one?

4

Got it, nevermind!

Thanks a ton for your help. :slight_smile:

5

You have an answer, but since I spent some time on it, here’s mine! 9^)

require ‘csv’
csvData = CSV.readlines(“d:\ruby\dev\filldown-csv\filldown.txt”)
puts ‘Before:’
csvData.each {|l| p l}

1.upto(csvData.size - 1){ |i|
0.upto(csvData[i].size - 1){|j|
csvData[i][j] ||= csvData[i-1][j]
}
}
puts ‘After:’
csvData.each {|l| p l}

For some reason my CSV wouldn’t read the data when it has quotes around
the values…

cheers
Chris

6

Thanks, I appreciate it. For some reason the output is the same as the
input when I try this though. Not sure why it does not work.

7

Ok, obiously I’m doing something wrong again. I am new to both
programming and to Ruby, so please excuse the low brow questions!

I’ve now got this because I really want to take the result and output
to a new file, but it does not work:

require ‘CSV’

last = “”
newFile = File.open(“C:\temp\geoff\filldown\filldownNew.txt”, “w+”)
CSV.foreach(“C:\temp\geoff\filldown\filldown.txt”) do |row|
if row[-1].empty?
row[-1] = last
else
last = row[-1]
end
newFile << (p row)
end

Ideas?

Thanks!

Geoff

8

Do you close the file?

I’m pretty sure output is buffered and if the file is not closed
properly it will not get flushed to disk.

Cheers

9

On Mar 15, 2006, at 5:48 PM, Geoff wrote:

+")
Change the above to:

newFile = CSV.open(…)

CSV.foreach(“C:\temp\geoff\filldown\filldown.txt”) do |row|
if row[-1].empty?
row[-1] = last
else
last = row[-1]
end
newFile << (p row)

And this to:

newFile << row

end

Ideas?

Also, just FYI, the Ruby naming convention for variables is
like_this, not likeThis.

Hope that helps.

James Edward G. II

10

Geoff wrote:

Thanks, I appreciate it. For some reason the output is the same as the
input when I try this though. Not sure why it does not work.

It occurred to me this morning (what else am I going to think about on
the bus 9^) that since your able to read the file with the
double-quotes, you need to check for missing field using ‘.empty?’
rather than ‘= nil’

cheers

11

On Mar 15, 2006, at 10:43 PM, ChrisH wrote:

Do you close the file?

I’m pretty sure output is buffered and if the file is not closed
properly it will not get flushed to disk.

All open files are closed when the Ruby interpreter exits normally.

James Edward G. II

12

Sweet… that’s certainly an improvement, but as you mention I do need
to retain the double quotes!

13

Geoff wrote:

newFile << (p row)
end

Do you need to preserve the redundant quotes in the output? If not,
this might be what you want:

 # vim:ts=4 sw=4 et
 require 'CSV'

 last = ''
 out_csv = CSV.open('output.csv', 'w')
 CSV.foreach('input.csv') do |row|
     if row[-1].empty?
         row[-1] = last
     else
         last = row[-1]
     end
     out_csv << row
 end
 out_csv.close

If it were me, I would probably group the datum “last” and the
functionality that dealt with it into a separate object, at the expense
of longer code. Maybe it’s premature factoring, but I can just see that
loop body getting more complicated as you want to do more with it, like
replacing all blank fields (instead of just the last one) with
previously read values.

 # vim:ts=4 sw=4 et
 require 'CSV'

 class BlankFiller
     def initialize(last='')
         @last = last
     end

     def fill(e)
         if e.empty?
             e = @last
         else
             @last = e
         end
     end
 end

 blank_filler = BlankFiller.new
 out_csv = CSV.open('output.csv', 'w')
 CSV.foreach('input.csv') do |row|
     row[-1] = blank_filler.fill(row[-1])
     out_csv << row
 end
 out_csv.close
14

Also, another thing that this should do eventually is to do this for
each column.