Use RAKE Task to move CSV to POSTGRES relational DB

Question

I have the following models.

class Lesson < ActiveRecord::Base
  has_many :books
  has_many :vocabularies
  has_many :sentenses
end

class Book < ActiveRecord::Base
  belongs_to :lesson
end

class Vocabulary < ActiveRecord::Base
  belongs_to :lesson
end

class Sentense < ActiveRecord::Base
  belongs_to :lesson
end

With the following table schema:

Table Lesson [lesson_number, lesson_name]
Table Books [lesson_id, page_start, page_finish]
Table Vocabulary [lesson_id, word, meaning]
Table Sentences [lesson_id, sentence, sentence meaning]

And I have a CSV file with 15,000 lessons. The CSV file uses the same structure of 2 books, 10 vocabulary, 2 sentences consistently throughout all lesson plans.

My thoughts are to start like this.

namespace :import_csv do
  desc "IMPORT Lessons"
  task :lessons => :environment do
    CSV.foreach('CcyTbl.csv') do |row|
      lesson_name_id = row[0]
      lesson_name = row[1]
      Lesson.create(lesson_name_id: lesson_name_id, lesson_name: lesson_name)
    end
  end

  desc "IMPORT BOOKS"
  task :books => :environment do
    CSV.foreach('CcyTbl.csv') do |row|
      lesson_name_id = row[0]
      book_name = row[3]
      book_start_pg = row[7]
      book_end_pg = row[8]
      Lesson.create(lesson_name_id: lesson_name_id, book_name: book_name, book_end_pg: book_end_pg)
  end
end

That much seems straight forward but I am struggling with:

How to handle null values.
Some lessons have two books (Think column 3 has book1 and book2 is column 9 and sometimes book2 is null)
Lessons might have 5-10 vocabulary words (Column 10 vocabulary 1, column 11 vocabulary 1 meaning, column 12 vocabulary, etc)

What is the best way to import the data in this CSV into their respective tables? Does it make more sense to create multiple rake tasks to do each portion or can it be done in one go?

UPDATE Here is a link to a sample of the header row and first row of data. (It is a bit too long to share a picture.)

seems like you're taking the right approach. Null values don't complicate things much. If some of your CSV data is failing your model-level validation, either remove the validation or give default values. As far as whether you should use one rake task or many ... it doesn't really matter imo, just branch out your code when you feel it's too much to handle in one file. — max pleaner
– max pleaner, Commented Mar 26, 2016 at 3:17
Could you update your question with the header and the first data row from the CSV file. — Dharam Gollapudi
– Dharam Gollapudi, Commented Mar 26, 2016 at 5:11
@Dharam added a link to a google docs sample. It was a bit too long to upload otherwise. If there is a better way to share please let me know! — CheeseFry
– CheeseFry, Commented Mar 26, 2016 at 7:11

B Seven · Accepted Answer · 2016-04-07 23:34:43Z

1

You may want to create a data object that makes it easier to work with the CSV data. Decoupling the CSV format with the model creation will make the whole process simpler:

csv = CSV.new(body, headers: true, header_converters: :symbol, converters: :all)
data = csv.to_a.map {|row| row.to_hash }

See CSV reference.

Now we have an easy way to access each field.

data.each do |d|  
  lesson = Lesson.create!(d[:join], ...)
  book = Book.create!(lesson: lesson, page_start:..)
end

BTW & FWIW,

class Sentense < ActiveRecord::Base

should be

class Sentence < ActiveRecord::Base

answered Apr 7, 2016 at 23:34

B Seven

46.3k73 gold badges257 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

Use RAKE Task to move CSV to POSTGRES relational DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related