Import data from excel to mysql in rails3 using roo gem

Roo gem allows us to access the contents of

  • Open-office spreadsheets (.ods)
  • Excel spreadsheets (.xls)
  • Google (online) spreadsheets
  • Excel’s new file format .xlsx

Following is a basic example to demonstrate how to get the datas from an excel sheet and save to your database. Suppose you have an excel sheet named student_details.xls which contain the student details such as “Name”,”Phone”,”Address”.We have to save them in our database but it will take a huge time if we insert them manually.Here we can user “roo” gem to fetch the datas from excel sheet and save to database in a fly.

Step#1

Add the gem in your gem file

gem ‘roo’

Then run the bundle

bundle install

Step#2

Place your excel sheet in the rails application such as in public folder.

Step#3

Now its time to change your controller like below.

Add the following line in your controller

require ‘roo’

Create a new method where you will fetch the data from excel sheet and save it in your database.

def  fetch_excel_data

ex = Excel.new(“#{Rails.root}/public/student_details.xls”)
ex.default_sheet = ex.sheets[1] #Mention the sheet number
3.upto(1000) do |line| #start and end of row
name = ex.cell(line,’A’)
phone = ex.cell(line,’B’)
adr = ex.cell(line,’C’)

@student = Student.create(:name => name,:phone => phone,:address => adr)

end
end

when you call this method the roo gem will fetch the datas accordingly and save to your database.

Advertisements

Generate Random number in ruby using SecureRandom

Ruby 1.9.x includes a lot of new things and one of the coolest things is the SecureRandom.

SecureRandom has useful methods for creating random numbers and strings. Some useful methods are described below

SecureRandom#base64

The result will contain A-Z, a-z, 0-9, “+”, “/” and “=”. If we will not provide the length of the string it will take 16 as default.

If the SecureRandom number generator will through “NotImplementedError” then you need to include “require ‘securerandom'”

p SecureRandom.base64 #pNPkWt2SaoHAqlMzFxSG4w==
p SecureRandom.base64(8) #Vi7OjnZYdW0=
p SecureRandom.base64(4) #JgPjpQ==

SecureRandom#hex

The result will be alphanumeric and contain 0-9 and a-f. If we will not provide the length of the string it will take 16 as default.
The length of the string will be the twice of n.

p SecureRandom.hex #8aecb22ea5388614f440b73ddf1ab458
p SecureRandom.hex(4) #87bef884

SecureRandom#random_number

The result will be the numeric only and contant 0-9. If we will not provide the argument then the result will be float

p SecureRandom.random_number #0.6153820461378544
p SecureRandom.random_number(100) #46

SecureRandom#uuid()

It will generate a v4 random UUID (Universally Unique IDentifier) is pure unique across a large namespace for universal unique number to generate a ID in a database.

p SecureRandom.uuid #215eb865-681f-4afa-b995-796b1e74299b

SecureRandom#urlsafe_base64

It will generate the random URL-safe token and contain A-Z, a-z, 0-9, “-” and “_”. “=” is also used if padding is true. If we will not provide the length of the string it will take 16 as default.

By default, padding is not generated because “=” may be used as a URL delimiter.

The generated string is not as unique as one generated by UUID, it will still be fairly random. Guaranteeing uniqueness across an application with a small loop.

The resulting string only includes characters that are URL safe

p SecureRandom.urlsafe_base64 #_TpR098Z46M7nuvQ_AkKUQ
p SecureRandom.urlsafe_base64(nil, true) #MHTSlDPU15fk66ZD6x6LLg==

Steps of S3 bucket implementation in Rails(3.x)

Amazon Simple Storage Service, e.g. S3, is a “highly durable and available store” and can be used to reliably store application content such as media files, static assets and user uploads. It allows you to offload your entire storage infrastructure and offers better scalability, reliability, and speed than just storing files on the file-system.

It is an online storage web service offered by Amazon Web Services and provides storage through web services interfaces (REST, SOAP etc.)

Step#1

Install  aws-s3 gem by adding in Gemfile

gem ‘aws-s3’

Run bundle

bundle install

Step#2

To get AWS S3 bucket ‘Access Key ID’ and ‘Secret Access Key’ go to the “http://aws.amazon.com/s3”

Create s3_credentials.yml file under config directory and enter your Amazon S3 credentials

development:
bucket: dev-bucket-name
access_key_id: xxxxx
secret_access_key: xxxxx

test:
bucket: test-bucket-name
access_key_id: xxxxx
secret_access_key: xxxxx

production:
bucket: prod-bucket-name
access_key_id: xxxxx
secret_access_key: xxxxx

Step#3

Open your model file that will hold the attachment and modify it as follows

has_attached_file :image,
:styles =>{ :thumb => “100×100”, :medium => “200×200”, :large => “600×400” },
:storage => :s3,
:s3_credentials => “#{RAILS_ROOT}/config/s3_credentials.yml”,
:path => “:attachment/:id/:style.:extension”,
:bucket => ‘yourbucket’

Step#4

In view, to display the image

<%= image_tag Profile.image.url(:thumb) %>