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.