RoR - Import CSV file to table - dynamic
Satish Chauhan has a Ruby on Rails site that has a very helpful article on how to import a CSV file into a table. I took his code and have extended it. I offer it now to you.
My extension is to make the importation of the code dynamic, i.e. this code may be used, as is, to import any CSV file into any table without changing the code. The first row of the CSV file consists of column headers. In keeping with the convention over configuration principle of Rails, these column headers must be identical to the field names of the table that the data is to be imported into. (The exception to this rule is that the column headers may be mixed case.)
file: app/views/infolake/index.rhtml
<html>
<head>
<title> Import InfoLake Spreadsheet </title>
</head>
<body>
<h1>Import InfoLake Spreadsheet</h1>
<% form_for :load, :url=>{:controller=>"infolake", :action=>"csv_import"},
:html => { :multipart => true } do |f| -%>
<table>
<tr>
<td>
<label for="load_file"> Select a CSV File :</label>
</td>
<td >
<%= f.file_field :file -%>
</td>
</tr>
<tr>
<td colspan='2'>
<%= submit_tag 'Submit' -%>
</td>
</tr>
</table>
<% end -%>
</body>
</html>file: app/views/infolake/csv_import.rhtml
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta name="generator" content="HTML Tidy, see www.w3.org">
<title>Spreadsheet Import Confirmation</title>
</head>
<body>
<h1>Spreadsheet Import Confirmation</h1>
<p style="color: green"><%= flash[:message] %></p>
</body>
</html>
file: app/views/infolake/csv_import.rhtml
class InfolakeController < ApplicationController
require 'csv'
def index
@load
end
def csv_import
@file = CSV.open(params[:load][:file].path, 'r')
@cols = Hash.new
@nbr_rows = 0
@file.each { |row|
# This code will have another iteration, so we'll use "case" instead of "if"
case @nbr_rows
when 0: # Store the column headers and their positions in the CSV file in a hash
row.each_with_index { |col,i|
unless col.nil?
col.downcase!
@cols[col] = i
end
}
@nbr_rows += 1
else
import(row)
end
}
end # def csv_import
def import(row)
@infolake = Infolake.new
@cols.each { |col_name,col_ix|
unless row[col_ix].nil? || row[col_ix].empty?
eval("@infolake.#{col_name} = row[#{col_ix}]")
end
}
if @infolake.save!
@nbr_rows += 1
GC.start if @nbr_rows % 50 == 0
else
identifier = "#{row[@cols['identifier']]}"
puts "CSV record with identifier = #{identifier} was not saved."
end
msg = "No new records were added to the data base"
flash.now[:message]= msg if @nbr_rows == 0
msg = "#{@nbr_rows} new records were added to the data base"
flash.now[:message]= msg if @nbr_rows > 0
end # def import_row
end
You can leave a response, or trackback from your own site.