CSV seed not working for Rails database

Question

I am trying to seed a Rails database with the content of a CSV file. I am new to RoR so I can't find my mistake. The script works well but it does not take the content of my CSV file (everything is filled by nil).

The migration:

#db/migrate/x_create_transactions.rb
class CreateTransactions < ActiveRecord::Migration[5.0]
  def change
    create_table :transactions
    add_column :transactions, :siren, :integer
    add_column :transactions, :nom_ent, :string
    add_column :transactions, :adresse, :string
    add_column :transactions, :complement_adresse, :string
    add_column :transactions, :cp_ville, :string
    add_column :transactions, :pays, :string
    add_column :transactions, :region, :string
    add_column :transactions, :departement, :integer
    add_column :transactions, :activite, :string
    add_column :transactions, :date, :integer
    add_column :transactions, :nb_salaries, :string
    add_column :transactions, :nom, :string
    add_column :transactions, :prenom, :string
    add_column :transactions, :civilite, :string
    add_column :transactions, :adr_mail, :string
    add_column :transactions, :libele_acti, :string
    add_column :transactions, :categorie, :string
    add_column :transactions, :tel, :integer
  end
end

My rake:

#db/migrate/seeds.rb
require 'smarter_csv'
  options = {}
  SmarterCSV.process('lib/seeds/siren_db.csv', options) do |chunk|
    chunk.each do |row|
      Transaction.create!( {
        :siren => row[0],
        :nom_ent => row[1],
        :adresse => row[2],
        :complement_adresse => row[3],
        :cp_ville => row[4],
        :pays => row[5],
        :region => row[6],
        :departement => row[7],
        :activite => row[8],
        :date => row[9],
        :nb_salaries => row[10],
        :nom => row[11],
        :prenom => row[12],
        :civilite => row[13],
        :adr_mail => row[14],
        :libele_acti => row[15],
        :categorie => row[16],
        :tel => row[17]
      })
    end
  end

My model:

#models/transcations.rb
class Transaction < ApplicationRecord
end

The beginning of the csv file:

siren;nom_ent;adresse;complement_adresse;cp_ville;pays;region;departement;activite;date;nb_salaries;nom;prenom;civilite;adr_mail;libele_acti;categorie;tel
38713707;SYND COPR DU 6 AU 8 RUE DE CHARONNE 75;6 RUE DE CHARONNE;;75011 PARIS;FRANCE;Île-de-France;75;Activités combinées de soutien lié aux bâtiments;2008;1 ou 2 salariés;;;;;Syndicat de copropriété ;PME;
38713707;SYND COPR DU 6 AU 8 RUE DE CHARONNE 75;6 RUE DE CHARONNE;;75011 PARIS;FRANCE;Île-de-France;75;Activités combinées de soutien lié aux bâtiments;2008;1 ou 2 salariés;;;;;Syndicat de copropriété ;PME;
38724340;SYND COPR DU 18 BD ARAGO 75013 PARIS;18 BOULEVARD ARAGO;;75013 PARIS;FRANCE;Île-de-France;75;Activités combinées de soutien lié aux bâtiments;2008;1 ou 2 salariés;;;;;Syndicat de copropriété ;PME;

Thank you for your help.

Answer 1

With SmarterCSV

options = {col_sep: ';'}
SmarterCSV.process('lib/seeds/siren_db.csv', options).each do |chunk|
  chunk.each do |row|
    p row
  end
end

It outputs :

{:siren=>38713707, :adresse=>"6 RUE DE CHARONNE", :cp_ville=>"75011 PARIS", :pays=>"FRANCE", :region=>"le-de-France", :departement=>75, :activite=>"Activits combines de soutien li aux btiments", :date=>2008, :nb_salaries=>"1 ou 2 salaris", :libele_acti=>"Syndicat de coproprit", :categorie=>"PME"}
{:siren=>38713707, :adresse=>"6 RUE DE CHARONNE", :cp_ville=>"75011 PARIS", :pays=>"FRANCE", :region=>"le-de-France", :departement=>75, :activite=>"Activits combines de soutien li aux btiments", :date=>2008, :nb_salaries=>"1 ou 2 salaris", :libele_acti=>"Syndicat de coproprit", :categorie=>"PME"}
{:siren=>38724340, :adresse=>"18 BOULEVARD ARAGO", :cp_ville=>"75013 PARIS", :pays=>"FRANCE", :region=>"le-de-France", :departement=>75, :activite=>"Activits combines de soutien li aux btiments", :date=>2008, :nb_salaries=>"1 ou 2 salaris", :libele_acti=>"Syndicat de coproprit", :categorie=>"PME"}

row is a Hash, you'll have to rename the CSV header ("NOM"->"NOM_ENT") to fit your table columns, or get the correct key for each column.

If the names fit exactly, you'll be able to use :

Transaction.create!(row)

With Ruby CSV

I couldn't get SmarterCSV to parse the accented characters. The standard Ruby CSV works just fine :

require 'csv'

CSV.foreach('siren.csv', col_sep: ';', headers: true) do |row|
  p row.to_h
end

It outputs :

{"siren"=>"38713707", "nom_ent"=>"SYND COPR DU 6 AU 8 RUE DE CHARONNE 75", "adresse"=>"6 RUE DE CHARONNE", "complement_adresse"=>nil, "cp_ville"=>"75011 PARIS", "pays"=>"FRANCE", "region"=>"Île-de-France", "departement"=>"75", "activite"=>"Activités combinées de soutien lié aux bâtiments", "date"=>"2008", "nb_salaries"=>"1 ou 2 salariés", "nom"=>nil, "prenom"=>nil, "civilite"=>nil, "adr_mail"=>nil, "libele_acti"=>"Syndicat de copropriété ", "categorie"=>"PME", "tel"=>nil}
{"siren"=>"38713707", "nom_ent"=>"SYND COPR DU 6 AU 8 RUE DE CHARONNE 75", "adresse"=>"6 RUE DE CHARONNE", "complement_adresse"=>nil, "cp_ville"=>"75011 PARIS", "pays"=>"FRANCE", "region"=>"Île-de-France", "departement"=>"75", "activite"=>"Activités combinées de soutien lié aux bâtiments", "date"=>"2008", "nb_salaries"=>"1 ou 2 salariés", "nom"=>nil, "prenom"=>nil, "civilite"=>nil, "adr_mail"=>nil, "libele_acti"=>"Syndicat de copropriété ", "categorie"=>"PME", "tel"=>nil}
{"siren"=>"38724340", "nom_ent"=>"SYND COPR DU 18 BD ARAGO 75013 PARIS", "adresse"=>"18 BOULEVARD ARAGO", "complement_adresse"=>nil, "cp_ville"=>"75013 PARIS", "pays"=>"FRANCE", "region"=>"Île-de-France", "departement"=>"75", "activite"=>"Activités combinées de soutien lié aux bâtiments", "date"=>"2008", "nb_salaries"=>"1 ou 2 salariés", "nom"=>nil, "prenom"=>nil, "civilite"=>nil, "adr_mail"=>nil, "libele_acti"=>"Syndicat de copropriété ", "categorie"=>"PME", "tel"=>nil}

This should work out of the box :

Transaction.create!(row.to_h)

Answer 2

Is you CSV file correctly parsed? I it separates columns via semicolon. You probably have to put that into options:

options = {col_sep: ";")