How to write rake task to create data using excel in rails 3.2

Question

I want to create data by importing excel.

I have got the record of states in excel file and my state model has got only one field state_name.

I want to make a rake task to create all the record of all the states present in the excel file but I don't know how to work with excel.

I have created a basic rake task like this in the lib/tasks:-

namespace :state do
  task :create => :environment do
  end
end

How do I put the code which will read the excel file and create the states based on that ?

What do you mean by "state," like a region of a country? If so you might want to use a tool like [carmen-rails](https://github.com/jim/carmen-rails) which will handle that for you and translations. — Brandon Buck, Aug 30 '13 at 18:37
yes state like a region of a country, i don't want to use carmen-rails and want to do it with excel and rake — Dev R, Aug 30 '13 at 18:38

score 2 · Accepted Answer · answered Aug 30 '13 at 21:00

2

You can put the xls file into /tmp directory. Then put gem spreadsheet into your Gemfile. Then create xls.rb class into lib with a read method.

class Xls
  require 'spreadsheet'

  def self.read file_path
    Spreadsheet.open(file_path) do |book|
      book.worksheets.each do |ws|
        0.upto ws.last_row_index do |index|
          row = ws.row(index)
          yield ws, row
        end
      end
    end
  end

end

Then, into your task..

namespace :project do
  desc "Load xls"
  task :load => :environment do
    Xls.read("xls_file_path") do |sheet, row|
      state = row[0] #row[0] = the first column of the this row. 
      YourModel.save(state: state)
    end
  end
end

answered Aug 30 '13 at 21:00

Leantraxxx

4,506
3
38
56

Try to save your spreadsheet with "Microsoft Excel 97/2000/XP/2003 (.xls)" format first. I'm only showing how you could read an xls file and do the rake task. You can use anoher tool to make this if you need. [rubyxl](https://github.com/gilt/rubyXL) or maybe [simple-xls](https://github.com/mikeycgto/simple-xls) for example... – Leantraxxx Aug 31 '13 at 18:21
thanks it works +1 for the nice explanation, it will be great if you could write a post of how to make it work with roo as well :) – Dev R Sep 03 '13 at 14:59

Brandon Buck · Answer 2 · 2013-08-31T08:35:40.297

1

If you do it yourself you have to manage translations yourself, write all the helpers yourself and manage all the data yourself. With a library to do that you save yourself a lot of work and if you need to add or remove regions you can do that quickly and easily. I don't recommend doing it yourself.

If you are intent on doing it yourself then your best bet is to save the data in CSV format in a predictable way and read through the CSV creating entries. Assuming your CSV to be something of the format:

State,Country
Alabama,USA
Arkansas,USA
...

It would be simple to parse it:

require "csv"

task :load_states => :environment do
  CSV.foreach(csv_file, {headers: :first_row}) do |row|
    state = State.create(name: row["State"])
    state.country = Country.where(name: row["Country"]).first_or_create
    state.save
  end
end

edited Aug 31 '13 at 08:35

answered Aug 30 '13 at 19:26

Brandon Buck

7,177
2
30
51

Obviously omit the `{headers: :first_row}` if you don't have headers and access row elements by index (`row[0]`) instead of by header name. – Brandon Buck Aug 30 '13 at 19:27
you can remove the conditionals using first_or_create, i.e. `country = Country.where(name: row["Country"]).first_or_create` (rails 4) - see http://stackoverflow.com/questions/3046607/rails-find-or-create-by-more-than-one-attribute?answertab=active#tab-top – house9 Aug 30 '13 at 23:28
1

You are correct, glazed over that when I wrote the the suggestion. Thanks and updated the answer. – Brandon Buck Aug 31 '13 at 08:36

How to write rake task to create data using excel in rails 3.2

2 Answers2