Importing csv file on Ruby on Rails

Question

Sorry for my easy question because I'm so newbie in everything.

As my question says, I wanted to import my csv file with header line into the existing data table. Even I tried for 4 days with these answers(and other googlings) Trouble importing csv file with ruby CSV Module and Ruby on Rails - Import Data from a CSV file , but I couldn't find the way.

# db/migrate/20140923064239_create_restaurants.rb
class CreateRestaurants < ActiveRecord::Migration
  def change
    create_table :restaurants do |t|
      t.string :restaurant_category
      t.string :restaurant_subcategory
      t.timestamps
    end
  end
end

# lib/tasks/import.rake
require 'csv'

CSV.foreach("english.csv", :headers => true) do |row|
  Restaurant.create!(row.to_hash)
end

# english.csv
restaurant_category,restaurant_subcategory
kor,bakban
kor,galbi
kor,gobchang
kor,hanjeong
kor,hajang
kor,dolsot
kor,bude
kor,jokbal
kor,sullung
chin,ssambab
chin,dubu
chin,jechum
chin,pajeon

Then I did rake db:migrate, and got error message NameError: uninitialized constant Restaurant. I have tried many other ways in google but everytime this error message popped up. I just now assumes my existing Restaurant table is not working for this rake process(even though I don't know what this error message really means).

So please help me for this problem and I'm very sorry for might-be-easy question. But too hard for me and I had no way to solve it. Thanks.

Answer 1

Create a rake task for the same. Edit your import.rake file to

require 'csv'
namespace :db do
  task :import_csv => :environment do
    CSV.foreach("english.csv", :headers => true) do |row|
      Restaurant.create!(row.to_hash)
    end
  end
end

And run bundle exec rake db:import_csv

Answer 2

In case you'd like to have the restaurant data be imported across two or more tables -- say you have a Category and Subcategory table or whatever, and then a Restaurant table -- then there's a useful gem DutyFree that makes imports and exports fairly painless. For instance, what if this were your schema:

___________     _____________     _____________
|Category |     |Subcategory|     |Restaurant |
|         |     |           |     |           |
|         |---->|belongs_to |---->|belongs_to |
|         |     |   Category|     |Subcategory|
|         |     |           |     |           |
¯¯¯¯¯¯¯¯¯¯¯     ¯¯¯¯¯¯¯¯¯¯¯¯¯     ¯¯¯¯¯¯¯¯¯¯¯¯¯

This gem can intelligently analyse the has_many and belongs_to associations on models and based on these relationships identify how to properly save each imported row across multiple destination tables. Either a create or an update is performed based on if the data already exists or not.

Here is some sample CSV data for the Korean food categories you had above that is designed to target a schema such as above. Restaurants are from around the world that serve this food:

Category,Subcategory,Name,Address
Korean,Bakban,Gajeongsik Bakban,"1555-6, Seocho-dong, Seocho-gu, Seoul (서울특별시 서초구 서초대로46길 19-7)"
Korean,Bakban,Sakunja,"17-1 jalan 26a-70a prismaville desa sri hartamas 50480 Kuala Lumpur, Malaysia"
Korean,Galbi,Kalbi Korean BBQ & Sushi,"36 Rosebery Avenue, London EC1R 5HP England"
Korean,Gopchang,Gombawie,"151-4, Samseong-dong, Gangnam-gu, Seoul South Korea"
Korean,Gopchang,Samsung Wonjo Yang Gobchang (삼성원조양곱창),"133-6, Cheongdam-dong, Gangnam-gu, Seoul (서울특별시 강남구 청담동 133-6)"
Korean,Gopchang,Hamji Gopchang (함지곱창),"144-5, Nonhyeon-dong, Gangnam-gu, Seoul (서울특별시 강남구 학동로2길 33)"
Korean,Hanjeongsik,Kkott Dolge Jang 1 Beonga,"210-2, Bongsan-dong, Yeosu-si, Jeollanam-do"
Korean,Hanjeongsik,Chaegeundam,"983, Daechi-dong, Gangnam-gu, Seoul"
Korean,Haejang,Sun Ha Jang,"4032 W Olympic Blvd, Los Angeles, CA 90019"
Korean,Dolsot,Sarangchae Korean Restaurant,"278-280 Huntingdon Street, Nottingham NG1 3NA England"
Korean,Budae-jjigae,Gimnejip,"322-38, Sinjang-dong, Pyeongtaek-si, Gyeonggi-do"
Korean,Budae-jjigae,Daewoo Budaejjigae,"641-18, Yeoksam-dong, Gangnam-gu, Seoul"
Korean,Jokbal,Jang Choong Dong Wong Jokbal,"425 S Western Ave. Los Angeles, CA 90020"
Korean,Seolleongtang,Uchon Dolsot Seolleongtang,"24, Eulji-ro 12-gil, Jung-gu, Seoul 04550"
Chinese,Ssambab,GAAE Ssambab,"No. 6, Lane 40, Section 2, Zhongcheng Road, Shilin District, Taipei City (台北市士林區忠誠路二段40巷6號)"
Chinese,Dubu,Tofu Tofu,"96 Bowery, New York, NY 10013"
Chinese,Cífàntuán,Shanghai Hong Kong Noodle Shop,"29 Jardine's Bazaar, Causeway Bay, Hong Kong"
Chinese,Pajeon,Madang,"Gneisenaustr. 8, 10961 Berlin Germany"
Chinese,Pajeon,FOMO Pancake,"No.333 Huaihai Middle Road Xintiandi Plaza B2-17, Shanghai 200000 China"

To demonstrate your example from above, I took this CSV and made it into an RSpec test in the gem. This is an example of loading starting with Restaurant and then tacking on Subcategory and Category: https://github.com/lorint/duty_free/blob/master/spec/models/restaurant_spec.rb

And here is an example of going the other direction, starting with a Category and loading Subcategory and Restaurant: https://github.com/lorint/duty_free/blob/master/spec/models/restaurant_reverse_spec.rb

One nice thing about this gem is that after configuring the column definitions to do an import, you get export for free because everything works from the same template. For the example going from Category through has_many relationships to Subcategory and Restaurant, this template allows the column names in the CSV to line up perfectly with the database columns:

IMPORT_TEMPLATE = {
  uniques: [:name, :subcategories_name, :subcategories_restaurants_name, :subcategories_restaurants_address],
  required: [],
  all: [:name,
    { subcategories: [:name,
      { restaurants: [:name, :address] }] }],
  # An alias for incoming columns
  as: {
        'Category' => 'Name',
        'Subcategory' => 'Subcategories Name',
        'Name' => 'Subcategories Restaurants Name',
        'Address' => 'Subcategories Restaurants Address'
      }
}

With this in a Category model you can then call Category.df_import() and Category.df_export().

P.S. Great food choices -- I do love tasty Korean food!