Ruby, Generating Seeds with Excel

While working on the backend of a e-commerce clone application, my group had all of the product data stored in a excel file. It seemed tedious to go through and create the products again in the seeds.rb. There had to be a simpler way, and there is. This was our process. Yours may be different based on the version of Ruby you have, but the core concepts should be the same.

To start, make a new csv directory within the db directory.

Next, we need to make sure that the spreadsheet is formatted properly. The data needs to be stored in an actual table.

If your table has headers, make sure that the "My table has headers" box is checked. Ours included headers.

From here, save your file as CSV format to the csv directory you created earlier. Its important to note that each table needs to be its own file. You can not have multiple sheets tabs. So, if you have multiple tables, they need to be saved as individual csv files.

If you haven't generated your tables or models, now is a good time. Make sure your columns names match up with your csv columns names. If not, you will end up with your data in the wrong columns.

Now we can start coding in the seeds.rb file.

At the top of the file we need to include:

require "csv"

This provides the built in CSV class methods.

Next, we need to tell Ruby to read the content of the file and set that to a variable we can use:

csv_text = File.read('./db/csv/Products.csv')

Now, we need to parse the file:

csv_text = File.read('./db/csv/Products.csv')
csv = CSV.parse(csv_text, 
    :headers => true, 
    :header_converters => :symbol
)

Here we included the read file variable we just set, set headers to true, and converted the headers to symbols.

The next step is to iterate through the data and create a new product for each row.

csv_text = File.read('./db/csv/Products.csv')
csv = CSV.parse(csv_text, 
    :headers => true, 
    :header_converters => :symbol
)
csv.each do |row|
    Product.create!(row.to_hash)
end

Here we used .each as the iterator and told Ruby that for each row we wanted to call Product.create. We also included the bang operator "!" to raise an exception if the data does not match the data types that were defined in the Product table. Finally, we called the to_hash method on each row, to ensure that each row is returned as a hash.

You can repeat this process for each of your csv files.