将空白Excel单元格保存到Rails Postgres数据库
将空白Excel单元格保存到Rails Postgres数据库
提问于 2017-01-26 03:45:19
我已经设法让Roo Gem正常工作，这样我就可以导入Excel文件并将其保存到我的应用程序的数据库中。这已经成功了几次，但似乎在某些行中包含空格的电子表格中会出现错误。我的代码如下：
需要“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我是从Ruby2.3.0中读到的，它在方法之前加入了一个&，它不包含nil方法错误，所以我希望这能行得通。
前4行似乎工作得很好，但当它到达下面有空格的行时，是我得到的错误的一个片段：
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'我假设Postgres不满足于数据库中保存的空数据，因此将"Abingdon & Witney College (Abingdon Campus)“移到另一列，因为相关记录是一个字符串，如下面的迁移记录所示：
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这就是问题所在吗?有什么建议可以解决这个问题吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-01-26 03:55:48
您的选择具有：
[
(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]
]后一个值不是数字，您迁移具有t.decimal :lat
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41860321
复制相关文章
相似问题
腾讯云开发者
