Thursday, January 13, 2011

Excel spreadsheets & Ruby => :tricky

I've been working on an ERP data conversion project, using Ruby to convert the data before it gets dumped into the Oracle database.  I only recently got involved to help out, and an earlier decision mandated that we produce our converted data in spreadsheets [I know, I know -- not what I would have decided, but they wanted folks to be able to easily manipulate the data].

Anyway, we're using the Spreadsheet gem which is pretty nice; however, it does have some limitations, which can result in oblique error messages such as the following:

ruby customer_fix_zip.rb
/Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:376:in `validate!': OLE2 signature is invalid (Ole::Storage::FormatError)
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:368:in `initialize'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:110:in `new'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:110:in `load'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:77:in `initialize'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:83:in `new'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/ruby-ole-1.2.11.1/lib/ole/storage/base.rb:83:in `open'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/spreadsheet-0.6.5.0/lib/spreadsheet/excel/reader.rb:1144:in `setup'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/spreadsheet-0.6.5.0/lib/spreadsheet/excel/reader.rb:121:in `read'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/spreadsheet-0.6.5.0/lib/spreadsheet/excel/workbook.rb:32:in `open'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/spreadsheet-0.6.5.0/lib/spreadsheet.rb:62:in `open'
    from /Users/jseidel/.rvm/gems/ruby-1.9.2-head/gems/spreadsheet-0.6.5.0/lib/spreadsheet.rb:68:in `open'
    from customer_fix_zip.rb:12:in `'

Googling for help didn't... there are only a few posts out there about this situation, but not many responses.

This may be caused by a password on the spreadsheet -- at least that was the cause in my case. Once I had removed the password protection, everything worked just fine.  Hopefully, this will help other folks out there who are experiencing the same problem.

1 comment:

  1. I have the same problem, but it isn't anything to do with a password in my case, so it might be due to the fact that you read it and rewrote it; which is consistent with other people's experience in simply opening and saving it to another file.

    ReplyDelete