[Rails6 + Vue.js] Implement CSV import process using axios

I needed to import multiple CSV files into each table using Vue.js and Rails API, so make a note of it.

Please note

It is written by a beginner who is learning Rails, Vue.js as a memorandum. There is a possibility that the content may contain errors and there is a possibility that there is a better method, so please keep that in mind when referring to it. If you have any questions, please feel free to comment.

What you want to achieve

I want to prepare an input box and use axios to POST to the table where the selected CSV files (2 types) are prepared respectively.

csv1 ⇨ code_lists table csv2 ⇨ financials table The definition of the table is as follows.

code_lists table

  create_table "code_lists", force: :cascade do |t|
    t.string "edinet"
    t.string "securities"
    t.string "company"
    t.string "sector"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["company"], name: "index_code_lists_on_company"
    t.index ["edinet"], name: "index_code_lists_on_edinet", unique: true
    t.index ["securities"], name: "index_code_lists_on_securities"
  end

financials table

  create_table "financials", force: :cascade do |t|
    t.string "edinet"
    t.date "rec_date"
    t.string "account_name"
    t.float "value"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["account_name"], name: "index_financials_on_account_name"
    t.index ["edinet", "rec_date", "account_name"], name: "index_financials_on_edinet_and_rec_date_and_account_name", unique: true
    t.index ["edinet"], name: "index_financials_on_edinet"
  end

Preparation

Add the library in advance.

config/application.rb

require 'csv' #Postscript

Add Gem roo and bundle install

Gemfile

gem 'roo'

Since csrf token countermeasures are required at the time of ʻaxios-post, set a separate plugin. Create a new plugins` folder.

app/javascript/packs/plugins/vue-axios.js

const VueAxiosPlugin = {}
export default VueAxiosPlugin.install = function(Vue, { axios }) {
  const csrf_token = document.querySelector('meta[name="csrf-token"]').getAttribute('content')
  axios.defaults.headers.common = {
    "X-Requested-With": "XMLHttpRequest",
    "X-CSRF-Token": csrf_token
  }

  Vue.axios = axios
  Object.defineProperties(Vue.prototype, {
    axios: {
      get () {
        return axios
      }
    }
  })
}

Import the plugin into the entry file

hello_vue.js

import Vue from "vue/dist/vue.esm";
import axios from "axios"; //add to
import VueAxiosPlugin from "./plugins/vue-axios";  //add to
import App from "./components/App.vue";

Vue.use(VueAxiosPlugin, { axios: axios }) //add to

new Vue({
  el: "#app",
  render: h => h(App),
})

routing

The routing was set up as follows: Only the necessary parts are listed. Each has Rails API routing settings. Csv file for code_lists table to api / code_lists / import POST the csv file for the financials table to api / financials / import.

config/routes.rb

Rails.application.routes.draw do
  (Omitted above)
  namespace :api, format: 'json' do
    resources :code_lists do
      post :import, on: :collection
    end
  end

  namespace :api, format: 'json' do
    resources :financials do
      post :import, on: :collection
    end
  end
  (Omitted below)
end

View The view looks like this:

Import.vue

<template>
  <div>
    <div class="import-form">
      <input @change="selectedFile" type="file" name="file">
    </div>
    <div class="import-form">
      <button @click="upload('/api/code_lists/import')" type="submit">Code list upload</button>
    </div>
    <div class="import-form">
      <button @click="upload('/api/financials/import')" type="submit">Upload financial data</button>
    </div>
  </div>
</template>

<script>
import axios from 'axios'
export default {
  data: function(){
    return {
      uploadFile: null
    };
  },
  methods: {
    selectedFile: function(e) {
      //Save the information of the selected File
      e.preventDefault();
      let files = e.target.files;
      this.uploadFile = files[0];
    },
    upload: function(url) {
      //POST File using FormData
      let formData = new FormData();
      formData.append('file', this.uploadFile);
      // let config = {
      //     headers: {
      //         'content-type': 'multipart/form-data'
      //     }
      // };
      axios
          .post(url, formData)
          .then(function(response) {
              //response processing
          })
          .catch(function(error) {
              //error handling
          })
    }
  }
}
</script>

After selecting the file in the input box, save the file to uploadFile with the selectedFile method. Then, the upload method is executed in the click event of the button. POST the file to the url of the upload method argument (the two urls you set in routes earlier). The point is that the uploadFile saved in advance is passed to the FormData object when POSTing. I commented out because I didn't use config this time.

Controller and model

This time, it will be an implementation that imports two csv files into each table, and implementation is required for each model, but since the contents are almost the same, only the implementation of Financial Model is described. First about the controller.

app/controllers/api/financials_controller.rb

class Api::FinancialsController < ApplicationController
  def import
    Financial.import(params[:file])
  end
end

The controller only calls the Financial Class class methods.

Next, the description of the financial model is as follows.

app/models/financial.rb

class Financial < ApplicationRecord
  validates :edinet, presence: true
  validates :rec_date, presence: true
  validates :account_name, presence: true
  validates :value, presence: true
  def self.import(file)
    CSV.foreach(file.path, headers: true) do |row|
      #If the ID is found, call the record, if not found, create a new one
      financial = find_by(edinet: row["edinet"], rec_date: row["rec_date"], account_name: row["account_name"]) || new
      #Get data from CSV and set
      financial.attributes = row.to_hash.slice(*updatable_attributes)
      #save
      financial.save
    end
  end
  def self.updatable_attributes
    ["edinet", "rec_date", "account_name", "value"]
  end
end

Enter the column name of the CSV you want to import in updatable_attributes. Also, find_by is used to check if the record has already been imported, and if it has already been imported, it will be overwritten.

Finally

If you have any questions, please feel free to comment. We are developing an application that utilizes financial data of listed companies. We are developing using Vue.js for the front end and Rails API for the back end. I would like to continue to write articles about the findings I have gained. Thank you for staying with us so far!

Articles that I referred to: csrf measures: [Vue] axios can set CSRF token by default Front-end implementation: When you want to post a file with Vue.js Backend implementation: [Ruby on Rails] CSV import