title: Rails 5- How to import data from an xls file to the database
body: <p>I am trying to figure out how to add data from an xls file to my rails 5 psql database.</p>
  
  <p>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.</p>
  
  <p>I have also tried each of these tutorials. I can't get any of them to work. </p>
  
  <p>I have tried <a href="http://www.mattmorgante.com/technology/csv" rel="nofollow noreferrer">this tutorial</a>, and <a href="https://richonrails.com/articles/importing-csv-files" rel="nofollow noreferrer">this one</a>, and <a href="https://deepakrip007.wordpress.com/2013/12/14/import-csv-file-into-the-database-in-rails-application/" rel="nofollow noreferrer">this one</a>. I can't get any of them to work.</p>
  
  <p>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. </p>
  
  <p>I outlined my attempts to get this working in this <a href="https://stackoverflow.com/questions/40579864/rails-5-how-to-upload-excel-files-tutorials-not-working-out">SO post</a>. 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.</p>
  
  <p>I have:</p>
  
  <pre><code>class Randd::Field &lt; 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)
  </code></pre>
  
  <p><strong>First hunch</strong>: 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?</p>
  
  <p>Routes.rb</p>
  
  <pre><code>namespace :randd do
      resources :fields do
        collection do
          post :create
        end
      end
    end
  </code></pre>
  
  <p>Controller:</p>
  
  <pre><code>class Randd::FieldsController &lt; 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
  </code></pre>
  
  <p>Index view:</p>
  
  <pre><code>  &lt;table class="table table-bordered"&gt;
      &lt;tr&gt;
        &lt;td&gt; &lt;h5&gt;Title&lt;/h5&gt; &lt;/td&gt;
        &lt;td&gt; &lt;h5&gt;Reference (ANZ)&lt;/h5&gt; &lt;/td&gt;
        &lt;td&gt; &lt;h5&gt;Added&lt;/h5&gt; &lt;/td&gt;
  
       &lt;%# if policy(@package_ips).update? %&gt; 
          &lt;td&gt; &lt;h5&gt;Manage this asset&lt;/h5&gt;&lt;/td&gt; 
       &lt;%# end %&gt;
  
      &lt;/tr&gt;
        &lt;% @randd_fields.each do | field | %&gt;
          &lt;td&gt; &lt;%= field.title %&gt; &lt;/td&gt;
          &lt;td&gt; &lt;%= field.anz_reference %&gt; &lt;/td&gt;
          &lt;td&gt; &lt;%= field.created_at.try(:strftime, '%e %B %Y') %&gt; &lt;/td&gt;
        &lt;% end %&gt;
      &lt;/tr&gt;
    &lt;/table&gt;
  </code></pre>
  
  <p>The commented components shown above show all the various options I tried to use to get this to work.     </p>
  
  <pre><code># 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
  </code></pre>
  
  <p>My form has:</p>
  
  <pre><code>&lt;%#= simple_form_for (@field), multipart: true do |f| %&gt;
  &lt;%= simple_form_for @field,  multipart: true do |f| %&gt;
    &lt;%= f.error_notification %&gt;
  
    &lt;div class="form-inputs" style="margin-bottom: 50px"&gt;
      &lt;div class="row"&gt;
        &lt;div class="col-md-12"&gt;
          &lt;div class="form_title"&gt;Research Field Codes&lt;/div&gt;
        &lt;/div&gt;
      &lt;/div&gt;
  
      &lt;div class="row"&gt;
        &lt;div class="col-md-12"&gt;
          &lt;%= f.file_field :file %&gt;
        &lt;/div&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  
    &lt;div class="row"&gt;
      &lt;div class="col-md-10 col-md-offset-1" style="margin-top: 50px"&gt;
        &lt;div class="form-actions"&gt;
          &lt;%= f.button :submit %&gt;
        &lt;/div&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  &lt;% end %&gt;
  </code></pre>
  
  <p>The error message that arises out of this attempt says:</p>
  
  <pre><code>NoMethodError - undefined method `[]' for nil:NilClass:
    app/controllers/randd/fields_controller.rb:13:in `create'
  </code></pre>
  
  <p>Line 13 of my create action says:</p>
  
  <pre><code>Randd::Field.create(params[:randd_field][:file])
  </code></pre>
  
  <p>Next hunch: my resources are nested. Randd is the top level and field is the child.</p>
  
  <p>The model is called:</p>
  
  <pre><code>class Randd::Field &lt; ApplicationRecord
  </code></pre>
  
  <p>The controller is called:</p>
  
  <pre><code>class Randd::FieldsController &lt; ApplicationController
  </code></pre>
  
  <p>The permitted params in the controller are defined as:</p>
  
  <pre><code>  params.fetch(:randd_field, {}).permit(:title, :anz_reference)
  </code></pre>
  
  <p>The form uses this opening line:</p>
  
  <pre><code>&lt;%= simple_form_for @field,  multipart: true do |f| %&gt;
  </code></pre>
  
  <p>The line which is highlighted in the error message has:     </p>
  
  <pre><code>Randd::Field.create(params[:randd_field][:file])
  </code></pre>
  
  <p>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.</p>
  
  <p>Can anyone help to see what's wrong here? Alternatively does anyone have a reference to a <strong>complete</strong> 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?</p>