Xls2csv

[Patrick_Fernie]

Here's some code I used to do some xls-> csv dumping. It's currently
rigged up to drop the files in tempfiles that some other code I have
use, so you'll need to monkey it a bit for your needs. Also, the dump
method returns a list of the temp files it created which you probably
won't need, but this is hopefully a good guide for you to hack up.

require 'win32ole'
require 'tempfile'

# The XLSdumper object is used to interface with an excel instance
# via OLE. It is used to load an xls file, and dump all non-empty
# sheets to csv files
class Tempfile
  def name
    /\:(.*)\>/.match(self.inspect).captures[0]
  end
end

module XLSdumper
  @@initialized = false
  @@excel = nil

  def XLSdumper.die
    Proc.new {
      @@excel.Quit
      @@excel.ole_free
   }
  end

  # This is used to load an xls file of name _filename_ with an excel object,
  # and dump the sheets containing data to csv files.
  def XLSdumper.dump(filename, directory)
    raise IOError, "No such file #{filename}" unless File.exists? filename

    directory = directory.chomp(File::SEPARATOR)

    files =
    begin
      unless @@initialized
       @@excel = WIN32OLE.new("excel.application")
  @@excel.DisplayAlerts = false # This should stop annoying dialogs
  ObjectSpace.define_finalizer(XLSdumper, XLSdumper.die)
        WIN32OLE.const_load(@@excel, XLSdumper)
        @@initialized = true
      end
      workbook = @@excel.workbooks.Open(filename)
      workbook.worksheets.each { |ws|
        if ws.usedrange.cells.value # only save sheets w/ data
          base = File.basename(filename, ".xls")
          csv_file = Tempfile.new(base+"-"+ws.name)
          csv_filename = File.expand_path(csv_file.name)
          csv_file.close
          files << csv_filename
          ws.SaveAs("#{csv_filename}", XLSdumper::XlCSV)
        end
      }
      workbook.close
    rescue WIN32OLERuntimeError => error
      puts "There was an error using Excel w/ OLE:\n#{error}"
    #ensure
      #excel.Quit
      #excel.ole_free
    end
    files
  end
end

···

On 8/7/05, Ara.T.Howard <Ara.T.Howard@noaa.gov> wrote:

i'm trying to hack together a little win32ole program named xls2csv - function
is obvious. basically i want to mass convert a whole slew of xls doccuments
into csv using the windows excel application to create validation files (the
csv files) for testing a *nix excel file reader. i'm sure i could batch this
under excel but i wanted a way to spawn the command remotely to a windows box
running sshd under cygwin so i don't have to get up - after all the windows
box is a least 10 feet away

any pointers appreciated.

-a
--

> email :: ara [dot] t [dot] howard [at] noaa [dot] gov
> phone :: 303.497.6469
> Your life dwells amoung the causes of death
> Like a lamp standing in a strong breeze. --Nagarjuna

[Ara.T.Howard6 (Ara.T.Howard)]

<snip>

perfect! thanks a million - saved me a ton of time.

-a

···

On Mon, 8 Aug 2005, Patrick Fernie wrote:

Here's some code I used to do some xls-> csv dumping. It's currently rigged
up to drop the files in tempfiles that some other code I have use, so you'll
need to monkey it a bit for your needs. Also, the dump method returns a list
of the temp files it created which you probably won't need, but this is
hopefully a good guide for you to hack up.

--

email :: ara [dot] t [dot] howard [at] noaa [dot] gov
phone :: 303.497.6469
Your life dwells amoung the causes of death Like a lamp standing in a strong breeze. --Nagarjuna

===============================================================================

[Ara.T.Howard6 (Ara.T.Howard)]

<snip xlsdumper code>

that ended up working great - i modified it to be:

     require 'win32ole'

     module XLS2CSV
       def xls2csv path
         unless @excel
           @excel = WIN32OLE::new "excel.application"
           @excel.DisplayAlerts = false
           @kill_excel = lambda{ @excel.Quit; @excel.ole_free}
           ObjectSpace.define_finalizer @excel, @kill_excel
           at_exit &@kill_excel
           WIN32OLE.const_load @excel, XLS2CSV
         end
         wb = @excel.workbooks.Open path
         wb.worksheets.each do |ws|
           next unless ws.usedrange.cells.value
           wb.worksheets.each do |ws|
             ws.SaveAs "#{ path }.#{ ws.name.downcase }.csv" , XlCSV
           end
         end
         wb.close
       end
       module_function 'xls2csv'
     end

     if $0 == __FILE__
       require 'logger'
       ARGV.each do |path|
         logger = Logger::new STDERR
         begin
           XLS2CSV::xls2csv path
         rescue Exception => e
           logger.error{ "path <#{ path }> failed!" }
           logger.error{ e }
         end
       end
     end

