Skipping headers in FasterCSV

[John Mcleod]

Hello all,
I'm having one heck of a day.
I found out that Oracle has a identifier limit and I have several
columns that have a names longer than 30 chars.

What does this have to do with FasterCSV you ask?

I have a method in a controller that parses a .csv then writes to a
database table.

- controller -

def import_irb_file
# set file name
file = params[:irb][:file]
rowcount = 0

Irb.transaction do
FasterCSV.parse(file,
:headers => true,
:header_converters => lambda { |h| h.tr(" ",
"_").delete("^a-zA-Z0-9_")},
:converters => :all ) do |row|
Irb.create( {"reconciled" => 0,
"non_related" => 0
}.merge(row.to_hash))
rowcount += 1
end
end

# if successful then display, then redirect to index page
flash[:notice] = "Successfully added #{rowcount} IRB record(s)."
redirect_to :action => :index

rescue => exception
file_name = params[:irb]['file'].original_filename
file_parts = params[:irb]['file'].original_filename.split('.')
ext = file_parts[1]

if ext != 'csv'
error = "CSV file is required"
else
error = ERB::Util.h(exception.to_s) # get the error and HTML
escape it
end
# If an exception in thrown, the transaction rolls back and we end
up in this
# rescue block

flash[:error] = "Error adding projects to IRB table. (#{error}).
Please try again."

redirect_to :controller => 'irbs', :action => 'new'

end

If I rename several table columns, how can I skip reading the headers in
the .csv then write to the columns correctly.
I read a post that ":headers => true", allows reading the headers but
does not return them.
That's something I can use. But when I edit my csv portion to...

Irb.transaction do
FasterCSV.parse(file,
:headers => true) do |row|
Irb.create( {"reconciled" => 0,
"non_related" => 0
}.merge(row.to_hash))
rowcount += 1
end
end

I get an "Error adding projects to IRB table. (unknown attribute: Q.1B
PI8 Last). Please try again." error.

Thank you for any help with this.

JohnM

 

[John Mcleod]

I guess what I'm asking is...
How do I imput csv data regardless what the database table column's
titles are?

John

 

[Brian Candler]

I guess what I'm asking is...
How do I imput csv data regardless what the database table column's
titles are?

Try using Row#fields

# This method accepts any number of arguments which can be headers,
indices,
# Ranges of either, or two-element Arrays containing a header and
offset.
# Each argument will be replaced with a field lookup as described in
# FasterCSV::Row.field().

Should be something like this:

.... do |row|
puts row.fields(0,1,2,3,4,5)
end

 

[John Mcleod]

Thanks Brian for the reply.

I tried your advice and yes it does retrieve the row.

Irb.transaction do
FasterCSV.parse(file, :headers => true) do |row|
puts "Row:" + row.fields(0,1,2,3,4,5).to_s
Irb.create(row.to_hash)
end
end

I still get an error...

"Error adding projects to IRB table. (unknown attribute: Q.1B PI8 Last).
Please try again."

It looks like it's trying to match the header of the csv with the column
title.

Also, if I just do this...

Irb.transaction do
FasterCSV.parse(file, :headers => true) do |row|
puts "Row:" + row.to_s
Irb.create(row.to_hash)
end
end

I get all row data with commas. So I am getting the row data without the
headers.


John

 

[Brian Candler]

Thanks Brian for the reply.

I tried your advice and yes it does retrieve the row.

Irb.transaction do
FasterCSV.parse(file, :headers => true) do |row|
puts "Row:" + row.fields(0,1,2,3,4,5).to_s
Irb.create(row.to_hash)
end
end

I still get an error...

"Error adding projects to IRB table. (unknown attribute: Q.1B PI8 Last).
Please try again."

That's because you're still using row.to_hash and not row.fields !!
Try something like this:

Irb.create("foo" => row.field(0),
"bar" => row.field(1),
"baz" => row.field(2))

You should also be able to use row.fields without any args to get a flat
array.

