How to use cDataSet - Desktop Liberation

This is intended to be a fasttrack to get started with the Data Manipulation Classes that are used throughout the examples on this site. These classes are used to abstract data over the excel worksheet model and free the data from its physical location. You can also see the cDataSet Model here.

What to download

All the examples contain all the classes needed for them to work and all projects can be found here. If you just want the main utility classes used throughout this site, in the Downloads section cDataSet.xlsm includes everything you need including some examples.

A quick primer

cDataSet primer – Abstracting Excel and Google Spreadsheet data from physical location from Bruce McPherson

Creating a dataSet

Here is the simplest framework to read a worksheet into a cDataSet object. In this case we are using most of the defaults to populate a cDataSet. It will extrapolate the likely table size and column headings from a single cell start point, with a blank row and blank column delimiting the extent of the data.

Public Sub mainExample()
    Dim dSet As cDataSet
    Set dSet = New cDataSet
    With dSet
        .populateData Range("Orders!$a$1"), , , , , , True
        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            Call dosomething(dSet)
        End If
    End With
End Sub

Looping through the rows in a cDataSet

This example loops through every row in the dataSet and prints the cell in the ‘Total’ Column of each row.

Private Sub dosomething(dSet As cDataSet)
    Dim dr As cDataRow
    With dSet
        For Each dr In .Rows
            msgbox ("Total is " & dr.Cell("Total").toString) 
        Next dr
    End With
End Sub

Updating cells in cDataset

By design, all manipulation is on the contents of the cDataSet, not on the underlying worksheet. This means that processing is many times faster than you are used to, and it also means that you do not need to commit the changes until everything is finished. This makes rollback very simple if something goes wrong. You just don’t commit it and the worksheet stays intact. This example calculates a total column and commits the changes to that column only. You can commit the entire DataSet with dSet.Commit, but since we are only updating one column there is no point.

Private Sub dosomething(dSet As cDataSet)
    Dim dr As cDataRow
    With dSet
        For Each dr In .Rows
           dr.Cell("Total").Value = dr.Cell("Quantity").Value *          dr.Cell("Price").Value
            Next dr
            ' write the total column back, since we've updated it
            .Column("total").Commit
        End If
    End With
End Sub

Checking if all the columns you need are present

This will check whether the stated fields are present, and complain if any are not.

 With dSet
        ''... we can also check we have some mandatory fields if necessary
        If .HeadingRow.Validate(True, "Customer", "Country", "Total", "Quantity", "Price", "Contact") Then
        ' do something            
        End If
    End With

Creating cDataSets

This is a collection of cDataSet, which has methods to also create the cDataSet. Normally you are dealing with more than one cDataSet. This allows you to group them together. In this case we are creating 3 cDataSets named summary, formatparameters and country parameters. They can later be accessed through these names, for example dSets.DataSet(“summary”)

Dim dSets As cDataSets
    Set dSets = New cDataSets
    With dSets
        .create
        ' input summary data
        .init Range("summary!$a$1"), , "summary", , , True
        ' parameter blocks
        .init wholeSheet("parameters"), , "formatparameters", True, "Formats"
        .init wholeSheet("parameters"), , "countryparameters", True, "Country"
    End With

Creating a parameter cDataSet

