CODEWITHSUNDEEP
mysqlruby-on-railsroo-gem
Harshini
Harshini

Reputation: 73

storing data from excel sheet to db in ror

Suppose I have a excel sheet that contains data to be stored in the db.I used roo gem and inserted the data from excel sheet successfully.Now I am trying to validate. Assume my excel sheet as:

s.no  name    age
 1    abc     12
 2    def     qwer
 3    asd     23

when I upload this sheet, the 2 row data is roll-backed and 1,3 row data is stored in db. What I am trying to do is if rollback operation occurs then the remaining records should not be stored in db.i.e the row 3 data should not be stored as row 2 is roll-backed.Any help is appreciated.Thanks.

An update: Here is my code in controller:

def  fetch_excel_data

ex = Roo::Excel.new("/desktop/abc.xls")
ex.default_sheet = ex.sheets[0]
2.upto(ex.last_row) do |line| 
    name = ex.cell(line,2)
    age = ex.cell(line,ex.last_column)
    byebug
    @product = Product.create(:name => name,:age => age)
     @product.save!
     flash[:success] = "data is stored successfully"
end
end

I want to roll-back entire excel sheet not only records..Is there any way to do so?

Upvotes: 1

Views: 256

Answers (2)

Deepak Mahakale
Deepak Mahakale

Reputation: 23671

You can break the loop if any record is not valid that will not save further records

Something like

CSV.foreach("file.csv", headers: true) do |row|
  user = User.new(name: row['name'], age: row['age'])
  break unless user.valid?
  user.save
end

EDIT:

Taking help from @Rais suggestion you can do somthing like this

User.transaction do
  CSV.foreach("file.csv", headers: true) do |row|
    User.create!(name: row['name'], age: row['age'])
  end
end

Upvotes: 1

Rais
Rais

Reputation: 69

Just to add,you can achieve atomicity using ActiveRecord Transactions

Quoting from rails api documentation-

Transactions are protective blocks where SQL statements are only permanent if they can all succeed as one atomic action. The classic example is a transfer between two accounts where you can only have a deposit if the withdrawal succeeded and vice versa. Transactions enforce the integrity of the database and guard the data against program errors or database break-downs. So basically you should use transaction blocks whenever you have a number of statements that must be executed together or not at all.

For example:

ActiveRecord::Base.transaction do
  david.withdrawal(100)
  mary.deposit(100)
end

This example will only take money from David and give it to Mary if neither withdrawal nor deposit raise an exception. Exceptions will force a ROLLBACK that returns the database to the state before the transaction began. Be aware, though, that the objects will not have their instance data returned to their pre-transactional state.

Upvotes: 0

Related Questions