将数据从Excel电子表格提取到Ruby数据库 [英] Extract Data from Excel Spreadsheet into Database in Ruby

问题描述

我想从Rails应用程序的电子表格中提取大量数据，但我正在通过Rails控制台进行提取。

我有在我的数据库中有一个名为 instititutes 的表，目前有大约170条记录。我在一个有c.1000记录的电子表格中发现了更好的数据，并希望将其导入我的数据库，但跳过任何匹配的记录。

使用find_or_create_by方法将是最好的移动。电子表格的标题为 UKPRN 和 NAME ，表格中有 ukprn 和 name 作为相应的列（以及其他）。

使用Roo gem，到目前为止：

  require'roo'
 
 xlsx = Roo :: Spreadsheet.open （File.expand_path（'../ Downloads / UKPRN.xlsx'））
 xlsx.default_sheet = xlsx.sheets.last 
 header = xlsx.row（1）
 xlsx.each_row do | row | 
 row = Institute.find_or_create_by（UKPRN：ukprn，NAME：name）
 end 
  

这给出错误 NameError：unitialized constant UKPRN 。我仍然把我的头回到Ruby，所以任何帮助将赞赏。

我使用Ruby 2.2.3和Rails 5.0.1

编辑：

p row 显示：

  [UKPRN，NAME] 
 [10000291，Anglia Ruskin University] 
 [10000385，大学伯恩茅斯] 
 [10000571，巴斯温泉大学] 
 [10000712，伯明翰大学学院] 
 [10000824，伯恩茅斯大学] 
 [10000886，布莱顿大学] 
 [10000961，布鲁内尔大学伦敦] 
 ...等
  

解决方案

您的表格有 ukprn 和名称列，因此 find_or_create 应如下所示：

  Institute.find_or_create_by ：ukprn，name：name）
  

现在你只需要初始化 ukprn 。

pre> require'roo'

xlsx = Roo :: Excelx.new（File.expand_path（'../ Downloads / UKPRN.xlsx'））

xlsx.each_row_streaming（offset：1）do | row |
Institute.find_or_create_by（ukprn：row [0] .value，name：row [1] .value）
end


要执行此代码，请执行以下操作之一：

• 将其放在 db / seeds.rb 并执行 rake db：seed


• .rb 并运行 rails runner script.rb


• 将其复制粘贴到控制台）

I'm looking to extract quite a lot of data from spreadsheets for a Rails application, but I'm doing the extraction through the Rails Console.

I have a table called instititutes in my database which currently has around 170 records. I've found far better data in a spreadsheet with c.1000 records and want to import that to my database, but to skip any records that match already.

I thought that using the find_or_create_by method would be the best move for this. The spreadsheet has UKPRN and NAME as headers, and the table has ukprn and name as respective columns (as well as others).

Using the Roo gem, I've got this so far:

require 'roo'

xlsx = Roo::Spreadsheet.open(File.expand_path('../Downloads/UKPRN.xlsx'))
xlsx.default_sheet = xlsx.sheets.last
header = xlsx.row(1)
xlsx.each_row do |row|
    row = Institute.find_or_create_by(UKPRN: ukprn , NAME: name)
end

This is giving the error NameError: unitialized constant UKPRN. I'm still getting my head back into Ruby so any help would be appreciated.

I'm using Ruby 2.2.3 and Rails 5.0.1

Edit:

p row shows:

["UKPRN", "NAME"]
[10000291, "Anglia Ruskin University"]
[10000385, "The Arts University Bournemouth"]
[10000571, "Bath Spa University"]
[10000712, "University College Birmingham"]
[10000824, "Bournemouth University"]
[10000886, "The University of Brighton"]
[10000961, "Brunel University London"]
...etc

解决方案

Your table has ukprn and name as respective columns, so find_or_create should look like :

Institute.find_or_create_by(ukprn: ukprn , name: name)

Now you just need to initialize ukprn and name from row.

require 'roo'

xlsx = Roo::Excelx.new(File.expand_path('../Downloads/UKPRN.xlsx'))

xlsx.each_row_streaming(offset: 1) do |row|
  Institute.find_or_create_by(ukprn: row[0].value, name: row[1].value)
end

To execute this code, either :

• put it in db/seeds.rb and execute rake db:seed
• put it in script.rb and run rails runner script.rb
• copy-paste it in console (not really recommended)

这篇关于将数据从Excel电子表格提取到Ruby数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！