Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Importing From Excel Files in Rails

    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 0
    • 0
    • 2.96k
    Comment on it

    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/
          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
          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
          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]
                file_ext = File.extname(file.original_filename)
                raise "Unknown file type: #{file.original_filename}" unless [".xls", ".xlsx"].include?(file_ext)
                spreadsheet = (file_ext == ".xls") ? :
                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])
                flash[:notice] = "Records Imported"
                redirect_to users_path 
              rescue Exception => e
                flash[:notice] = "Issues with file"
                redirect_to users_path 

    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.

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

 1 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: