read

CSV (comma separated values) files are frequently used to import/export data.

In rails 3, FasterCSV comes as default and below is the way to upload csv files inside rails applications. The code below will also show you how to generate csv in memory, parse on csv data, skip header, iterate over records, save records inside db, export upload error file and many more.

Upload form

= form_tag upload_url, :multipart => true do
%label{:for => "file"} File to Upload
= file_field_tag "file"
= submit_tag

Assume upload_url maps to import action of customers controller

Controller code

class CustomersController < ApplicationController
[...]
def import
if request.post? && params[:file].present?
infile = params[:file].read
n, errs = 0, []
CSV.parse(infile) do |row|
n += 1
# SKIP: header i.e. first row OR blank row
next if n == 1 or row.join.blank?
# build_from_csv method will map customer attributes &
# build new customer record
customer = Customer.build_from_csv(row)
# Save upon valid
# otherwise collect error records to export
if customer.valid?
customer.save
else
errs << row
end
end
# Export Error file for later upload upon correction
if errs.any?
errFile ="errors_#{Date.today.strftime('%d%b%y')}.csv"
errs.insert(0, Customer.csv_header)
errCSV = CSV.generate do |csv|
errs.each {|row| csv << row}
end
send_data errCSV,
:type => 'text/csv; charset=iso-8859-1; header=present',
:disposition => "attachment; filename=#{errFile}.csv"
else
flash[:notice] = I18n.t('customer.import.success')
redirect_to import_url #GET
end
end
end
[...]
end

Customer model

class Customer < ActiveRecord::Base
scope :active, where(:active => true)
scope :latest, order('created_at desc')
def self.csv_header
"First Name,Last Name,Email,Phone,Mobile, Address, FAX, City".split(',')
end
def self.build_from_csv(row)
# find existing customer from email or create new
cust = find_or_initialize_by_email(row[2])
cust.attributes ={:first_name => row[0],
:last_name => row[1],
:email => row[3],
:phone => row[4],
:mobile => row[5],
:address => row[6],
:fax => row[7],
:city => row[8]}
return cust
end
def to_csv
[first_name, last_name, email, phone, mobile, address, fax, city]
end
end

Export customer records in CSV format

Below code loads customer records from database then generate csv_data inside memory and exports data to browser using send_data method.

Note: As we are not writing on file system hence code can easily work heroku.

<div class='code-highlight'><pre class='code-highlight-pre'><div data-line='1' class='code-highlight-row numbered'><div class='code-highlight-line'>def export </div></div><div data-line='2' class='code-highlight-row numbered'><div class='code-highlight-line'> # CRITERIA : to select customer records </div></div><div data-line='3' class='code-highlight-row numbered'><div class='code-highlight-line'> #=> Customer.active.latest.limit(100) </div></div><div data-line='4' class='code-highlight-row numbered'><div class='code-highlight-line'> custs = Customer.limit(10) </div></div><div data-line='5' class='code-highlight-row numbered'><div class='code-highlight-line'> filename ="customers_#{Date.today.strftime('%d%b%y')}" </div></div><div data-line='6' class='code-highlight-row numbered'><div class='code-highlight-line'> csv_data = FasterCSV.generate do |csv| </div></div><div data-line='7' class='code-highlight-row numbered'><div class='code-highlight-line'> csv << Customer.csv_header </div></div><div data-line='8' class='code-highlight-row numbered'><div class='code-highlight-line'> custs.each do |c| </div></div><div data-line='9' class='code-highlight-row numbered'><div class='code-highlight-line'> csv << c.to_csv </div></div><div data-line='10' class='code-highlight-row numbered'><div class='code-highlight-line'> end </div></div><div data-line='11' class='code-highlight-row numbered'><div class='code-highlight-line'> end </div></div><div data-line='12' class='code-highlight-row numbered'><div class='code-highlight-line'> send_data csv_data, </div></div><div data-line='13' class='code-highlight-row numbered'><div class='code-highlight-line'> :type => 'text/csv; charset=iso-8859-1; header=present', </div></div><div data-line='14' class='code-highlight-row numbered'><div class='code-highlight-line'> :disposition => "attachment; filename=#{filename}.csv" </div></div><div data-line='15' class='code-highlight-row numbered'><div class='code-highlight-line'>end</div></div></pre></div>

Blog Logo

Sandip Ransing


Published

Image

Fun On Rails

Journal of a Web Developer #ruby #rails #JS

Back to Overview