Tuesday, February 16, 2010

Using Fastercsv to import data

Fastercsv is best for importing data in ruby on rails application.
For this we just need to add the gem.
gem install fastercsv

For importing data first we will need to upload the .csv file to the server/ rails application. Then we can call the data very easily. For uploading the file we need to take look at the step by step tutorial explained by tutorialpoint.com
For e.g. Consider the we need to upload the marks for a specific division and exam for students.

In view
<% form_tag ({:action => 'uploadFile'}, :multipart => true) do %>
  <%= file_field 'upload', 'datafile' %>
  <%= hidden_field_tag "division_id", params[:division_id] %>
  <%= hidden_field_tag "exam_id", params[:exam_id] %>
<%= submit_tag "Upload" %> <% end %>

Here I am sending the division_id and exam_id as parameters.

In Controller
def uploadFile
  post = Exam.upsave(params[:upload], params[:division_id], params[:exam_id])
  flash[:ok] = "data imported"
  redirect_to enter_marks_path

In Model
require "fastercsv"
def self.upsave(upload, divid, exam)
  @division = Division.find(divid)
  name =  upload['datafile'].original_filename
  directory = "public/data"
  path = File.join(directory, name)
  File.open(path, "wb") { |f| f.write(upload['datafile'].read) }
  # more code to be added

The above code is same as given in the tutorialpoint.com to upload the .csv file to the data folder in public file.
Now The problem is that different divisions can have different subjects so, in the method defined above we will need to add the following code.
We are going to declare one local variable.
num = 0
  m = []
  FasterCSV.foreach(path) do |col|
   if num == 0
   # finding the col values
    col.each do |c|
    # unless the column value is "R.No"(which is a roll no of student) find the subject using the abbreviation of that subject
      unless c == "R.No"
       subj = Subject.first(:conditions => ["abbreviation = '#{c}'"])
       if subj.present?
  # other code will come here
  num += 1

Subjects table has name and abbreviations columns, so while creating the .csv file user needs to use "R.No" as roll no of the student and subjects in the divisions using the abbreviations.
In the above code I am adding the subject's id in the "m" array declared before.
Now in the else we will import the data
if col.size > 1
  row_count = 1
  m.each do |subjectid|
    @division.subjects.each do |subject|
    if subject.id == subjectid
    # find the student with roll no
    student = Student.first(:conditions => ("roll_no = #{row[0]} AND division_id = #{@division.id}"))
    # find the mark for student
    mark = Mark.first(:conditions => ["student_id = #{student.id} AND subject_id = #{subjectid} AND division_id = #{divid} AND exam_id = #{exam}"])
      if mark.present?
        # we added row_count as a local variable
        mark.update_attribute(:written, row[row_count])
  row_count += 1

No comments:

Post a Comment