Import CSV Data in a Rails App with ActiveAdmin

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?

Answers


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


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

@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. ....

For future reference, I built out a gem that lets you easily add csv import to an active admin resource

See this link


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.


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

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


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

Need Your Help

Using Entity Framework entities as business objects?

.net entity-framework architecture

I'm using Entity Framework O/R mapper from Microsoft and using entity classes (generated classes that are mapped to DB objects) as a business objects.

Cannot find module 'ReactNative' from 'react-native.js' w/ Jest

javascript reactjs react-native jestjs

I'm attempting to use jest (v20.0.0) w/ my React Native application (v0.42.0) however when I run yarn jest I get the following error: