Import Data from SQL file or Excel file into database via Rails

Question

I have data from an Excel spreadsheet that I want to import into a database via Rails. I am using Rails 3.2.3 and Ruby 1.9.2.

The data is usernames and passwords, file name is member.xlsx.

<%= form_for @member do |f| %>
    <%= f.text_field :import,
        :maxlength=>'50',
        :style=>'width: 250px;',
        :placeholder => 'browse file *.sql or *.xlsx' %>
    <%= f.submit  %><% end %>

My table:

create_table :members do |t|
  t.string :username
  t.string :password_hash
  t.string :password_salt

Solved

Solution

I'm using the spreadsheet gem for parse the Excel file and the CarrierWave gem for uploading the Excel file. Because I'm using devise model for user, here's what the controller for importing the file looks like

def import
    if params[:excel_file].nil?
      redirect_to user_new_import_path, :flash => { :error => 'You have not selected a file'}
    else
      test_file = params[:excel_file]
      file = ExcelUploader.new
      file.store!(test_file)
      book = Spreadsheet.open "#{file.store_path}"
      sheet1 = book.worksheet 0
      sheet1.each 1 do |row|
        name = row[0]
        email = row[2]
        generated_password = Devise.friendly_token.first(6) 
        temp_password = generated_password
        @user = User.create(:name => name, :email => email, :password => temp_password, :password_confirmation => temp_password)     
         UserMailer.password_send(@user).deliver
      end
      if @user.save
        redirect_to users_path, :notice => "success"
      else
        redirect_to new_import_user_path, :flash => { :error => 'error. try again' }
      end
    end
end

Answer 1

Importing a sql file is a horrible idea since the sql could do anything to your db.

Importing an Excel file works ok. There are Excel gems to read the data. The roo gem is available for the xlsx format but I haven't tested it. I tell users to use Excel to store the file in xls format and then they upload that.

Even better (from the app developer's point of view) is to import csv files. Downside of csv is that the file only contains exactly one table.

On the other hand, a problem with Excel import is that people can supply you with multiple tab files, with formulas rather than values in the cells, etc. -- So be sure to include plenty of error checking.

Frequent problems: people entering dates as strings in the Excel sheet when your code expects Date objects. Or numbers entered by people as strings. Your code should test and handle these issues.

See http://rubygems.org/gems/roo

Re: "give me some code" -- That's not how StackOverflow works. Hopefully the above will assist you on your journey.