How to read an xlsx or xls file from a URL

Question

I'm trying to open a file from a URL using roo (1.11.0)

Foobazs-iMac:pipeline foobazlabs$ irb
irb(main):001:0> require 'roo'
require '=> true
irb(main):002:0> require 'open-uri'
=> true
irb(main):003:0> Roo::Excelx.new(open("https://www.filepicker.io/api/file/xPxn6cu3RZuQdgrx4y72"))
NoMethodError: undefined method `start_with?' for #<Tempfile:0x007ffd091d25c8>

but it looks like there isn't support for this using roo (it accepts only path names)

Can't use a temp file path name either

irb(main):004:0> t = Tempfile.new('temp.xlsx')
=> #<File:/var/folders/q_/q738l9cj3xl90yfs1g8wc4200000gn/T/temp.xlsx20130317-2753-1p3l5l6>
irb(main):005:0> t.binmode
irb(main):006:0> open("https://www.filepicker.io/api/file/xPxn6cu3RZuQdgrx4y72") { |data| t.write   data.read }
=> 13494
irb(main):007:0> Roo::Excelx.new(t.path)
use Roo::Excelx.new to handle .xlsx spreadsheet files. This has .xlsx20130317-2753-1p3l5l6
TypeError: /var/folders/q_/q738l9cj3xl90yfs1g8wc4200000gn/T/temp.xlsx20130317-2753-1p3l5l6 is not an Excel-xlsx file

Any Ruby library/gem that I can use for this purpose? I only need to read xls and xlsx files. I don't need to write. Since I'm on Heroku, I can only use Tempfiles not actual Files.

Have you tried passing in the url without `open`? According to the documentation, the way you're supposed to instantiate a new `Excel` object is as follows: `oo = Excel.new("http://www.somedomain.com/simple_spreadsheet.xls")` — fbonetti, Mar 17 '13 at 16:57
I fixed it by removing the check for .xlsx extension in roo. Of course, the use case ensures that it is always an .xlsx file so it works for my case. — Robin Rosicky, Apr 01 '13 at 08:46

score 1 · Answer 1 · edited May 17 '13 at 13:55

This might not be what you have in mind, but I use two packages to read xls and xlsx files, which are more common than I would like.

read.xlsx in xlsx uses Java, works most of the time. I don't think it reads straight from a url, I usually download.file first.
read.xls in gdata uses Perl (download if you uses Windows, otherwise automatic), and reads straight from a url. Here's an example that reads in a file from the UN population projections:

library("gdata")
url <- "http://esa.un.org/unpd/wpp/Excel-Data/DB04_Population_ByAgeSex_Annual/WPP2010_DB4_F1B_POPULATION_BY_AGE_BOTH_SEXES_ANNUAL_2011-2100.XLS"
data <- read.xls(url, sheet=1)

score 1 · Answer 2 · answered Jul 12 '13 at 21:17

You can use RemoteTable for XLSX, XLS, CSV, and other formats:

require 'remote_table'
RemoteTable.new('https://www.filepicker.io/api/file/xPxn6cu3RZuQdgrx4y72', format: :xlsx).each do |row|
  puts row
end

Gives you:

{"Name"=>"Kristina H. Chung", "E-mail"=>"cheenu.madan@gmail.com", "Phone"=>"8032696336.0", "Org"=>"ABCD"}
{"Name"=>"Paige H. Chen", "E-mail"=>"cheenu.madan@gmail.com", "Phone"=>"7502167067.0", "Org"=>"ABCD"}
{"Name"=>"Sherri E. Melton", "E-mail"=>"cheenu.madan@gmail.com", "Phone"=>"7044576998.0", "Org"=>"ABCD"}
{"Name"=>"Gretchen I. Hill", "E-mail"=>"cheenu.madan@gmail.com", "Phone"=>"7967784377.0", "Org"=>"ABCD"}
{"Name"=>"Karen U. Puckett", "E-mail"=>"cheenu.madan@gmail.com", "Phone"=>"9151299999.0", "Org"=>"ABCD"}
[...]

score 0 · Answer 3 · answered Oct 22 '13 at 08:52

0

I was able to get ride of the NoMethod error (undefined method start_with?) by adding an additional to_s string conversion to the path:

Roo::Excelx.new(path.to_s)

In my case it was an uploaded Excel file which I tried to process with the Roo gem, though

answered Oct 22 '13 at 08:52

0x4a6f4672

27,297
17
103
140

score 0 · Answer 4 · answered Nov 15 '14 at 21:24

0

Nothing here works for me, but this yes(for xlsx):

file =  Roo::Excelx.new(params[:file].path,nil, :ignore)

from: http://railscasts.com/episodes/396-importing-csv-and-excel?view=asciicast

answered Nov 15 '14 at 21:24

overallduka

1,520
19
27

How to read an xlsx or xls file from a URL

4 Answers4