Seeding Your Rails Database With a Spreadsheet
Did you find a great spreadsheet you would like to use to seed your database? Or maybe your client has given you an excel sheet of information that you need to seed the database with for their webpage? I’m sure you don’t really want to, nor have the time to copy and paste all of the information into your seed file. This would take precious time away from building out a beautiful application with a bunch of great functionality! Well, you’re in luck, because today I am going to give you a step by step guide to convert your spreadsheet to a CSV file to seed your database with.
Please keep in mind, the steps regarding a spreadsheet will be based off a spreadsheet created using Numbers on a Macbook. Windows may differ in a few keys, but the steps should generally be the same.
Cleaning Up Your Spreadsheet
Each row in your spreadsheet will account for an instance, while each column will be a value within this instance. If your spreadsheet has headers, the header of the column will be the key of the key value pair within this instance. That being said, this guid will assume, your spreadsheet has headers.
Please be sure the spreadsheet is set up with the first row being your headers and every row after that being your values that you would like to convert into an instance, of course matching each column in the row with it’s matching header.
You do not want any additional rows outside of this because it will ruin the set up of information being seeded and could cause null values within your seeded database. A title on your spreadsheet is okay, as long as it does not account for any cells. Your column headers must start on row 1 and your columns must begin at column A.
Exporting Your Spreadsheet As A CSV File
Next you want go to the “File” tab, then go to “Export As”, and lastly “CSV…” and it’s as easy as that. Now you have a CSV file that can be worked with in rails.
Adding The CSV File To Your Project
Now that we have a CSVversion of the spreadsheet, we can add this to our project for use. Inside of the “lib” folder, create a folder, which can be called anything, but for this demonstration, we will call the folder seeds. Then, you will need to place your CSV file in this folder. The filename should end with “.csv” to indicate that it is a CSV file.
Setting Up Your Seeds File To Read Your CSV File
Next you need to “require ‘csv’” at the top of your seeds file. This allows for the CSV data to be parsed by Ruby.
After that, you want to set up your seeds file to read from the CSVfile that was placed in the project. The second line is essentially saving the file as a variable for use when creating your seeds. Please keep in mind, you will be replacing “file_name.csv” with the actual file name you gave to your file in the previous step.
The last line is parsing the CSV file to be read by Ruby. You will place your variable name for the file as the first argument, the second argument is specifying whether or not the sheet has headers. In this demonstration we have true because our spreadsheet was set up with headers. The last argument is just encoding the data for Ruby. Not very important to know exactly what the last argument is doing, but you can do further research if you’d like.
Converting The Parsed Data To Create Instances
The additional lines should like somewhat familiar. We are essentially looping through our file, and creating a new instance for each row. The first line in our loop instantiates a new instance of our table (replace “Table” with your actual table name). Then your need to call on your new table variable column name and set it equal to the value in this row we are looping through at the particular header name from our spread sheet. Be sure your are righting your header name exactly as it was written in your spread sheet or else you will have null values because Ruby is case sensitive when parsing through this data. After your have parsed through the and set up all the necessary information, you take your table and save it to create the new instance within the database.
Seeding The Data
At this point, you have your seed file set up properly to be seeded, given you have your models properly set up. Now you may run “ $rails db:seed ” and your should be able to use these newly created seeds!
This may seem a bit lengthy, but that’s just because I was trying to be descriptive with each step so that you can build this up on your own, but honestly this is a super quick and easy set up! Hopefully you can take this information and seed your next project with a spreadsheet.
