Reading from databases to arrays

Resolver One gives you an easy way to load data from database tables into worksheets, using the Database Worksheet functionality, but there is no built-in way to load data into ranges of cells. This is because the amount of data returned by a database query can change, and cell ranges returned by one database query can start to overwrite data returned by another.

However - sometimes you have a query where you know for a fact that only the first, say, ten rows matter. Or perhaps you have a query that returns only ten rows anyway, because you use a TOP keyword in the SELECT statement.

Or perhaps you want to pull in data from a database but just access a subset of the values, by referring into the returned range.

This example shows how you can do that, using a simple bit of IronPython code, and Resolver One's array functions.

Because this example reads from a database, in order to get it working you will need an appropriately-configured database; it should look like the one used in the Writing to a database example and contain at least 8 rows. However, the principles of how the spreadsheet works should be clear from examining the code.

Getting data from a database

The first thing to look at is in the pre-constants user code; there is a long, but simple, function that gets data from a database and puts it into an array. (This function is likely to be built into future versions of Resolver One, but you are welcome to copy and paste this version into your own sheets in the meantime).

def QueryDB(connectString, query):
    from System import Array
    from System.Data import DataSet
    from System.Data.Odbc import OdbcConnection, OdbcDataAdapter
    connection = OdbcConnection(connectString)
    adaptor = OdbcDataAdapter(query, connection)
    dataSet = DataSet()
    connection.Open()
    adaptor.Fill(dataSet)    
    connection.Close()
    if len(dataSet.Tables) > 0 and len(dataSet.Tables[0].Rows) > 0:
        ret = Array.CreateInstance(object, len(dataSet.Tables[0].Rows[0].ItemArray), len(dataSet.Tables[0].Rows))
        for rowIndex, row in enumerate(dataSet.Tables[0].Rows):
            for colIndex, val in enumerate([item for item in row.ItemArray]):
                ret[colIndex, rowIndex] = val
        return ret

Loading the data into the grid

In cell B1, we have a formula that uses the function to connect to the database and load data up:

=QueryDB("Driver={SQL Server};Server=NG\\SQLEXPRESS;Database=ResolverDemo;UID=demo;PWD=demo;", "select top 20 ID, Symbol, Price, Currency, BuySell, NumberOfShares, Broker FROM Transactions order by ID desc")

This returns a .NET Array object, and Resolver One will simply display that object in the grid as the string "<2D array>". There's more about how Resolver One handles arrays on the page devoted to them, but for now what we want to do is extract the data from the array and use it.

Unpacking the array into a range of cells

Things become a lot more interesting when we unpack the array. Cell B3 holds the formula "=B1", but has been unpacked into the range B3:G10. So now, we have a subset of the data from the database, inserted into the grid at a fixed point, which we know will not grow any larger - because the unpacked array is of fixed size. Unpacking is described on the Arrays page; in short, it allows you (using the key combination Control-Shift-Enter) to make a formula in one cell that returns an array, and display the values within the array in a range of other cells.

Referencing into the array for a single value

Another way to look at the values in an array, without unpacking it, is simply to use square brackets to look at a particular item; the formula in cell B12 does just this, and looks at the value in column 2, row 3 of the array. (Array indexes are zero-based.)

Comments

If you have comments, questions or suggestions about any of the Resolver One documentation, please post them to the Documentation Suggestions Forum.