0

I am trying to retrieve some data from an excel 2010 workbook and store into sqlite3 database with Rails and Roo gem. The application is not saving all the attributes to the database as I want.

Here is my import method in the Beneficiary model

def self.import(file)
  spreadsheet = open_spreadsheet(file)
  spreadsheet.each_with_pagename do |name, sheet|
    for i in 1..sheet.last_row do
      beneficiary = Beneficiary.new()
      beneficiary.name, beneficiary.gender, beneficiary.age, beneficiary.in_school, beneficiary.venue =
      sheet.excelx_value(i, 2), sheet.excelx_value(i, 3), sheet.excelx_value(i, 4), sheet.excelx_value(i, 5), sheet.excelx_value(1, 1)
      beneficiary.save!
    end
  end
end

def self.open_spreadsheet(file)
  case File.extname(file.original_filename)
    when ".csv" then
      Roo::CSV.new(file.path)
    when ".xls" then
      Roo::Excel.new(file.path)
    when ".xlsx" then
      Roo::Excelx.new(file.path)
    else
      raise "Unknown file type: #{file.original_filename}"
  end
end

And here is my database migration script:

class CreateBeneficiaries < ActiveRecord::Migration
  def change
    create_table :beneficiaries do |t|
      t.string :name #, null: false
      t.integer :age #, null: false
      t.string :gender #, null: false
      t.string :in_school #, null: false
      t.string :venue
      t.string :learning_center
      t.string :facilitator
      t.timestamps null: false
    end
  end
end

When I included the not null constraints, I got errors, but when i removed them, some attributes where not saved. Hence I think the problem is with my import method, but I don't know why.

Any help will be much appreciated.

Sablefoste
  • 4,032
  • 3
  • 37
  • 58
Awa Desmoline
  • 495
  • 4
  • 26
  • can you show errors you are getting? – Pardeep Dhingra Dec 31 '15 at 12:43
  • @PardeepDhingra it give an error like this when i uncomment the not null constraints, SQLite3::ConstraintException: NOT NULL constraint failed: beneficiaries.in_school: INSERT INTO "beneficiaries" ("name", "gender", "age", "venue", "created_at", "updated_at") VALUES (?, ?, ?, ?, ?, ?). But when i remove the constraints, it does not save all the attributes. – Awa Desmoline Dec 31 '15 at 15:07
  • I guess it's because of `age` column. Its integer but `roo` default will return it as `string` which is marking it `null`. Try after removing `false` validation from `age` col. – Pardeep Dhingra Jan 01 '16 at 02:13

0 Answers0