Rails CSV import export
This is my memo about how to implement basic CSV import/export in a Rails app.
Example
Routes
# GET /cities List with import and export buttons
# GET /cities.csv Export csv
# POST /cities/import Import csv
resources :cities, only: :index do
collection do
post :import
end
end
Controller
class CitiesController < ApplicationController
before_action :ensure_csv_file, only: :import
# GET /cities
# GET /cities.csv
def index
@cities = City.all.order(:preamble)
respond_to do |format|
format.html
format.csv { send_data @cities.to_csv, filename: "cities-#{Date.current}.csv" }
end
end
# POST /cities/import
def import
count = City.import_csv(params[:file])
flash[:success] = "#{count} records were successfully uploaded from #{params[:file].original_filename}"
redirect_to cities_url
rescue StandardError => exception
flash[:error] = exception.to_s
redirect_to(cities_url)
end
private
def ensure_csv_file
if params[:file].nil?
flash[:error] = "No File Uploaded"
redirect_to(cities_url)
elsif params[:file].content_type != "text/csv"
flash[:error] = "File type must be csv: #{params[:file].original_filename}"
redirect_to(cities_url)
end
end
end
Model
require "csv"
class City < ApplicationRecord
validates :preamble, presence: true, uniqueness: true
class << self
def to_csv
csv_attribute_names = %w[preamble description]
CSV.generate(headers: true) do |csv|
csv << csv_attribute_names
find_each { |city| csv << city.attributes.values_at(*csv_attribute_names) }
end
end
# Takes an uploaded file, upsert records from the file content and returns imported item count.
def import_csv(file)
options = {
headers: true,
encoding: "iso-8859-1:utf-8",
header_converters: ->(f) { f.strip.downcase.parameterize(separator: "_") },
converters: ->(f) { f ? f.strip : nil }
}
transaction do
delete_all
CSV.foreach(file.path, options) do |row|
row_hash = row.to_hash.symbolize_keys
ensure_valid_csv_row(row_hash)
create!(row_hash.slice(:preamble, :description))
end
end
count
end
private
def ensure_valid_csv_row(row_hash)
required_keys = %i[preamble description]
raise "Invalid csv row" unless required_keys.to_set.intersect?(row_hash.keys.to_set)
end
end
end
View
= content_for :page_header do
%h1 Cities
#cities_index
= form_tag import_cities_path, multipart: true do
.row
.col-xs-4
= link_to 'Export CSV', cities_path(format: :csv), class: "btn btn-primary btn-sm"
.col-xs-8
= render 'shared/csv_file_import', submit_options: { "data-test-id" => "cities-csv-import" }
%table.table
%thead
%tr
%th.col-lg-3 Preamble
%th.col-lg-9 Description
%tbody
- @cities.each do |city|
%tr
%td{"data-test-id" => "cities-index-table-city-#{city.id}-preamble"}= city.preamble
%td{"data-test-id" => "cities-index-table-city-#{city.id}-description"}= city.description
RSpec
require "rails_helper"
feature "user uploads cities csv", :js do
scenario "user uploads valid csv file and sees uploaded rows content" do
user = create(:user)
login_as(user, scope: :user)
visit cities_url
page.attach_file("file", Rails.root.join("spec", "fixtures", "files", "valid_cities.csv"))
find("[data-test-id='cities-csv-import']").click
expect_url_with_flash(cities_url, "3 records were successfully uploaded from valid_cities.csv")
City.all.each { |city| expect_correct_city_row(city) }
end
scenario "user tries to upload non-csv file and sees correct table content" do
user = create(:user)
login_as(user, scope: :user)
visit cities_url
page.attach_file("file", Rails.root.join("spec", "fixtures", "files", "invalid_cities.csv"))
find("[data-test-id='cities-csv-import']").click
expect_url_with_flash(cities_url, "Invalid csv row")
expect(page).not_to have_selector("[data-test-id*='cities-index-table-citiy-']")
end
private
def expect_correct_city_row(city)
expect(page).to have_selector("[data-test-id='cities-index-table-city-#{city.id}-preamble']",
text: /\A#{Regexp.escape(city.preamble)}\z/)
expect(page).to have_selector("[data-test-id='cities-index-table-city-#{city.id}-description']",
text: /\A#{Regexp.escape(city.description)}\z/)
end
end