f = row.fields
Irb.create("foo"=>f[0], "bar"=>f[1], "baz"=>f[2])

Also, if I just do this...

Irb.transaction do
FasterCSV.parse(file, :headers => true) do |row|
puts "Row:" + row.to_s
Irb.create(row.to_hash)
end
end

I get all row data with commas. So I am getting the row data without the
headers.

Yes. row.to_s converts back to a CSV row. From the source:

def to_csv(options = Hash.new)
fields.to_csv(options)
end
alias_method :to_s, :to_csv

 

[John Mcleod]

Thanks again.

BINGO!

Try something like this:

Irb.create("foo" => row.field(0),
"bar" => row.field(1),
"baz" => row.field(2))

I replaced "foo","bar", and "baz" with database table column titles and
the "create" went through fine.

Thank you very much with the advice. I'm still learning every day, as
I'm up to 4-5 months with Ruby on Rails.

A funny thing, the more I learn, the more the users want more.

John

 

[John Mcleod]

Hold on there cowboy!!!!

I committed everything to Production and BLAM!

My development database is sqlite3 and my production is Oracle.

When I try to do the import I get the following...

"Error adding projects to IRB table. (undefined method `IRB' for
#<Irb:0xd9a3aa8>). Please try again."

When I comment out some of the important code, namely the create ...

def import_irb_file
# set file name
file = params[:irb][:file]
rowcount = 0

Irb.transaction do
# using ":headers => true", this will read but skip the header
row in the .csv file.
FasterCSV.parse(file, :headers => true) do |row|
# set row data to an Array
f = row.fields

=begin
Irb.create("irb_number" => f[0],
"pi_full_name" => f[1])
=end
rowcount+=1
end
end

Everything works fine.

I checked all column titles and I can't see where the problem is.

John

 

[Brian Candler]

"Error adding projects to IRB table. (undefined method `IRB' for
#<Irb:0xd9a3aa8>). Please try again."

Where did "Please try again" come from? Is this your own exception
handler? Try commenting that out so you get a full backtrace.

There's probably an AR method you can use to inspect the class
(Irb.column_names maybe?) And in any case, check there are accessor
methods for the columns you expect, e.g.

x = Irb.new
x.irb_number = 123
x.pi_full_name = "bar"

If this is a rails app, use "script/console production" as an easy way
to do this. It fires up a Ruby IRb shell with your models already
loaded.

And finally, if you are developing for Oracle, then your dev database
should be Oracle too! There is a free edition of Oracle (XE) which is
just the job. Limited to 1 CPU, 1GB RAM, 4GB table space.

http://www.oracle.com/technology/products/database/xe/index.html

 

[John Mcleod]

Sorry, I should have posted the entire method.

Where did "Please try again" come from? Is this your own exception
handler? Try commenting that out so you get a full backtrace.

def import_irb_file
# set file name
file = params[:irb][:file]
rowcount = 0

Irb.transaction do
# using ":headers => true", this will read but skip the header
row in the .csv file.
FasterCSV.parse(file, :headers => true) do |row|
# set row data to an Array
f = row.fields

Irb.create("irb_number" => f[0],
"pi_full_name" => f[1] )
rowcount+=1
end
end

# if successful then display, then redirect to index page
flash[:notice] = "Successfully added #{rowcount} IRB record(s)."
redirect_to :action => :index

rescue => exception
file_name = params[:irb]['file'].original_filename
file_parts = params[:irb]['file'].original_filename.split('.')
ext = file_parts[1]

if ext != 'csv'
error = "CSV file is required"
else
error = ERB::Util.h(exception.to_s) # get the error and HTML
escape it
end
# If an exception in thrown, the transaction rolls back and we end
up in this
# rescue block

flash[:error] = "Error adding projects to IRB table. (#{error}).
Please try again."

redirect_to :controller => 'irbs', :action => 'new'

end

I will check for any AR methods for inspecting.

thanks again.

John

 

[John Mcleod]

Ah, found a solution.

I forgot to check the model. I had a validation set with a different
column title.