To convert Excel into CSV efficiently in ruby

Question

I used the spreadsheet gem to do this. It works but it can be very slow at times .I even tried the Roo gem, but that didn't improve the performance. Is there a better way to do this job? The weird thing is that some worksheets in the same excel work faster and some worksheets work very slowly, even taking up to 1 hour.

Can we use open office to open each worksheet(tab) in a single excel and convert them to csv much faster? If yes, how would I do it in ruby?

Or is there an even better solution?

Just adding a small example I tried with Roo gem

xls = Roo::Excel.new(source_excel_file)
xls.each_with_pagename do |name, sheet|
  # p sheet.to_csv(File.join(dest_csv_dir,name + ".csv"))
  #sheet.parse(:clean => true)#.to_csv(File.join(dest_csv_dir,name + ".csv"))
  puts name
  puts sheet.parse(:clean => true)
end

Maximomaximum

railshorde.com/blog/ruby-convert-excel-file-to-csv – Hillard

Answer 1

Cowardly Preface: I am SUPER new to ruby and know almost nothing of rails, but I have tangled with Excel before. I created a dummy workbook on my local machine with 5 sheets, each containing 10 columns and 1000 rows of randomly-generated numbers. I converted each sheet into its own CSV with this:

require 'win32ole'
require 'csv'

# configure a workbook, turn off excel alarms
xl = WIN32OLE.new('excel.application')
book = xl.workbooks.open('C:\stack\my_workbook.xlsx')
xl.displayalerts = false

# loop through all worksheets in the excel file
book.worksheets.each do |sheet|
  last_row = sheet.cells.find(what: '*', searchorder: 1, searchdirection: 2).row
  last_col = sheet.cells.find(what: '*', searchorder: 2, searchdirection: 2).column
  export = File.new('C:\\stack\\' + sheet.name + '.csv', 'w+')
  csv_row = []

  # loop through each column in each row and write to CSV
  (1..last_row).each do |xlrow|
    (1..last_col).each do |xlcol|
      csv_row << sheet.cells(xlrow, xlcol).value
    end
    export << CSV.generate_line(csv_row)
    csv_row = []
  end
end

# clean up
book.close(savechanges: 'false')
xl.displayalerts = true
xl.quit

An eyeball benchmark for this script was ~30 seconds, with each attempt coming in a few seconds above or below that.

Ashe

but I assume it is for a windows machine .I guess I should have mentioned it but I think it won't work in mac/unix/linux right ? – Maximomaximum

This one is a good solution for windows users but unfortunately I never use windows machine .I use a Mac and my code run on linux machines . – Maximomaximum

super stupid question but is it not possible to get win32ole on *nix? – Messuage

@DanPantry, very unlikely. under the hoods, win32ole is like "use installed microsoft office to do the work". it's very handy when you are on Windows+Office (all the examples in this comment are straightforward translation from Office VBA Help); but in any other environment you just can't get it. – Polka

Answer 2

I assume we are talking about old Excel format (xls), it seems spreadsheet gem can't work with xlsx anyways.

I'd give a try to one of command-line spreadhseet converters: either xls2csv from catdoc package (really fast, though not all Excel files are processed successfully) or ssconvert from gnumeric package (moderate speed, and requires to install entrie GNumeric, which sometimes not an option for server, but really robust).

NB: When parsing Excel, roo just requires spreadsheet and wraps it in own API, so it never can be faster or more reliable than spreadsheet.

NB2: If I remember correctly (thought it was some long years ago), trying to automate OpenOffice from ruby was a) really hard and b) really slow.

Polka

Only problem I have with xls2csv is what If I have multiple worksheets in an single excel and want them to be separate CSV . – Maximomaximum

xls2csv has -b option - "sheet break string". So, if your xls is succesfully parsed with xls2csv (mine are not, so I can't test it), you can either split output file with some more bash magic, like here; or you can capture output into Ruby with popen, and just use regular split method on it. – Polka

Mine neither are getting parsed :( . – Maximomaximum

give a try to ssconvert, then (though, if on server, it provides no option besides install the full gnumeric, which, in its turn, require all gnome libraries) – Polka

Answer 3

xls_file = Roo::Excelx.new('test.xlsx')
CSV.open('test.csv') do |csv|
    (2..xls_file.last_row).each do |i| # if you do not need header otherwise (1..xls_file.last_row)
        csv << a.row(i)
    end
end

Witham

way too low :D. There is this also as an alternative github.com/scpike/excel2csv/blob/master/src/excel2csv.rb But mostly what shanky Munjal said – Epoxy

Answer 4

Be sure you're using the up-to-date Roo (1.13.2).

Also be sure you're using the patch for skipping trailing blank rows:

https://github.com/Empact/roo/blob/master/lib/roo/worksheet.rb

If you can post one of your spreadsheets that's taking a long time to parse, it may help people here help you. Just be sure to delete any confidential data.

Maxima

yes I m using the latest gem . I tried this example with roo gem but it was very slow . – Maximomaximum

xls = Roo::Excel.new(source_excel_file) xls.each_with_pagename do |name, sheet| # p sheet.to_csv(File.join(dest_csv_dir,name + ".csv")) #sheet.parse(:clean => true)#.to_csv(File.join(dest_csv_dir,name + ".csv")) puts name puts sheet.parse(:clean => true) end – Maximomaximum

can you give me an example of how to use Roo for multiple sheet in single excel . – Maximomaximum