I've managed to get the Roo Gem working so that I can import an Excel file and save it to the database of my app. This has been successful a few times, but it appears that in spreadsheets that contain blanks in certain rows it brings up an error. My code is as follows:
require 'roo'
xlsx = Roo::Excelx.new(File.expand_path('../Downloads/LOCATION.xlsx'))
xlsx.each_row_streaming(offset: 1) do |row|
Location.find_or_create_by(ukprn: row[0]&.value, accomurl: row[1]&.value, instbeds: row[2]&.value, instlower: row[3]&.value, instupper: row[4]&.value, locid: row[5]&.value, name: row[6]&.value, lat: row[7]&.value, long: row[8]&.value, locukprn: row[9]&.value, loccountry: row[10]&.value, privatelower: row[11]&.value, privateupper: row[12]&.value, suurl: row[13]&.value)
end
I read that from Ruby 2.3.0 by including an & before the method it doesn't include nil method errors, so I was hoping this would work.
The first 4 rows seem to work fine but as it gets to a row with blanks in Below is a snippet of the error I get:
Location Load (1.1ms) SELECT "locations".* FROM "locations" WHERE "locations"."ukprn" = $1 AND "locations"."accomurl" = $2 AND "locations"."instbeds" IS NULL AND "locations"."instlower" = $3 AND "locations"."instupper" = $4 AND "locations"."locid" = $5 AND "locations"."name" = $6 AND "locations"."lat" = $7 AND "locations"."long" IS NULL AND "locations"."locukprn" = $8 AND "locations"."loccountry" = $9 AND "locations"."privatelower" = $10 AND "locations"."privateupper" = $11 AND "locations"."suurl" = $12 LIMIT $13 [["ukprn", "10000055"], ["accomurl", "http://www.brookes.ac.uk/studying-at-brookes/accommodation/halls-in-detail/"], ["instlower", 4081], ["instupper", "4684"], ["locid", "6463"], ["name", "AB"], ["lat", "Abingdon & Witney College (Abingdon Campus)"], ["locukprn", 51], ["loccountry", "-1.28696"], ["privatelower", 10000055], ["privateupper", "XF"], ["suurl", "4219"], ["LIMIT", 1]]
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type numeric: "Abingdon & Witney College (Abingdon Campus)"
: SELECT "locations".* FROM "locations" WHERE "locations"."ukprn" = $1 AND "locations"."accomurl" = $2 AND "locations"."instbeds" IS NULL AND "locations"."instlower" = $3 AND "locations"."instupper" = $4 AND "locations"."locid" = $5 AND "locations"."name" = $6 AND "locations"."lat" = $7 AND "locations"."long" IS NULL AND "locations"."locukprn" = $8 AND "locations"."loccountry" = $9 AND "locations"."privatelower" = $10 AND "locations"."privateupper" = $11 AND "locations"."suurl" = $12 LIMIT $13
from /home/dave/.rvm/gems/ruby-2.3.3/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:606:in `exec_prepared'
I assume that Postgres is not content with the null data being saved in the database and therefore the "Abingdon & Witney College (Abingdon Campus)" is moved to another column, because the relevant record is a string as shown in the migration record below:
class CreateLocations < ActiveRecord::Migration[5.0]
def change
create_table :locations do |t|
t.text :name
t.decimal :lat
t.decimal :long
t.string :locid, index: true
t.integer :locukprn
t.string :loccountry
t.integer :privatelower
t.string :privateupper
t.string :ukprn, index: true
t.string :suurl
t.string :accomurl
t.integer :instbeds
t.integer :instlower
t.string :instupper
t.references :institute, foreign_key: true, index: true
end
end
end
Is this what's going wrong, and are there any suggestions as to how to solve this?
Your Select has:
[
(0)["ukprn", "10000055"],
(1)["accomurl", "http://www.brookes.ac.uk/studying-at-brookes/accommodation/halls-in-detail/"],
(2)["instlower", 4081],
(3)["instupper", "4684"],
(4)["locid", "6463"],
(5)["name", "AB"],
(6)["lat", "Abingdon & Witney College (Abingdon Campus)"],
(7)["locukprn", 51],
(8)["loccountry", "-1.28696"],
(9)["privatelower", 10000055],
(10)["privateupper", "XF"],
(11)["suurl", "4219"],
(12)["LIMIT", 1]
]
This lat value is not a numeric and your migration has t.decimal :lat