Asp Forum - csv nil check and update

Geoff

3/15/2006 8:23:00 PM

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

12 Answers

James Gray

3/15/2006 8:35:00 PM

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

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

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

Geoff

3/15/2006 8:50:00 PM

That does give me some ideas... thanks!

Now:

"Initialize" cannot convert nil to a string.

Any ideas on that one?

Geoff

3/15/2006 9:04:00 PM

Got it, nevermind!

Thanks a ton for your help. :)

Chris Hulan

3/15/2006 9:20:00 PM

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

Geoff

3/15/2006 11:47:00 PM

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

Geoff

3/15/2006 11:48:00 PM

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.

James Gray

3/16/2006 4:00:00 AM

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

> 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
> +")

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

Chris Hulan

3/16/2006 4:40:00 AM

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

Chris Hulan

3/16/2006 1:37:00 PM

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

Jeff Schwab

3/16/2006 2:34:00 PM

Geoff wrote:

> 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

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

comp.lang.ruby

csv nil check and update

Geoff

James Gray

Geoff

Geoff

Chris Hulan

Geoff

Geoff

James Gray

Chris Hulan

Chris Hulan

Jeff Schwab

x Login to ForumsZone