Loading Excel Data Into an ActiveRecord Database

Do you have a large excel file that you would like to get into your ruby program or rails database? There’s a gem for that! Either install the csv gem and require it, or add it to your Gemfile.

$ gem install csv

#in your .rb file

require ‘csv’

or in your Gemfile

gem ‘csv’

Once you have the necessary gem, you can now use the CSV class and its methods. The CSV class has around 125 methods, but I’ll only go over a few here.

CSV files

As the name of the gem suggests, the Excel file you want to use needs to be in a comma separated values or csv format. If you already have your file in .csv format you can move on to the next section. If your file is currently in an .xlsx or other Excel format you can convert your file into to .csv. CSV files can only have one sheet, as they are technically stored as text files. If you have more than one sheet you’ll want to create separate files for each sheet. Also to make sure you’re just getting the data you want, you may want to delete any columns or rows that are not specifically the data that you need in your database (header rows can be left in). After cleaning up those two things, simply use File>Save As on Windows or File>Save a Copy on Mac and underneath the file name chose CSV as your file format.

Read a .csv file

Once you have the .csv file ready to go, you can use the CSV class method .read

data = CSV.read(“path/my_csv_file.csv”)
data[0][1] #=> data from the first row and the second column

If your file had a header row at the top, there is another method that is better to use. The CSV .parse method has similar functionality to read however it will remove the headers from the array, but still keep track of the column names.

data = CSV.parse(File.read((“path/my_csv_file.csv”), headers: true)
data[0][1] #=> data from the first non-header row and the second column

This parse method will return an object of type CSV::Table instead of an array. For copying in data you don’t have to worry about that too much, for the most part you can just think of it as an array. It does come with a few hash-like features which we’ll get to next. Using whichever method better fits your csv file, you now have access to it in Ruby!

Save the data in your database

Now that you have your data stored in a variable, we can use it to create or update rows in our Rails tables. Because of the ordering of the data that was read/parsed we can call .each on our data to process it row by row. After saving the data into a variable using read or parse we can create or update a row in one of our database tables by doing the following:

Create

When using the the .read method you’ll want to find the column number of that csv file that matches the data you’re looking for (remember to start at 0 when counting). You can then use ActiveRecord’s .create to add a new entry, using row[column_number] for each database column required. (Note: try using IRB to check the first few rows (data[0], data[1], data[2], etc.) to make sure it’s working as intended before creating a bunch of rows in your database)

data.each do |row|
    Model.create(
        name: row[0]
        age: row[1]
        email: row[5]
    )
end

When .parse was the method used on a file with headers in the original .csv, we also have some added functionality that comes with the CSV::Table object it creates. The above code will still work, however we now have the ability to use the column header name instead of its number (a lot more readable!).

data.each do |row|
    Model.create(
        name: row[“name”]
        age: row[“age”]
        email: row[“email”]
    )
end

Update

Updating would be done in a similar manner as create. Because we’re wanting to update already created rows in the database, we’ll first need to find the row to update. This can be done using data from the .csv as well as seen below.

data.each do |row|
    student = Model.find_by(name: row["name"])
    student.update(
        email: row[“email”]
    )
end

References

https://github.com/ruby/csv