Import CSV Data in a Rails App with ActiveAdmin

Question

i want to upload CSV files through the activeadmin panel.

on the index page from the resource "product" i want a button next to the "new product" button with "import csv file".

i dont know where to start. in the documentation is something about collection_action, but with the code below i have no link at the top.

ActiveAdmin.register Post do
    collection_action :import_csv, :method => :post do
      # Do some CSV importing work here...
      redirect_to :action => :index, :notice => "CSV imported successfully!"
    end
  end

anyone here who use activeadmin and can import csv data?

Debutante

Answer 1

Continuing from Thomas Watsons great start to the answer which helped me get my bearings before figuring the rest of it out.

The code blow allows not just CSV upload for the example Posts model but for any subsequent models thereafter. all you need to do is copy the action_item ands both collection_actions from the example into any other ActiveAdmin.register block and the functionality will be the same. hope this helps.

app/admin/posts.rb

ActiveAdmin.register Post do
  action_item :only => :index do
    link_to 'Upload CSV', :action => 'upload_csv'
  end

  collection_action :upload_csv do
    render "admin/csv/upload_csv"
  end

  collection_action :import_csv, :method => :post do
    CsvDb.convert_save("post", params[:dump][:file])
    redirect_to :action => :index, :notice => "CSV imported successfully!"
  end

end

app/models/csv_db.rb

require 'csv'
class CsvDb
  class << self
    def convert_save(model_name, csv_data)
      csv_file = csv_data.read
      CSV.parse(csv_file) do |row|
        target_model = model_name.classify.constantize
        new_object = target_model.new
        column_iterator = -1
        target_model.column_names.each do |key|
          column_iterator += 1
          unless key == "ID"
            value = row[column_iterator]
            new_object.send "#{key}=", value
          end
        end
        new_object.save
      end
    end
  end
end

note: this example does a check to see whether or not the first column is an ID column, it then skips that column as rails will assign an ID to the new object (see example CSV below for reference)

app/views/admin/csv/upload_csv.html.haml

= form_for :dump, :url=>{:action=>"import_csv"}, :html => { :multipart => true } do |f|
  %table
    %tr
      %td
        %label{:for => "dump_file"}
          Select a CSV File :
      %td
        = f.file_field :file
    %tr
      %td
        = submit_tag 'Submit'

app/public/example.csv

"1","TITLE EXAMPLE","MESSAGE EXAMPLE","POSTED AT DATETIME"
"2","TITLE EXAMPLE","MESSAGE EXAMPLE","POSTED AT DATETIME"
"3","TITLE EXAMPLE","MESSAGE EXAMPLE","POSTED AT DATETIME"
"4","TITLE EXAMPLE","MESSAGE EXAMPLE","POSTED AT DATETIME"
"5","TITLE EXAMPLE","MESSAGE EXAMPLE","POSTED AT DATETIME"

note: quotations not always needed

Stonefly

for the csv_db block do you replace model_name, target_model and new_object with relative names like Post or Lead? – Deshawndesi

you dont need to simply when you call CsvDb.convert_save("post", params[:dump][:file]) just replace the post with resource you'd like – Stonefly

How do I overwrite any existing entries? I get a SQLite3::ConstraintException: PRIMARY KEY must be unique error if I try to import the CSV over existing records. – Acroterion

its the Id that is the problem. you could throw in a conditional statement along the lines of if id == something.id something.update_attributes :blah => "blah" end – Stonefly

Answer 2

Adding a collection_action does not automatically add a button linking to that action. To add a button at the top of the index screen you need to add the following code to your ActiveAdmin.register block:

action_item :only => :index do
  link_to 'Upload CSV', :action => 'upload_csv'
end

But before calling the collection action you posted in your question, you first need the user to specify which file to upload. I would personally do this on another screen (i.e. creating two collection actions - one being a :get action, the other being your :post action). So the complete AA controller would look something like this:

ActiveAdmin.register Post do
  action_item :only => :index do
    link_to 'Upload posts', :action => 'upload_csv'
  end

  collection_action :upload_csv do
    # The method defaults to :get
    # By default Active Admin will look for a view file with the same
    # name as the action, so you need to create your view at
    # app/views/admin/posts/upload_csv.html.haml (or .erb if that's your weapon)
  end

  collection_action :import_csv, :method => :post do
    # Do some CSV importing work here...
    redirect_to :action => :index, :notice => "CSV imported successfully!"
  end
end

Unkenned

Answer 3

@krhorst, I was trying to use your code, but unfortunately it sucks on big imports. It eat so much memory =( So I decided to use own solution based on activerecord-import gem

Here it is https://github.com/Fivell/active_admin_import

Features

1. Encoding handling
2. Support importing with ZIP file
3. Two step importing (see example2)
4. CSV options
5. Ability to prepend CSV headers automatically
6. Bulk import (activerecord-import)
7. Ability to customize template
8. Callbacks support
9. Support import from zip file
10. ....

Cadmus

Answer 4

Based on ben.m's excellent answer above I replaced the csv_db.rb section suggested with this:

require 'csv'
class CsvDb
  class << self
    def convert_save(model_name, csv_data)
      begin
        target_model = model_name.classify.constantize
        CSV.foreach(csv_data.path, :headers => true) do |row|
          target_model.create(row.to_hash)
        end
      rescue Exception => e
        Rails.logger.error e.message
        Rails.logger.error e.backtrace.join("\n")
      end
    end
  end
end

While not a complete answer I did not want my changes to pollute ben.m's answer in case I did something egregiously wrong.

Larson

Answer 5

For large excel which takes time on normal process, I created a gem that process Excel sheets using an active job and display results using action cable(websockets)

https://github.com/shivgarg5676/active_admin_excel_upload

Handfast

Answer 6

expanding on ben.m's response which I found very useful.

I had issues with the CSV import logic (attributes not lining up and column iterator not functioning as required) and implemented a change which instead utilizes a per line loop and the model.create method. This allows you to import a .csv with the header line matching the attributes.

app/models/csv_db.rb

require 'csv'
class CsvDb
  class << self
    def convert_save(model_name, csv_data)
      csv_file = csv_data.read
      lines = CSV.parse(csv_file)
      header = lines.shift
      lines.each do |line|
        attributes = Hash[header.zip line]
        target_model = model_name.classify.constantize
        target_model.create(attributes)
      end
    end
  end
end

So your imported CSV file can look like this (use to match up with model attributes):

importExample.csv

first_name,last_name,attribute1,attribute2
john,citizen,value1,value2

Pericardium

Answer 7

Some of the solutions above worked pretty well. I ran into challenges in practice that I solved here below. The solved problems are:

1. Importing CSV data with columns in different orders
2. Preventing errors caused by hidden characters in Excel CSVs
3. Resetting the database primary_key so that the application can continue to add records after the import

Note: I took out the ID filter so I could change IDs for what I'm working on, but most use cases probably want to keep it in.

require 'csv'
class CsvDb
  class << self
    def convert_save(model_name, csv_data)
      csv_file = csv_data.read
      csv_file.to_s.force_encoding("UTF-8")
      csv_file.sub!("\xEF\xBB\xBF", '')
      target_model = model_name.classify.constantize
      headers = csv_file.split("\n")[0].split(",")
      CSV.parse(csv_file, headers: true) do |row|
        new_object = target_model.new
        column_iterator = -1
        headers.each do |key|
          column_iterator += 1
          value = row[column_iterator]
          new_object.send "#{key.chomp}=", value
        end
        new_object.save
      end
      ActiveRecord::Base.connection.reset_pk_sequence!(model_name.pluralize)
    end
  end
end

Cotton