Skipping headers in FasterCSV

ruby-talk
1

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

···

--
Posted via http://www.ruby-forum.com/.

2

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

John

···

--
Posted via http://www.ruby-forum.com/.

3

John Mcleod wrote:

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

···

--
Posted via http://www.ruby-forum.com/\.

4

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

···

--
Posted via http://www.ruby-forum.com/.

5

John Mcleod wrote:

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

···

--
Posted via http://www.ruby-forum.com/\.

6

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

···

--
Posted via http://www.ruby-forum.com/\.

7

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

···

--
Posted via http://www.ruby-forum.com/.

8

John Mcleod wrote:

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

···

--
Posted via http://www.ruby-forum.com/\.

9

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

···

--
Posted via http://www.ruby-forum.com/\.

10

Ah, found a solution.

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

···

--
Posted via http://www.ruby-forum.com/.