csv updater

[Geoff]

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.

<<begin code>>

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

<<end code>>

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

 

[James Edward Gray II]

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_foreach_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 Gray II

 

[Geoff]

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_foreach_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!

 

[James Edward Gray II]

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_foreach_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 Gray II

 

[Geoff]

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