xls2csv batch extractor

xls2csv.rb

#!/usr/bin/env ruby

cdir=ARGV[0]
cdir='.' if cdir.nil?

flist=Dir.glob(cdir.chomp('/')+'/*.xls')

flist.each do |x|
    wlist=`xls2csv -x #{x} -q -W | tail -n +4 | head -n -1`.split( /\r?\n/ )
    
    wlist.each do |y|
        fname=x.chomp('xls')+y+'.csv'
        puts `xls2csv -x #{x} -w #{y} -c #{fname}`
    end
end

This script converts all the worksheets of all the Excel files under the current directory or the directory passed to it as command line parameter into CSV files. It uses the excellent Perl-based xls2csv script that you can install from here: http://search.cpan.org/~ken/xls2csv/script/xls2csv. For this script to work correctly, you will need to put xls2csv somewhere in the PATH (e.g. /usr/local/bin).

This one only converts the active worksheet of the Excel file into CSV:

# Author: Vinay Kumar Bettadapura

if ARGV.length != 2
    puts "usage: \"ruby xls2csv.rb source_dir target_dir\""
    exit -1
end

source_dir = ARGV[0]
target_dir = ARGV[1]

if !File.exists?(target_dir)
    puts "target_dir \"#{target_dir}\" is not a valid directory"
    exit -1
end 

source_entries = []
begin
    source_entries = Dir.entries(source_dir).sort
    # To remove the first two array elements which 
    # will be "." and ".."
    source_entries.shift
    source_entries.shift
rescue Exception => e
    puts "source_dir \"#{source_dir}\" is not a valid directory"
    exit -1
end

if source_entries.empty?
    puts "source_dir \"#{source_dir}\" is empty"
    exit 0
end

source_entries.each{|file|
    source_file = source_dir + "/" + file
    target_file = target_dir + "/" + file.gsub(".xls", ".csv")

    puts "Converting \"#{source_file}\" to \"#{target_file}\""
    `xls2csv \"#{source_file}\" > \"#{target_file}\"`
}

puts "Done..."