Importing excel files in Rails

Hi friends,

Importing excel sheets and storing them into db is a very common usecase of applications. So here I am going to guide you to a step-by-step process of how to import records into db from an excel file in Rails.
1. Create a rails application

	>> rails new sample_import_csv

2. Create a User model using scaffold with only two fields first_name and last_name in it. It will create all the CRUD operation for User model.

	>> cd sample_import_csv
	>> rails g scaffold user first_name:string last_name:string
	#=>
	invoke  active_record
      create    db/migrate/20160408072220_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml
      invoke  resource_route
       route    resources :users
      invoke  scaffold_controller
      create    app/controllers/users_controller.rb
      invoke    erb
      create      app/views/users
      create      app/views/users/index.html.erb
      create      app/views/users/edit.html.erb
      create      app/views/users/show.html.erb
      create      app/views/users/new.html.erb
      create      app/views/users/_form.html.erb
      invoke    test_unit
      create      test/controllers/users_controller_test.rb
      invoke    helper
      create      app/helpers/users_helper.rb
      invoke      test_unit
      invoke    jbuilder
      create      app/views/users/index.json.jbuilder
      create      app/views/users/show.json.jbuilder
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/users.coffee
      invoke    scss
      create      app/assets/stylesheets/users.scss
      invoke  scss
      create    app/assets/stylesheets/scaffolds.scss

3. Now set the root of the server to its the index of the user by changing config/routes.rb to

      # You can have the root of your site routed with "root"
      root 'users#index'

4. Run migration and start the server and navigate in it by adding the user records by going to http://localhost:3000

      >> rake db:migrate
      >> rails s

5. Now here for importing users from excel or csv, I am adding here two gems in Gemfile

      # Used for importing, reading excel files
      # @link https://github.com/roo-rb/roo
      gem 'roo'

      # This library extends Roo to add support for handling class Excel files, including:
      # .xls files
      # .xml files in the SpreadsheetML format (circa 2003)
      # @dependent on roo gem
      # @link https://github.com/roo-rb/roo-xls
      gem 'roo-xls'

6. Run bundle and after that add a route in the config/routes.rb for importing the xls file.

      post 'import_from_excel' => "users#import_from_excel"

7. Create an action inside the controller to read the records and save it in the users table

       def import_from_excel
          file = params[:file]
          begin
            file_ext = File.extname(file.original_filename)
            raise "Unknown file type: #{file.original_filename}" unless [".xls", ".xlsx"].include?(file_ext)
            spreadsheet = (file_ext == ".xls") ? Roo::Excel.new(file.path) : Roo::Excelx.new(file.path)
            header = spreadsheet.row(1)
            ## We are iterating from row 2 because we have left row one for header
            (2..spreadsheet.last_row).each do |i|
              User.create(first_name: spreadsheet.row(i)[0], last_name: spreadsheet.row(i)[1])
            end
            flash[:notice] = "Records Imported"
            redirect_to users_path 
          rescue Exception => e
            flash[:notice] = "Issues with file"
            redirect_to users_path 
          end
       end

8. So that's it your records can now be imported from excel into your db. The excel file should be according to your need. This sample application is also available in Github. https://github.com/raghvendra1501/sample_import_csv.git

Hope you liked reading this blog. For more like this Click here.