Forum: Ruby csv updater

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D3851c0aae6324a6d339cdefab56f452?d=identicon&s=25 Geoff (Guest)
on 2006-03-17 00:30
(Received via mailing list)
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
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2006-03-17 05:50
(Received via mailing list)
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 Gray II
D3851c0aae6324a6d339cdefab56f452?d=identicon&s=25 Geoff (Guest)
on 2006-03-17 18:40
(Received via mailing list)
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!
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2006-03-17 19:22
(Received via mailing list)
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 Gray II
D3851c0aae6324a6d339cdefab56f452?d=identicon&s=25 Geoff (Guest)
on 2006-03-17 19:41
(Received via mailing list)
>>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
4299e35bacef054df40583da2d51edea?d=identicon&s=25 James Gray (bbazzarrakk)
on 2006-03-17 19:51
(Received via mailing list)
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 Gray II
This topic is locked and can not be replied to.