Rails CSV import, adding to a related table

Question

Hi,

I have a csv importing system on my app (used locally only) which parses the csv file line by line and adds the data to the database table. This is based on a tutorial here.

require 'csv'

def csv_import 
  @parsed_file=CSV::Reader.parse(params[:dump][:file])
  n = 0
  @parsed_file.each_with_index  do |row, i|
    next if i == 0  #ignore the first row
    course = Course.new
    course.title = row[0]
    course.unit_code = row[1]
    course.course_type = row[2]
    course.value = row[3]
    course.pass_mark = row[4]
    if course.save
      n = n+1
      GC.start if n%50==0
    end
    flash.now[:message] = "CSV Import Successful, #{n} new courses added to the database."
  end
  redirect_to(courses_url) 
end

This is all in the courses controller and works fine. There is a relationship that courses HABTM years and years HABTM courses. In the csv file (effectively in row[5] to row[8]) are the year_id s. Is there a way that I can add this within the method above. I am confused as to how to loop over the 4 items and add them to the courses_years table.

Thank you Jack

Answer 1

Have you tried to put either one of these before you save the course:

course.years.push(row[5])
course.years.push(row[6])
course.years.push(row[7])
course.years.push(row[8])

OR

course.years = [ row[5], row[6], row[7], row[8] ]

Place it before you save the course. It will fill the joint table courses_years.

EDIT

The error that you get seems to be because we are trying to put id's instead of objects, we should do this instead:

  .....
  year_array = Year.find(row[5], row[6], row[7], row[8])
  course.years << year_array
  .....

After we get the year objects, then we put it inside the association. You can save the course object after that.

Answer 2

You can do this by adding a simple loop after your "normal" data is added to the model, and using the << method to append to the years association.

...
course.value = row[3]
course.pass_mark = row[4]
5.upto(8).each do |i|
  one_year = Year.find(row[i])
  course.years << one_year if one_year
end
if course.save
  n = n+1
...

You can add more checks in the loop if you want to make sure that the values are valid, and/or change the find to locate your year in another way. Another way when the related data is "trailing off the end" like this is to keep adding until there is nothing left to add, and also to add the years themselves if they don't exist yet:

...
course.value = row[3]
course.pass_mark = row[4]
row[5..-1].each do |year_id|
  one_year = Year.find_or_create_by_id(year_id)
  course.years << one_year
end
if course.save
  n = n+1
...

There are a lot of different ways to do this, and the way which is right is really dependent on your actual data, but this is the basic method.