Ruby, Excel, and SQLite (instead of MS Access)

I used to rely on Microsoft Access for my desktop database needs, but have recently converted to SQLite and its associated Ruby library. SQLite is fast, light, and powerful. Combining SQLite with Ruby gives me the ability to quickly handle most data processing tasks.

Example: Let's say you've been given an Excel workbook with multiple worksheets of data, and you need to cross-reference data between worksheets. In many such cases, you'll want to import the data into a real database and use SQL.

Here's a brief, unpolished snippet of code that reads data from an open Excel workbook and creates an SQLite database with a table for each worksheet in the Excel workbook:

require 'win32ole'
require 'sqlite3'

#   Connect to a running instance of Excel
xl = WIN32OLE.connect('Excel.Application')
#   Get the active workbook
wb = xl.ActiveWorkbook
#   Create the SQLite3 database
db = SQLite3::Database.new('excel.db')
#   Create a database table for each worksheet 
#   in the workbook
wb.Worksheets.each do |ws|
    #   Grab all values from worksheet into a 
    #   2-dimensional array
    data = ws.UsedRange.Value
    #   Grab first row of data to use as field names
    field_names = data.shift
    #   Create database table using worksheet name and 
    #   field names
    db.execute("CREATE TABLE [#{ws.Name}] \
        ( #{field_names.join(',')} );")
    #   For each row of data...
    data.each do |row|
        #   ...single-quote all field values...
        row.collect! { |f| f = "'" + f.to_s + "'" }
        #   ...and insert a new record into the 
        #   database table
        db.execute("INSERT INTO [#{ws.Name}] VALUES \
            ( #{row.join(',')} );")
    end
end

Now, you've got your data loaded into a real database, quickly and easily, and can get to work on it... via Ruby or the SQLite command line interface.

I would be happy to provide more info on using SQLite3 with Ruby, but why the lucky stiff gives a pretty good jump-start here.