How to insert only a few columns from a csv into a table

Question

I have a large .csv file with the following header row:

:headers => 
     ["_id_", "name", "ascii_names", "alternate_name", "latitute",
     "longitude", "feature_class", "feature_code", "country_code", "cc2", 
     "admin_code_1", "admin_code_2", "admin_code_3", "admin_code_4", "population", 
     "elevation", "dem", "timezone", "modification_date"]}

I have a table only with the name, country_code, timezone columns in it. I would like to insert only those 3 columns of the .csv file into the table.

What I've tried:

CSV.foreach(csv_file, {:col_sep => "\t", :quote_char => '&', :write_headers => true, :headers => ["_id_", "name", "ascii_names", "alternate_name", "latitute", "longitude", "feature_class", "feature_code", "country_code", "cc2", "admin_code_1", "admin_code_2", "admin_code_3", "admin_code_4", "population", "elevation", "dem", "timezone", "modification_date"]}
    ) do |row|
    City.create row.to_hash.values_at(:name, :timezone, :country_code)
    binding.pry
  end

The problem is that when I check the table after running this, none of the values are populated. The rows themselves have been created, but they're all empty.

How do I map these correctly, so that the .create() knows which columns match to the table?

Answer 1

You need to first pick whether you're going to identify column headers as strings or symbols. In your headers => ["_id_" ...] you're declaring them as Strings, but in your row.to_hash.values_at(:name, ...), you're looking for Symbols. -Thank you to @mu

Secondly, you can manually specify values to insert into your table without hashing them, as you can when you create new entries any other time through Ruby.

City.create(:name => row[:name], :timezone => row[:timezone], :country_code => row[:country_code])

All in all, your method should look like this:

  CSV.foreach(csv_file, {:col_sep => "\t", :quote_char => '&', #:write_headers => true, 
    :headers => [:geonameid, :name, :ascii_names, :alternate_name, :latitute, :longitude, :feature_class, :feature_code, :country_code, :cc2, :admin_code_1, :admin_code_2, :admin_code_3, :admin_code_4, :population, :elevation, :dem, :timezone, :modification_date]}
    ) do |row|
    City.create(:name => row[:name], :timezone => row[:timezone], :country_code => row[:country_code])
  end

Answer 2

First of all, you're tell CSV that the headers are strings:

:headers => ["_id_", "name", ...]

That means that inside the CSV.foreach block, this:

row.to_hash

is a Hash with String keys. Then you call values_at on that Hash and ask for three Symbol keys and values_at is correctly giving you [nil] because a Hash with string keys won't have any values for Symbol keys. That means that you're CSV.foreach block is just an overly complicated way of saying:

City.create [nil]

When you pass an Array to create, you're essentially saying:

array.map { |e| City.create(e) }

That reduces your block to:

City.create nil

Somewhere inside ActiveRecord or ActiveModel it probably calling to_h on the passed attributes so calling create with nil is the same as calling it with an empty Hash.

The result of all that is a pile of empty records because, well, that's what you're asking ActiveRecord to do and you're not stopping it from throwing garbage data into your database.

The solution has two parts:

1. Use String keys everywhere or Symbol keys everywhere.
2. create wants a Hash argument in your case so use Hash#slice instead of values_at.

Something more like this:

CSV.foreach(csv_file, ...) do |row|
  City.create row.to_hash.slice(*%w[name timezone country_code])
end

---

PS: You really should include some NOT NULL constraints in your database and some validations in your models.