Jia Li Jia Li - 3 months ago 18
MySQL Question

Rails create table from csv that has special character in column name

I have a CSV file that i'm trying to load to rails database. Everything works fine until it gets to a column named

BW (MB)
. I'm thinking it's an issue with the parentheses as the other headers have spaces and they are fine. I'd like to avoid renaming the header in the original CSV file itself if possible.

Code:

CSV.foreach(csv_file, :headers => true) do |row|
Sevonedatum.create!(row.to_hash.slice('Date', 'Market Area', 'BW (MB)'))
end


Error output:


ActiveRecord::MultiparameterAssignmentErrors: 1 error(s) on assignment of multiparameter attributes [error on assignment ["96.26"] to BW (undefined method `BW =' for #)]


You can see the
MultiparameterAssignment
error is pointing to an issue when the value is being assigned to
BW
, though in the error it's missing the
(MB)
portion.

From the migration file:

class CreateSevonedata < ActiveRecord::Migration[5.0]
def change
create_table :sevonedata do |t|

t.column :'Date', :date
t.column :'Market Area', :string
t.column :'BW (MB)', :float
t.timestamps
end
end
end

Answer

You can't have parentheses in a column name: Rails will try to map the column name "BW (MB)" to a valid Ruby attribute name, and parentheses aren't allowed. Spaces convert to underlines OK, but parentheses aren't allowed.

Look at how Active Record does convention over configuration - one of the key principles of Rails.

Rails lets you do some amount of overriding, but you can't have a column "BW (MB)" that equates to a valid Ruby class attribute.

You have 2 options - both of which you need to change the column name to "BW".

Option 1: edit the headers of the CSV files so they match the column name, and change your code accordingly.

Option 2: leave the header as-is, but write additional code to replace the hash key "BW (MB)" with "BW"

CSV.foreach(csv_file, :headers => true) do |row|
  foo = row.to_hash.slice('Date', 'Market Area', 'BW (MB)')
  foo["BW"] = foo["BW (MB)"]
  foo.delete("BW (MB)")
  Sevonedatum.create!(foo)
end

(you can optimize that to fewer lines - I'm making it verbose since you've said you're a beginner)