Csv updater

Ruby
1

Greetings!

I got some help on this a while back and have been tweaking on it
trying to get it do work right. What I want to do is take a csv file,
and whenever there is a blank cell update it with the info from the
cell directly above (and on down as long as there are blanks).

There are two problems with what I have so far:

  1. It does not retain the double quotes and I would like it to be able
    to do that.
  2. On a previous version it was working for just the last column. I
    have tried to adjust it to make it work for any column (at least that I
    list in the write(colNum) definition, but it is not working.

<>

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(‘C:\temp\geoff\filldown\filldownNew.txt’, ‘w’)

class ColumnWrite < BlankFiller
def write(colNum)
CSV.foreach(“C:\temp\geoff\filldown\filldown.txt”) do |row|
row[colNum] = blank_filler.fill(row[colNum])
out_csv << row
end
write(0)
write(1)
write(2)
end
end

<>

Here is the csv file I’m working with for a test case:

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

Ideas?

Thanks!

Geoff

2

On Mar 16, 2006, at 5:28 PM, Geoff wrote:

  1. It does not retain the double quotes and I would like it to be able
    to do that.
  2. On a previous version it was working for just the last column. I
    have tried to adjust it to make it work for any column (at least
    that I
    list in the write(colNum) definition, but it is not working.

I’m pretty sure this does what you want:

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

require “csv”
require “enumerator”

last_row = Array.new

CSV.foreach(ARGV.shift) do |row|
puts( row.enum_for(:each_with_index).map do |cell, index|
if cell.empty?
last_row[index]
else
last_row[index] = cell
end
end.map { |output| “”#{output}""}.join(",") )
end
Neo:~/Desktop$ ruby csv_filldown.rb test_data.csv > output.csv
Neo:~/Desktop$ cat output.csv
“BegDoc”,“EndDoc”,“New”
“Doc1BegDoc”,“Doc1EndDoc”,“Test1”
“Doc2BegDoc”,“Doc1EndDoc”,“Test1”
“Doc2BegDoc”,“Doc1EndDoc”,“Test1”
“Doc3BegDoc”,“Doc3EndDoc”,“Test2”
“Doc4BegDoc”,“Doc3EndDoc”,“Test2”
“Doc5BegDoc”,“Doc5EndDoc”,“New”

Hope that helps.

James Edward G. II

3

Cool, the puts command works, but when I tweak it to output this to a
file, the result is:

78,101,119
84,101,115,116,49
84,101,115,116,49
84,101,115,116,49
84,101,115,116,50
84,101,115,116,50
78,101,119

The tweak I used on the code is:

require “csv”
require “enumerator”

last_row = Array.new
out_csv = CSV.open(“C:\temp\geoff\filldown\filldownNew.txt”, “w”)
output = ‘’
CSV.foreach(“C:\temp\geoff\filldown\filldown.txt”) do |row|
puts( row.enum_for(:each_with_index).map do |cell, index|
if cell.empty?
last_row[index]
else
last_row[index] = cell
end
end.map { |output| “”#{output}""}.join(",") )
out_csv << output
end

I’m getting the feeling I just don’t understand programming and Ruby
enough at this point and need to re-read some intro material. Thanks a
bunch for spending time on this problem for me, I do appreciate it!

4

If you go back and look at my last message, I used the puts version
to create a file (using redirection). That’s a pretty flexible
trick, probably worth getting the hang of.

I’ll have to look into that. When I run that code it does not modify my
file, just puts to the shell. Sounds like a good trick, which I’ll
certainly look into.

The main problem with your new version is that you are using CSV to
manage the output file, but it order to enforce quoting we had to
roll our own solution (the CSV file format does not require it for
the examples you are showing). Switching to a normal file should get
you going:

Ah… works perfectly!

Thanks a ton for your help on this! I think I’ve learned more by trying
to solve this problem than I have the whole time reading tutorials and
such.

Geoff

5

On Mar 17, 2006, at 11:38 AM, Geoff wrote:

Cool, the puts command works, but when I tweak it to output this to a
file…

If you go back and look at my last message, I used the puts version
to create a file (using redirection). That’s a pretty flexible
trick, probably worth getting the hang of.

The main problem with your new version is that you are using CSV to
manage the output file, but it order to enforce quoting we had to
roll our own solution (the CSV file format does not require it for
the examples you are showing). Switching to a normal file should get
you going:

#!/usr/local/bin/ruby -w

require “csv”
require “enumerator”

last_row = Array.new

File.open(“C:\temp\geoff\filldown\filldownNew.txt”, “w”) do |
out_file|
CSV.foreach(“C:\temp\geoff\filldown\filldown.txt”) do |row|
out_file.puts( row.enum_for(:each_with_index).map do |cell, index|
if cell.empty?
last_row[index]
else
last_row[index] = cell
end
end.map { |output| “”#{output}""}.join(",") )
end
end

Hope that helps.

James Edward G. II

6

On Mar 17, 2006, at 12:38 PM, Geoff wrote:

I’ll have to look into that. When I run that code it does not
modify my
file, just puts to the shell. Sounds like a good trick, which I’ll
certainly look into.

It depends on how you run it:

ruby csv_filldown.rb # <= this just prints to the screen

ruby csv_filldown.rb > output.csv # redirected to a file

Thanks a ton for your help on this! I think I’ve learned more by
trying
to solve this problem than I have the whole time reading tutorials and
such.

Happy to help.

James Edward G. II