but this throws an error for one excel file i have on the

   next unless ws.usedrange.cells.value

line. like:

     [ahoward@localhost excel]$ /opt/cxoffice/bin/wine ~/.cxoffice/dotwine/fake_windows/ruby/bin/ruby.exe ./xls2csv xls/b\&bINVENT.xls
     E, [2005-08-10T08:25:46.448000 #-39] ERROR -- : path <Y:\excel\xls\b&bINVENT.xls> failed!
     E, [2005-08-10T08:25:46.448000 #-39] ERROR -- : value
         OLE error code:0 in <Unknown>
           <No Description>
         HRESULT error code:0x8007000e
            (WIN32OLERuntimeError)
     Y:/excel/xls2csv:18:in `method_missing'
     Y:/excel/xls2csv:18:in `xls2csv'
     Y:/excel/xls2csv:14:in `each'
     Y:/excel/xls2csv:14:in `xls2csv'
     Y:/excel/xls2csv:34
     Y:/excel/xls2csv:31:in `each'
     Y:/excel/xls2csv:31

this could be an artifact of running under wine - but all the other files
convert fine so i'm not positive. every seen that kind of error from your
xlsdumper code? others?

thanks for the tips.

-a

···

On Mon, 8 Aug 2005, Patrick Fernie wrote:

Here's some code I used to do some xls-> csv dumping. It's currently
rigged up to drop the files in tempfiles that some other code I have
use, so you'll need to monkey it a bit for your needs. Also, the dump
method returns a list of the temp files it created which you probably
won't need, but this is hopefully a good guide for you to hack up.

--

email :: ara [dot] t [dot] howard [at] noaa [dot] gov
phone :: 303.497.6469
Your life dwells amoung the causes of death Like a lamp standing in a strong breeze. --Nagarjuna

===============================================================================

[Patrick_Fernie]

Wow, the code looks so much neater ;). It's great to see a quick hack
get put to good use. Anyway, no, I haven't seen any errors like this.
I find bugs w/ win32ole a little hard to diagnose, what with the
errors being hidden behind OLE. It may be a wine issue, but if your
other files are working fine, it's probably something shortsighted in
the code. My first guess is something is going awry in the method
chaining on the "next" line; you could try splitting it up into
separate lines and seeing if that gives you an insight when compared
alongside a run with a 'working' xls file. On another tack, a quick
google for that particular error code suggested that it was an "out of
memory" code in some OLE/COM programming contexts, but this wasn't in
reference to Excel OLE programming specifically, so it is just as
likely to be a red herring. But, is that file decidedly larger or more
complex than others? Worth a look. Anyway, sorry I couldn't be more
helpful, hope something here inspires a quick bug-squash.

-Patrick

···

On 8/10/05, Ara.T.Howard <Ara.T.Howard@noaa.gov> wrote:

On Mon, 8 Aug 2005, Patrick Fernie wrote:

> Here's some code I used to do some xls-> csv dumping. It's currently
> rigged up to drop the files in tempfiles that some other code I have
> use, so you'll need to monkey it a bit for your needs. Also, the dump
> method returns a list of the temp files it created which you probably
> won't need, but this is hopefully a good guide for you to hack up.

<snip xlsdumper code>

that ended up working great - i modified it to be:

     require 'win32ole'

     module XLS2CSV
       def xls2csv path
         unless @excel
           @excel = WIN32OLE::new "excel.application"
           @excel.DisplayAlerts = false
           @kill_excel = lambda{ @excel.Quit; @excel.ole_free}
           ObjectSpace.define_finalizer @excel, @kill_excel
           at_exit &@kill_excel
           WIN32OLE.const_load @excel, XLS2CSV
         end
         wb = @excel.workbooks.Open path
         wb.worksheets.each do |ws|
           next unless ws.usedrange.cells.value
           wb.worksheets.each do |ws|
             ws.SaveAs "#{ path }.#{ ws.name.downcase }.csv" , XlCSV
           end
         end
         wb.close
       end
       module_function 'xls2csv'
     end

     if $0 == __FILE__
       require 'logger'
       ARGV.each do |path|
         logger = Logger::new STDERR
         begin
           XLS2CSV::xls2csv path
         rescue Exception => e
           logger.error{ "path <#{ path }> failed!" }
           logger.error{ e }
         end
       end
     end

but this throws an error for one excel file i have on the

   next unless ws.usedrange.cells.value

line. like:

     [ahoward@localhost excel]$ /opt/cxoffice/bin/wine ~/.cxoffice/dotwine/fake_windows/ruby/bin/ruby.exe ./xls2csv xls/b\&bINVENT.xls
     E, [2005-08-10T08:25:46.448000 #-39] ERROR -- : path <Y:\excel\xls\b&bINVENT.xls> failed!
     E, [2005-08-10T08:25:46.448000 #-39] ERROR -- : value
         OLE error code:0 in <Unknown>
           <No Description>
         HRESULT error code:0x8007000e
            (WIN32OLERuntimeError)
     Y:/excel/xls2csv:18:in `method_missing'
     Y:/excel/xls2csv:18:in `xls2csv'
     Y:/excel/xls2csv:14:in `each'
     Y:/excel/xls2csv:14:in `xls2csv'
     Y:/excel/xls2csv:34
     Y:/excel/xls2csv:31:in `each'
     Y:/excel/xls2csv:31

this could be an artifact of running under wine - but all the other files
convert fine so i'm not positive. every seen that kind of error from your
xlsdumper code? others?

thanks for the tips.

-a
--

> email :: ara [dot] t [dot] howard [at] noaa [dot] gov
> phone :: 303.497.6469
> Your life dwells amoung the causes of death
> Like a lamp standing in a strong breeze. --Nagarjuna

[bitrocker]

first of all: i'm new to the list, i'm new to ruby, i'm not to suse
anyway: hi all!

here's my prob (which doesnt seem to be my problem only, but i could'nt
find any answer searching the net high and low)

i installed ruby using yast (ruby 1.8.2), then i tried to install gems
like this:

1. cd /usr/local/src
2. wget http://rubyforge.org/frs/download.php/5207/rubygems-0.8.11.tgz
3. tar -zxvf rubygems-0.8.11.tgz
(which extracts to rubygems-0.8.10, not rubygems-0.8.11 ???)
4. cd rubygems-0.8.10
5. ruby setup.rb
6. this causes the follwing (zlib?) error:

As of RubyGems 0.8.0, library stubs are no longer needed.
Searching $LOAD_PATH for stubs to optionally delete (may take a while)...
...done.
No library stubs found.

/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__':
No such file to load -- zlib (LoadError)
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/package.rb:9
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/builder.rb:1
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems.rb:61:in
`manage_gems'
         ... 7 levels...
        from setup.rb:887:in `exec_install'
        from setup.rb:705:in `invoke'
        from setup.rb:674:in `invoke'
        from setup.rb:1352

... everything i try now: ruby setup.rb setup, ruby setup.rb install,
gem install rubygems-update ... everything causes this zlib error. i
installed the latest zlib and zlib-devel package via yast... no change.

my zlib is:

zlib-1.2.1-70.12

ruby is:

ruby 1.8.2 (2004-12-25) i686-linux

i tried searching for other ruby or zlib packages using yast but there
aren't any other

anyone?

thanks: lars

[Eric_Promislow]

bitrocker wrote:

first of all: i'm new to the list, i'm new to ruby, i'm not to suse
anyway: hi all!

here's my prob (which doesnt seem to be my problem only, but i could'nt
find any answer searching the net high and low)

