Adding Additional Parameter to Excel Import in Rails

Question

I'm using Rails 7 with gem 'roo' 2.9 for doing an excel import. Everything went fine so far.

Now i want to delete part of formerly imported data from my database (postgresql) before i repeat the import with the same excel file.

I have no unique attribute or attribute set in my dataset to decide which data to delete.

Example: My Excelfile contains the following attributes:

• employee_name
• costcenter
• working_hours

I have an separate excelfile for every year. So when i repeat the import of an explicit file i want to delete only the data for the corresponding year. But the attribute "year" is not included in the importfile.

My idea is, to

1. add the attribute "year" to my model,
2. submit it from my gui during import and
3. save it to every dataset.

Topic 1 and 2 are working, but i have no idea, how to save it to the database. I am always getting a NIL value for the year.

This is my code:

model

class OvertimesImport
  include ActiveModel::Model
  require 'roo'

  attr_accessor :file
  attr_accessor :year

  def initialize(attributes={})
    attributes.each { |name, value| send("#{name}=", value) }
  end

  def persisted?
    false
  end

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

  def load_imported_overtimes
    spreadsheet = open_spreadsheet
    header = spreadsheet.row(1)
    (2..spreadsheet.last_row).map do |i|
      row = Hash[[header, spreadsheet.row(i)].transpose]
      overtime = Overtime.new
      overtime.attributes = row.to_hash
      overtime
    end
  end

  def imported_overtimes
    @imported_overtimes ||= load_imported_overtimes
  end

  def save
    if imported_overtimes.map(&:valid?).all?
      imported_overtimes.each(&:save!)
      true
    else
      imported_overtimes.each_with_index do |overtime, index|
        overtime.errors.full_messages.each do |msg|
          errors.add :base, "Row #{index + 2}: #{msg}"
        end
      end
      false
    end
  end

end

controller

class OvertimesImportsController < ApplicationController

  def new
    @overtimes_import = OvertimesImport.new
  end

  def create
    delete_old_overtimes
    @overtimes_import = OvertimesImport.new(params[:overtimes_import])
    respond_to do |format|
      if @overtimes_import.save
        format.html { redirect_to root_path, notice: "Overtimes successfully imported." }
      else
        format.html { render :new, status: :unprocessable_entity }
      end
    end
  end

  private

  #Delete all former import data
  #Here i want to delete only data from a special year
  def delete_old_overtimes
    Overtime.where(year: ???????).delete_all
  end

end

view

<%= form_for @overtimes_import do |f| %>
  <% if @overtimes_import.errors.any? %>
    <%= pluralize(@overtimes_import.errors.count, "error") %> prevented us from
    importing your spreadsheet. Please update your spreadsheet and try agin.
    <ul>
      <% @overtimes_import.errors.full_messages.each do |msg| %>
        <li><%= msg %></li>
      <% end %>
    </ul>
  <% end %>
  <div>
    <div class="row mt-4">
      <div class="col-4">
        <%= f.label :year, class: "form-label" %>
        <%= f.text_field :year %>
      </div>
    </div>
    <div class="row mt-4">
      <div class="col-4">
        <%= f.file_field :file %>
      </div>
    </div>
    <div class="row mt-4">
      <div class="col-2">
        <%= f.submit "Import File", class: 'btn btn-outline-success' %>
      </div>
    </div>
  </div>
<% end %>

How and where to put the code to submit the attribute "year" to my database and use it to delete the corresponding data before the import?

Answer 1

I think you must save the year to the database instead of using it like an attribute accessor.