Ruby - Inserting entries from CSV into database

csv, ruby, ruby-on-rails

Solution

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.

Problem

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.

Original source