Rails CSV import, adding to a related table

Question

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

jamuraa · Accepted Answer · 2010-05-13 15:04:32Z

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.

edited May 13, 2010 at 15:04

answered May 13, 2010 at 14:11

jamuraa

3,43728 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jack Over a year ago

Excellent. Thank you so much. Do you have any idea where I can find out more about the << method? It is quite difficult to find using google...

jamuraa Over a year ago

apidock.com is a great site for looking up rails related documentation. You're looking for ActiveRecord association methods I think: apidock.com/rails/ActiveRecord/Associations/ClassMethods

Jack Over a year ago

Bingo. Great site. One last thing. When I use your second example, the server seems to get stuck in a loop. Am I missing something?

Jack Over a year ago

Cheers for the fix. I'm still having some issues with it. Here is my code: pastie.org/958719 You have a nil object when you didn't expect it! You might have expected an instance of Array. The error occurred while evaluating nil.empty?

Jack Over a year ago

Absolutely excellent. Serious kudos, you've been a massive help.

|

Samuel Chandra · Accepted Answer · 2010-05-13 14:25:46Z

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.

edited May 13, 2010 at 14:25

answered May 13, 2010 at 13:57

Samuel Chandra

1,1757 silver badges13 bronze badges

3 Comments

Jack Over a year ago

Thanks for the response. I keep getting the following error: Year(#2183546760) expected, got CSV::Cell(#2156593920) Any suggestions?

Samuel Chandra Over a year ago

Check out my edited answer, I assume that the Year model data is available, if not, we will do it differently.

Jack Over a year ago

Excellent, cheers. Is there a simple way to put a loop around the array's creation, so that if a cell was empty it would skip it? jamuraa has tried this in his code, but it doesn't seem to work. Any ideas?

Collectives™ on Stack Overflow

Rails CSV import, adding to a related table

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related