i installed ruby using yast (ruby 1.8.2), then i tried to install gems
like this:

1. cd /usr/local/src
2. wget http://rubyforge.org/frs/download.php/5207/rubygems-0.8.11.tgz
3. tar -zxvf rubygems-0.8.11.tgz
(which extracts to rubygems-0.8.10, not rubygems-0.8.11 ???)
4. cd rubygems-0.8.10
5. ruby setup.rb
6. this causes the follwing (zlib?) error:

As of RubyGems 0.8.0, library stubs are no longer needed.
Searching $LOAD_PATH for stubs to optionally delete (may take a while)...
..done.
No library stubs found.

/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__':
No such file to load -- zlib (LoadError)
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/package.rb:9
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/builder.rb:1
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems.rb:61:in
`manage_gems'
         ... 7 levels...
        from setup.rb:887:in `exec_install'
        from setup.rb:705:in `invoke'
        from setup.rb:674:in `invoke'
        from setup.rb:1352

.. everything i try now: ruby setup.rb setup, ruby setup.rb install,
gem install rubygems-update ... everything causes this zlib error. i
installed the latest zlib and zlib-devel package via yast... no change.

my zlib is:

zlib-1.2.1-70.12

ruby is:

ruby 1.8.2 (2004-12-25) i686-linux

