Mapping csv columns to model?

Question

I'm creating a database of students and have the following code to read a csv file with headers and store each of the non-header lines as a student object.

class Student < ApplicationRecord
     def self.import(file)
          CSV.foreach(file.path, headers: true) do |row|
                Student.create! row.to_hash
          end
     end
end

My issue is that the csv files I need to import can be of different formats. Some might have headers organized like:

Undegraduate Major | First Name | Last Name | Class Year | Undergraduate Minor

First_name | Last_name | Major | Minor

First_name | Last_name | Class_year

As you can see, not all of the fields might be present, and they definitely might not be in the same order. How can I deal with this?

if the columns match the Student class attributes, why do you need all the columns and why do you need the columns in order? — Leantraxxx
– Leantraxxx, Commented Jun 28, 2017 at 19:50
The attribute of the student might have the name "major" but the column's name is "Undergraduate major", so it can't recognize the field "Undergraduate major" because it only knows the field "major" — Reef Rashid
– Reef Rashid, Commented Jun 28, 2017 at 19:52

Leantraxxx · Accepted Answer · 2017-06-28 19:58:24Z

2

You can do something like this to map the columns with attributes:

MAP = {
  "Undegraduate Major" => :major,
  "First Name" => :first_name,
  "Last Name" => :last_name,
  "Class Year" => :class_year,
  "Undergraduate Minor" => :minor,
  "Major" => :major,
  "Minor" => :minor,
  "Class_year" => :class_year
}

CSV.foreach(file.path, headers: true) do |row|
  data = {}

  row.to_hash.each do |k, v|
    key = MAP[k]
    data[key] = v
  end

  Student.create! data
end

answered Jun 28, 2017 at 19:58

Leantraxxx

4,6163 gold badges42 silver badges59 bronze badges

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

6 Comments

Reef Rashid Over a year ago

Hi! Thanks, I think this solution should work, but I'm running into the following error: "Unknown attribute " for Student". It seems like it's reading " as an attribute, but the csv file has not " as an attribute. Any idea what could be going on?

Leantraxxx Over a year ago

I think you are mapping a column to an attribute that does not exist in Student

Reef Rashid Over a year ago

Yeah, I just realized that, thanks! Can you explain to me how this would behave if there was an attribute that exists in some csv files but not others. Let's say some csv file include the attribute "minor" and some csv files have this column while others don't. For the files that do not have this column, will the "minor" attribute just be set to null?

Leantraxxx Over a year ago

Yes, the attributes you don't have in te csv will be set to nil (or its default value). The order is not important. You only need to have a MAP const with all the possibilities.

Roger Over a year ago

Isn't this very slow, if you need to do 100K records ??

|

Collectives™ on Stack Overflow

Mapping csv columns to model?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related