Rails 5- How to import data from an xls file to the database

Question

I am trying to figure out how to add data from an xls file to my rails 5 psql database.

I have tried to follow the GoRails tutorials - but those become irrelevant quickly, because they use CSV files created somewhere in the app to do the tutorial. I don't know how to convert my xls data to csv data to follow along with that tutorial.

I have also tried each of these tutorials. I can't get any of them to work.

I have tried this tutorial, and this one, and this one. I can't get any of them to work.

The response to a question I posted on the blog for the first tutorial says to take a step not outlined in the post. It's not at all obvious to me that I'm supposed to fill in the gaps in the step by step instructions.

I outlined my attempts to get this working in this SO post. I wasn't able to find help to get the xls upload working. So, I'm setting out a request for help again - given the the first SO post became very long, with all the various attempts that I made to implement the suggested solutions.

I have:

class Randd::Field < ApplicationRecord

    require 'csv'


  # def self.import(file)
  #     CSV.foreach(file.path, headers: true) do | row |
  #         Randd::Field.create! row.to_hash
  #     end
  # end

  def self.create(file)
    CSV.foreach(file.path, headers: true) do |row|

      randd_field_hash = row.to_hash # exclude the price field
      randd_field = Randd::Field.where(id: randd_field_hash["id"])

      if randd_field.count == 1
        randd_field.first.update_attributes(randd_field_hash)
      else
        Randd::Field.create! row.to_hash#(randd_field_hash)
      end # end if !product.nil?
    end # end CSV.foreach
  end # end self.import(file)

First hunch: in this model, I'm requiring 'csv'. Do I have to require 'xls' or something else given that I don't actually have a list of comma separated values anywhere to import?

Routes.rb

namespace :randd do
    resources :fields do
      collection do
        post :create
      end
    end
  end

Controller:

class Randd::FieldsController < ApplicationController

def index
    @randd_fields = Randd::Field.all
end

def new
    @field = Randd::Field.new
end

def create
    # @field = Randd::Field.new(randd_field_params)
    Randd::Field.create(params[:randd_field][:file])
    redirect_to action: "index", notice: "Data imported"
end

def show
    redirect_to action: "index"
end

Index view:

  <table class="table table-bordered">
    <tr>
      <td> <h5>Title</h5> </td>
      <td> <h5>Reference (ANZ)</h5> </td>
      <td> <h5>Added</h5> </td>

     <%# if policy(@package_ips).update? %> 
        <td> <h5>Manage this asset</h5></td> 
     <%# end %>

    </tr>
      <% @randd_fields.each do | field | %>
        <td> <%= field.title %> </td>
        <td> <%= field.anz_reference %> </td>
        <td> <%= field.created_at.try(:strftime, '%e %B %Y') %> </td>
      <% end %>
    </tr>
  </table>

The commented components shown above show all the various options I tried to use to get this to work.

# def import
#   # byebug
#   # Randd::Field.import(params[:file])
#   # Randd::Field.create(params[:randd_field]['file'])
#   # redirect_to action: "index", notice: "Data imported"
# end

private
 def randd_field_params
  params.fetch(:randd_field, {}).permit(:title, :anz_reference)
 end

end

My form has:

<%#= simple_form_for (@field), multipart: true do |f| %>
<%= simple_form_for @field,  multipart: true do |f| %>
  <%= f.error_notification %>

  <div class="form-inputs" style="margin-bottom: 50px">
    <div class="row">
      <div class="col-md-12">
        <div class="form_title">Research Field Codes</div>
      </div>
    </div>

    <div class="row">
      <div class="col-md-12">
        <%= f.file_field :file %>
      </div>
    </div>
  </div>

  <div class="row">
    <div class="col-md-10 col-md-offset-1" style="margin-top: 50px">
      <div class="form-actions">
        <%= f.button :submit %>
      </div>
    </div>
  </div>
<% end %>

The error message that arises out of this attempt says:

NoMethodError - undefined method `[]' for nil:NilClass:
  app/controllers/randd/fields_controller.rb:13:in `create'

Line 13 of my create action says:

Randd::Field.create(params[:randd_field][:file])

Next hunch: my resources are nested. Randd is the top level and field is the child.

The model is called:

class Randd::Field < ApplicationRecord

The controller is called:

class Randd::FieldsController < ApplicationController

The permitted params in the controller are defined as:

  params.fetch(:randd_field, {}).permit(:title, :anz_reference)

The form uses this opening line:

<%= simple_form_for @field,  multipart: true do |f| %>

The line which is highlighted in the error message has:

Randd::Field.create(params[:randd_field][:file])

I wonder if the problem has something to do with using randd_field in some of these places and just @field in the form? The form doesnt work if I try to change it to @randd_field and I don't know where to find the ruby or rails rules for how to use namespaces in any complete, accurate form.

Can anyone help to see what's wrong here? Alternatively does anyone have a reference to a complete tutorial that doesn't rely on me guessing what the missing steps are or how to convert it for use with xls instead of a .csv file?

Answer 1

There are a few ruby xls parsers out there you could use. Creek, Simple Spreadsheet, and Roo.