Rails : Import CSV data to database

Actually there are various ways to achieve this. Firstly you can use the PostGreSQL’s copy command. Secondly you can choose to use Ruby to load data.

Using copy command in PG

Create your table:

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

Copy data from your CSV file to the table:

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;

You can also specify the columns to read:

\copy zip_codes(ZIP,CITY,STATE) FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV

Using Ruby

# == Schema Information
# Table name: vehicles
#  id         :integer          not null, primary key
#  fuelType   :string
#  make       :string
#  model      :string
#  range      :string
#  rangeCity  :string
#  year       :string
#  VClass     :string
#  cylinders  :string
#  displ      :string
#  created_at :datetime         not null
#  updated_at :datetime         not null

class Vehicle < ApplicationRecord
  def self.import!
    CSV.foreach(Rails.root.join('db/vehicles.csv'), headers: true) do |row|
      Vehicle.create! row.to_hash.slice('fuelType', 'make', 'model', 'range', 'rangeCity', 'year', 'VClass', 'cylinders', 'displ')
      print '.'.green
# lib/tasks/load_vehicle_data.rake
desc 'Loads data from db/vehicles.csv file to vehicles table'
task :load_vehicle_data => :environment do
  MyLogger.log('Starting to load the vehicle data from db/vehicles.csv ...')

  MyLogger.log('Done loading the vehicle data from db/vehicles.csv!')

If you choose to beautify the Log then you can use this

# app/models/concerns/my_logger.rb
module MyLogger
  def log(message, message_color: :green)
    puts '~'.yellow.bold * 100
    puts "DateTime: #{DateTime.now}"
    puts '~'.yellow.bold * 100
    puts message.try(message_color)
    puts '~'.yellow.bold * 100

  module_function :log

For colorful CGI I have used this code in initializers

# config/initializers/string_modifier.rb
class String
  def black; "\e[30m#{self}\e[0m" end
  def red; "\e[31m#{self}\e[0m" end
  def green; "\e[32m#{self}\e[0m" end
  def blue; "\e[34m#{self}\e[0m" end

  def bold; "\e[1m#{self}\e[22m" end
  def italic; "\e[3m#{self}\e[23m" end
  def underline; "\e[4m#{self}\e[24m" end
  def blink; "\e[5m#{self}\e[25m" end
  def reverse_color; "\e[7m#{self}\e[27m" end
# note: if you use awesome_print gem, these methods will be auto-included

Screenshot from 2017-02-09 09-30-49.png



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s