Parameter sheets are a useful way of providing variable data to your procedures. A parameter cDataSet gives you an very quick an easy way to devise a parameter methodology and syntax. For an example of a parameter sheet, look here. Parameters are given as a table with rows where the first column is the parameter name, and columns containing the values associated with that parameter name. For example, a parameter called Plots.Chart.Width would be on the table called plots, at the intersection of row charts and column width. cDataSets allows multiple tables on the same worksheet, identified by a unique table name, and also allows you to access a particular cell through it’s name. So the value of the example parameter above would be dSets.DataSet(“plots”).Cell(“chart”,”width”).Value. This example creates a dataset (from worksheet “summary”) and two parameter sets (from worksheet “parameters”.

Dim dSets As cDataSets
    Set dSets = New cDataSets
    With dSets
        .create
        ' input summary data
        .init Range("summary!$a$1"), , "summary", , , True
        ' parameter blocks
        .init wholeSheet("parameters"), , "formatparameters", True, "Formats"
        .init wholeSheet("parameters"), , "countryparameters", True, "Country"
    End With

Copying a cDataSet

Every object (cCell, cDataSet, cDataRow, cDataColumn, cHeadingRow) has a property .Where that contains the Excel address of the cell or group of cells. They also have a method, .Commit, which will write back that group of cells to the worksheet. You can also copy the data to a place other than the original location specified in the .Where property. For example, dSet.bigCommit Range(“Contact!$a$1”) will copy an entire dataset to some location starting at the given range.

Selectively copying a cDataSet

.bigCommit can also selectively copy certain columns, and reorder them if required. The example below checks a dataset has the expected fields, performs some calculation on various columns, writes back the changed columns and copies a subset of the dataset to another sheet, with the columns rearranged.

 Dim dr As cDataRow
    With dSet
        ''... we can also check we have some mandatory fields if necessary
        If .HeadingRow.Validate(True, "Customer", "Country", "Total", "Quantity", "Price", "Contact") Then
            ' do some manipulation
            For Each dr In .Rows
                dr.Cell("Total").Value = dr.Cell("Quantity").Value * dr.Cell("Price").Value
            Next dr
            ' write the total column back, since we've updated it
            .Column("total").Commit
            ' now copy a couple of columns to another sheet
            .bigCommit Range("Summary!$a$1"), True, Array("customer", "contact", "total", "country")
        End If
    End With

Filtering with .bigCommit

In addition to being able to selectively copy particular columns, .bigCommit can also filter on specific values. In this example, we have a form that is being used to allow the selection of various columns to be copied using these objects. You can also specify a filterColumn and value. In this case only rows that match the filtervalue in the specified column will be copied.

.bigCommit ws.Cells(1, 1), True, arrayofColumnNames, filterColumn, cbxFilterValue.Value, chbApproximate.Value

Here is the implemented form, with all fields except Total being copied and a filter in the middle of being applied to the customer column. Implementing such a form is very simple, and is completely data driven. the full example is in the GettingStarted Series download.

Returning the unique values in a column

In the example above, you can see that the filter Value combox has been populated with every value found in the Customer Column. This is easily achieved through the cDataColumn(“Customer”).uniqueValues property. This is how to populate the data set and return a sorted collection of cCell which hold the first occurrence of each unique value in the selected column.

 With dSet
                .populateData wholeSheet(cbxWsIn.Value), , , , , , True
                With .Column(CStr(cbxFilterField.Value))
                    Set uv = .uniqueValues(eSortAscending)
                End With
            End With

This can then be used to populate the selection combobox

With cbxFilterValue
                .AddItem "...pick a value"
                For Each cc In uv
                    .AddItem CStr(cc.Value)
                Next cc
                .Value = .List(0)
            End With

The .cJobject property

It is possible to return a cJobJect from a cDataSet. This is a arbitrary object that can be used to represent data of a hierarchical, or structured format. You can think of it as a ‘class on demand’. The main purpose of this class on this site is to convert to and from jSon in order to communicate with web applications. cDataSet is also capable of populating from a cJobject. This means that we have a very simple way to get Excel data in and out of other web application such as Google Visualizations. In this example, we create a dataset from a worksheet, and create a copy of that sheet from the cJobject representation of that original worksheet. This illustrates the capabilty to both serialize and deserialize a cJobject.

Option Explicit
Public Sub jobjectExample()
Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject
' to test delete the contents of the worksheet 'clone' - this will fill it up
Set dSet = New cDataSet
With dSet
.populateData Range("orders!$a$1"), , , , , , True
If .Where Is Nothing Then
MsgBox ("No data to process")
Else
' make a json object of one sheet, and the deJSon it in a clone
Set dsClone = New cDataSet
dsClone.populateJSON .jObject, Range("Clone!$A$1")
End If
End With

The Google Wire Protocol

Google use this to get data in and out of Google Docs, and to communicate with Gadgets. This site has plenty of examples of projects that do this. Here is how to populate your spreadsheet with data that is intended for a Google Gadget. You can create a URL of your data in google docs , and this will get the data in real time and update your Excel Worksheet.

Public Sub googleWireExample()
    Dim dSet As cDataSet, dsClone As cDataSet, jo As cJobject, cb As cBrowser
    Dim sWire As String
    ' get the google wire string
    ' to test, delete the contents of the worksheet sheet 'clone' - this will fill it up
    Set cb = New cBrowser
    sWire = cb.httpGET(Url)
    ' load to a dataset
    Set dSet = New cDataSet
    With dSet
        .populateGoogleWire sWire, Range("Clone!$a$1")
        If .Where Is Nothing Then
            MsgBox ("No data to process")
        Else
            ' it worked
        End If
    End With
    Set dSet = Nothing
    Set cb = Nothing
End Sub

Using Google Maps

We have all the tools now to be able to use google maps to fill in geoMapping data in an Excel Sheet. There is a full example of this here. This is a little more complex than the examples so far, but here is an example of a full application. In this case, the rules for how to interpret the jSon response from an inquiry to the Google Maps API are held in a cDataSet parameter sheet, as are the rules for how to identify the key fields needing decoded in the input address, and what kind of fields the output fields are. In future versions of cDataSet, this will likely become an inbuilt method so that mapping will become a capability of these objects.

Public Sub googleMappingExample()
    Dim dSets As cDataSets
    Dim ds As cDataSet, dr As cDataRow, sWire As String, dFields As cDataSet
    Dim jo As cJobject, cb As cBrowser, sReq As String, dRules As cDataSet
    ' populate the customer master
    Set dSets = New cDataSets
    iDebug = 0
    With dSets
        .create
        Set ds = .init(wholeSheet(cCustMaster), , cDataMaster, , , True)
        ' create the parameter page deserialization data set
        Set dRules = .init(wholeSheet(cParamSheet), , cParamRules, True, cParamRules)
        ' create the parameter page data set
        Set dFields = .init(wholeSheet(cParamSheet), , cParamFields, True, cParamFields)
        With dFields
        ' check that the required fields are present in the input data
          If Not ds.HeadingRow.Validate(True, _
                .Cell(cFieldID, cFieldValue).toString, _
                .Cell(cFieldAddress, cFieldValue).toString) Then
            Exit Sub
          End If
        End With
    End With
    ' now we have the data file and know that it contains an ID column and an address column
    ' now geocode the addresses - one by one (the final version will do it as a batch)
    Set cb = New cBrowser
    For Each dr In ds.Rows
        sReq = cApiUrl & URLEncode( _
            dr.Cell(dFields.Cell(cFieldAddress, cFieldValue).toString).toString) & _
            "&sensor=false"
        ' get mapping result
        sWire = cb.httpGET(sReq)
        Set jo = New cJobject
        With jo.deSerialize(sWire)
            If .isValid Then
            ' now we have a cjobject of every field in the response
                If .Child("status").toString = "OK" Then
                    ' all is good go and find necessary fields
                    findSuitableJob .Child("results"), dr, Range("debug!a2")
                Else
                    MsgBox ("Unable to geomap - status " & .Child("status").toString & ":url" & sReq)
                End If
            Else
                MsgBox ("Badly formed jSon response received to " & sReq)
            End If
        End With
        Set jo = Nothing
    Next dr
    Set dSets = Nothing
    Set cb = Nothing
End Sub

Summary

For further information, look at Data Manipulation Classes.

You can also see the cDataSet Model here.

Most projects on this site use these classes so you can download any of them for examples. Please provide feedback, ask questions or otherwise participate on our forum.