When working on projects I like to create sample data in Excel and import that data into my database. I prefer this to the default rake db:seed options. Loading from Excel works nicely in production or when a computer illiterate client wishes to add lots of records to their database. For example, they could add 100 products into excel and upload the file instead of touching the code, or navigating a UI in order to create new products.

Accomplishing Excel loading in Rails is easy by using the default seeds.rb file and the gem Roo; which provides an interface to spreadsheets. Typically I have each sheet represent a different table.

#load the excel file
excel_file = Roo::Spreadsheet.open(‘public/loader/tv_seed_data.xls’)
#navigate to specific sheet
excel_file.sheet(0)
#select row range you wish to iterate over
4.upto(500) do |line|
  name = excel_file.cell(line, ‘B’)
  description = excel_file.cell(line, ‘C’)
  Show.create!(name: name, description: description)
end

You can expand this to include foreign keys, where the corresponding id fields are stored in the Excel sheet. For example, the Episodes Excel sheet would have a column with a number that corresponds to a specific Show in the Show Excel Sheet

excel_file = Roo::Spreadsheet.open(‘public/loader/tv_seed_data.xls’)
#Load the Show's first, save the objects in a hash with the key being an integer
#starting at 0
index = 0
@shows = {} 
excel_file.sheet(0)
4.upto(500) do |line|
  name = excel_file.cell(line, ‘B’)
  description = excel_file.cell(line, ‘C’)
  @shows[index] = Show.create!(name: name, description: description)
  index = index + 1
end
#switch to a new sheet, reset index
index=0
@episodes = {}
excel_file.sheet(1)
4.upto(500) do |line|
  name = excel_file.cell(line, ‘B’)
  description = excel_file.cell(line, ‘C’)
  #column D contains number which corresponds to the show number from previous sheet
  television_show = excel_file.cell(line, ‘D’)
  @episodes[index] = Episode.create(name: name, description: description, show: show[television_show.floor]
  index = index + 1
end

When using paperclip you can quickly upload images

image_name = excelFile.cellLine(‘E’)
#where image_path is file path to the images
image_file_path = “#{image_path}#{image_name}”
Episode.create!(name: name, description: description, show: show, image: File.new(image_file_path)

For some of my applications I find it convenient to populate only certain tables. This can be nice when only a few tables need to be populated and the rest of the database can remain untouched. In this situation I add environment variables to the rake command i.e. rake db:seed ALL=True or rake db:seed Users=true and corresponding if statements for which tables to reload.

If ENV[‘USERS’]
  #load users table
else
  #don’t load the users table
end