Parsing XLS Spreadsheet in Rails using Roo Gem

Question

I am trying to parse a XLS file with the roo gem without using a file upload plugin. Unfortunately I can not access the data of the File.

I get the error:

#<File:0x007ffac2282250> is not an Excel file

So roo is not recognizing the file as an Excel file. Do I need to save the file locally to use roo or is there a way around that. I would like to parse the data of the excel file directly into the database.

The params that are coming through:

Parameters: {"utf8"=>"✓", "authenticity_token"=>"yLqOpSK981tDNYjKSoWBh0VnFEKSk0XA/wOt3r+yWJc=", "uploadform"=>{"name"=>"xls", "file"=>#<ActionDispatch::Http::UploadedFile:0x007ffac22b6550 @original_filename="cities2.xls", @content_type="application/octet-stream", @headers="Content-Disposition: form-data; name=\"uploadform[file]\"; filename=\"cities2.xls\"\r\nContent-Type: application/octet-stream\r\n", @tempfile=#<File:/var/folders/qn/70msrkt90pd390sdr14_0g2m0000gn/T/RackMultipart20120306-3729-1m2xcsp>>}, "commit"=>"Save Uploadform"}

I am trying to access the file with

    if params[:uploadform][:file].original_filename =~ /.*\.xls$/i
        oo = Excel.new(params[:uploadform][:file].open)

        rooparse(oo)
    end

I also tried params[:uploadform][:file].read and params[:uploadform][:file] already but I think the .open would be the correct method here!?

And would you recommend using paperclip or carrierwave here?

Thank you for your help!

Answer 1

This is the path:

params[:file].tempfile.path.

You can try this:

Excel.new(params[:uploadform][:file].tempfile.path)

Answer 2

Looking at the source for Excel.new, it seems that it wants a file name, not a File object or handler. In other words, it needs string representation of the full path, including filename, to the the file you want to parse. Also, it checks the extension of the file. So if the tempfile doesn't end with ".xls" you'll need to rename the file first.

Answer 3

Yes, I can not parse the full file yet but that's another problem. At least I am getting the first row from the table into my database with the following lines:

require 'fileutils'
require 'iconv'

tmp = params[:uploadform][:file].tempfile
file = File.join("public", params[:uploadform][:file].original_filename)
FileUtils.cp tmp.path, file

oo = Excel.new(file)

rooparse(oo)
FileUtils.rm file

Thanks for your input!