I needed to import multiple CSV files into each table using Vue.js and Rails API, so make a note of it.
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.
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
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),
})
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.
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.
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
Recommended Posts