i tried searching for other ruby or zlib packages using yast but there
aren't any other

anyone?

thanks: lars

I'm on Windows, trying to install rails with the preview version
of Ruby 1.8.3, built on August 9, 2005. (Why? Because I wanted
to do some performance analysis, and the ruby-prof library wants
something built after March 22, 2005).

The only way I could get the zlib bindings built and installed was
by downloading the zlib library, explicitly building ext/zlib
against that version, and installing it. Seems to work, although
there are no tests I could find.

Here's how I did it:

1. Install zlib sources and binaries for Win32 from
www.zlib.org into C:/apps/GnuWin32

2. cd <ruby-src-distn>/ext/zlib

3. ruby extconf.rb --with-zlib-include=C:/apps/GnuWin32/include \
--with-zlib-lib=C:/apps/GnuWin32/lib

4. nmake DESTDIR=<target-dir> install

5. cp c:/apps/GnuWin32/bin/zlib1.dll c:/ruby183/bin

6. ruby -r zlib -e 1
Doesn't complain

7. cd back to rubygem download dir

8. ruby setup.rb
Works

9. gem install rails --include-depencies
Works

This only applies to working from source. Previously I've used
Win and OSX installers, and had no problems.

HTH, Eric

[Caio_Moritz_Ronchi]

Lars, I was having the same problems you've describe trying to install
RubyGems on Ubuntu Linux. I had the zlib package installed, and still
I was getting the same errors that you did.

Things changed when I installed the zlib-dev package. I think that's
the solution for your problem too.

cheers,

···

On 8/10/05, bitrocker <rocknroll@bitrocker.com> wrote:

first of all: i'm new to the list, i'm new to ruby, i'm not to suse
anyway: hi all!

here's my prob (which doesnt seem to be my problem only, but i could'nt
find any answer searching the net high and low)

i installed ruby using yast (ruby 1.8.2), then i tried to install gems
like this:

1. cd /usr/local/src
2. wget http://rubyforge.org/frs/download.php/5207/rubygems-0.8.11.tgz
3. tar -zxvf rubygems-0.8.11.tgz
(which extracts to rubygems-0.8.10, not rubygems-0.8.11 ???)
4. cd rubygems-0.8.10
5. ruby setup.rb
6. this causes the follwing (zlib?) error:

As of RubyGems 0.8.0, library stubs are no longer needed.
Searching $LOAD_PATH for stubs to optionally delete (may take a while)...
...done.
No library stubs found.

/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__':
No such file to load -- zlib (LoadError)
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/package.rb:9
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems/builder.rb:1
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require__'
        from
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:18:in
`require'
        from /usr/local/lib/ruby/site_ruby/1.8/rubygems.rb:61:in
`manage_gems'
         ... 7 levels...
        from setup.rb:887:in `exec_install'
        from setup.rb:705:in `invoke'
        from setup.rb:674:in `invoke'
        from setup.rb:1352

... everything i try now: ruby setup.rb setup, ruby setup.rb install,
gem install rubygems-update ... everything causes this zlib error. i
installed the latest zlib and zlib-devel package via yast... no change.

my zlib is:

zlib-1.2.1-70.12

ruby is:

ruby 1.8.2 (2004-12-25) i686-linux

i tried searching for other ruby or zlib packages using yast but there
aren't any other

anyone?

thanks: lars

--
Caio Moritz Ronchi