ADO, Excel, and Data Types

I've written before about using Ruby with Microsoft's ADO technology to query Excel workbooks as databases.

This works well---most of the time. But you may occasionally bump into data type issues, where you find that Excel/ADO treat a specific column as a different data type than you expected.

For this reason, I recommend using a collection of ADO functions that will expressly convert a value to a specific data type. These ADO functions include:

CStr(x) - Converts x to a String value
CDec(x) - Converts x to a Decimal value
CInt(x) - Converts x to an Integer value
CDate(x) - Converts x to a Date value
CCur(x) - Converts x to a Currency value

For example, instead of assuming that the ORDER_NUMBER field would be treated as a string...

    SELECT * FROM ORDERS WHERE ORDER_NUMBER = '12345' ;

...call the CStr() function on it to ensure a string-to-string comparison:

    SELECT * FROM ORDERS WHERE CStr(ORDER_NUMBER) = '12345' ;

It's a few extra keystrokes that could save you time---and frustration---in the long run.

More information can be found here.

Using Ruby & ADO to Work with Excel Worksheets

In an earlier article, I discussed using ActiveX Data Objects (ADO) to access a Microsoft Access database. A reader commented that ADO can also be used to access data in an Excel worksheet. Here's a brief demonstration...

As usual, we'll use the win32ole library:

require 'win32ole'

Create a new ADODB.Connection object:

connection = WIN32OLE.new('ADODB.Connection')

To open a connection to your Excel workbook, we'll call the Connection object's Open method and pass it a connection string. You can use same the Microsoft Jet driver used for accessing an MS Access database, but we need to append an "Extended Property" to specify that this is an Excel woorkbook:

conn_string =  'Provider=Microsoft.Jet.OLEDB.4.0;'
conn_string << 'Data Source=c:\my_folder\my_workbook.xls;'
conn_string << 'Extended Properties=Excel 8.0;'
connection.Open(conn_string)

Now, we'll create an ADO recordset object:

recordset = WIN32OLE.new('ADODB.Recordset')

When calling the RecordSet object's Open method, pass it your SQL statement and the open connection object. When working with an Excel worksheet as your table, append '$' to the worksheet table name and wrap it in brackets:

recordset.Open("select * from [Sheet1$];", connection)

The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:

data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:

connection.close

There you have it! My thanks to reader Khaoz for the suggestion of using ADO with Excel.

Other articles about working with ADO can be found under the ado label to the right.

As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Using Ruby & ADO to Work with MS Access Databases

A reader in the Ruby forum recently asked about using Ruby to get data from a Microsoft Access database. This can be done easily using the ActiveX Data Objects (ADO), via the win32ole library. Let's walk through it...

As usual, require the win32ole library:

require 'win32ole'

Next, create a new ADODB Connection object, which will manage the connection to the database:

connection = WIN32OLE.new('ADODB.Connection')

To open a connection to the Access database, call the Connection object's Open method, passing it the Provider and Data Source (your Access mdb file) parameters:

connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:\baseball\lahman54.mdb')

To execute an SQL statement that does not return a recordset (ie, CREATE, ALTER, INSERT), you can call the Connection object's Execute method:

connection.Execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

To perform a query that returns a recordset of data, first create a new ADODB Recordset object:

recordset = WIN32OLE.new('ADODB.Recordset')

Then call the Recordset object's Open method, passing it the SQL statement and your existing Connection object:

recordset.Open(sql, connection)

This loads the query results into the Recordset object. The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:

data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:

recordset.Close

We could create a simple class to wrap up this functionality:

class AccessDb
    attr_accessor :mdb, :connection, :data, :fields

    def initialize(mdb=nil)
        @mdb = mdb
        @connection = nil
        @data = nil
        @fields = nil
    end

    def open
        connection_string =  'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        connection_string << @mdb
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        recordset = WIN32OLE.new('ADODB.Recordset')
        recordset.Open(sql, @connection)
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            @data = recordset.GetRows.transpose
        rescue
            @data = []
        end
        recordset.Close
    end

    def execute(sql)
        @connection.Execute(sql)
    end

    def close
        @connection.Close
    end
end

This class is very similar to an SqlServer class I previously discussed.
We would use this AccessDb class as follows:

db = AccessDb.new('c:\Baseball\lahman54.mdb')
db.open

db.query("SELECT * FROM AllStar WHERE playerID = 'conceda01';")
field_names = db.fields
rows = db.data

db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

db.close

That about wraps up our show for today. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Ruby, ADO, and SQLServer

Ruby and ActiveX Data Objects (ADO) make working with Microsoft SQL Server databases simple. Here's a simple example of a class that manages the SQL Server database connection and queries:

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields

    def initialize
        @connection = nil
        @data = nil
    end

    def open
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=USER_ID;"
        connection_string << "password=PASSWORD;"
        connection_string << "Initial Catalog=DATABASE;"
        connection_string << "Data Source=IP_ADDRESS;"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array 
        # of columns, so we'll use the transpose method to 
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

The above code is, of course, incomplete and can certainly be improved and extended (error handling, etc.). But, hopefully, it provides you with a solid foundation on which to build.

UPDATE: You might like to know that you can automate many of your SQL Server administrative tasks by leveraging Distributed Management Objects (SQL-DMO). I've explained this in a later article here.

Thanks for stopping by!