importdata

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
    end
  end
end
# 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 ...')
  
  Vehicle.import!

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

 

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
  end

  module_function :log
end

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
end
# note: if you use awesome_print gem, these methods will be auto-included

 

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

 

Sources

http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table

 

Note: Open (PG)PostGreSQL in command line and do some stuffs

To access postgres console:

$ sudo -u postgres -i

postgres@host:~$ psql

or

$ sudo -u postgres psql

[sudo] password for john: 
psql (9.3.12)
Type "help" for help.

postgres=# 

 

# For help
postgres=# help
 You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
 \h for help with SQL commands
 \? for help with psql commands
 \g or terminate with semicolon to execute query
 \q to quit
# For help
postgres=# \h
 Available help:
 ABORT CLUSTER DEALLOCATE END
 ALTER AGGREGATE COMMENT DECLARE EXECUTE
 ALTER COLLATION COMMIT DELETE EXPLAIN

Create database

# database command has to end with semicolon
postgres=# create database july_prod_dump1;
CREATE DATABASE

Alter permission

ALTER USER new_user CREATEDB

 

Ubuntu: Open localhost database in pgadmin

Make sure you have installed PGAdmin

sudo apt-get install pgadmin3

now set your database

$ sudo -u postgres psql

it will ask you password, get inside and alter the user’s password if you have not already set it

postgres=# alter user john with password 'mypassword';

and then open pg admin, fill the form and get inside

pgadmin-09 13:04:25

some helpful links

http://www.indjango.com/ubuntu-install-postgresql-and-pgadmin/