Ruby - Inserting entries from CSV into database

Question

I have an uploaded CSV file, which I parse like so:

CSV.foreach(@my_file.file.path) do |row|
    puts row[1]
end

The incoming CSV file has at least the following columns: "id", "name", "number", "phone", and "food".

I would like to do something like:

CSV.foreach(@my_file.file.path) do |row|
     //find the columns in "row" associated with "id", "name", "number"
     //even though I don't know definitively which column they will be in
     //for example, "name" may be the 2nd or 3rd or 4th column (etc)

     //insert into my_table values(id, name, number)

end

Note that the CSV file will always have column names as the first row, however from file to file, the ordering of those columns may differ.

Answer 1

Here's a snippet of code that will collect only the fields you care about into an array of hashes:

require 'csv'

fields_to_insert = %w{ id name food number phone }
rows_to_insert = []

CSV.foreach("stuff.csv", headers: true) do |row|
  row_to_insert = row.to_hash.select { |k, v| fields_to_insert.include?(k) }
  rows_to_insert << row_to_insert
end

Given the following contents of stuff.csv:

junk1,name,junk2,food,id,junk4,number,phone
foo,Jim,bar,pizza,123,baz,9,555-1212
baz,Fred,bar,sushi,55,foo,44,555-1213

rows_to_insert will contain:

[{"name"=>"Jim",
  "food"=>"pizza",
  "id"=>"123",
  "number"=>"9",
  "phone"=>"555-1212"},
 {"name"=>"Fred",
  "food"=>"sushi",
  "id"=>"55",
  "number"=>"44",
  "phone"=>"555-1213"}]

I'd take that and use activerecord-import to insert them all at once:

SomeModel.import(rows_to_insert)

You could insert the records one at a time in the CSV loop, but that's inefficient, and because id is normally a protected attribute, you can't mass-assign it, so you'd have to do this to insert a single record:

some_model = SomeModel.new(row_to_insert.select { |k, v| k != "id" }
some_model.id = row_to_insert["id"]
some_model.save!

...or something similar.

Answer 2

If the first row are the header names you can use the :headers => true option to parse in order to use the first row as keys for the data.

text = File.read(@my_file.file.path)
csv = CSV.parse(text, :headers => true)
csv.each do |row|
  row = row.to_hash.with_indifferent_access
  YourModel.create!(row.to_hash.symbolize_keys)
end