Parse.comHOW TO USE A CLOUD BASED NOSQL DATABASE WITH EXCEL/VBA

EXCEL LIBERATION

What is parse.com? A noSQL database

SDK for multiple platforms, including IOS and Android

Cloud based script execution

Built in analytics and dashboards

Role based security

Free for most of us

Easy to use and to get started with

Best for smaller datasets

Read more at parse.com

Why use parse.com with VBA? Allows Excel to easily share data with other platforms and across workbooks

Is easier to get started with than many other noSQL databases

Avoids the architectural and framework complications of sql server and other traditional data base backends

There is a restAPI that’s pretty easy to implement a VBA wrapper class for

Because we can

Authentication using VBA API

Encrypt parse.com restAPI and

application ID Keys

Store in windows registry

Once only per user/PC

Get from windows registry

Decrypt restAPI and application

ID keys

Access Parse.com

Subsequent accesses from any workbook

Avoids the problem of needing keys in every workbook

Code for Authentication First time to register and encrypt credentials on this PC for this user

Private Sub firstTimeParseCom() Dim parseCom As cParseCom Set parseCom = New cParseCom With parseCom.init(“a parse class name", , , "your app id", _ "your restapikey") .tearDown End WithEnd Sub

Thereafter from any workbook

Set parseCom = getParsed(“a parse class name”)

A Parse Class is like a Table

Code for a QueryUses cJobect for javaScript like object emulation and JSON conversion

With getParsed("VBAParseData") With .getObjectsByQuery(JSONParse("{'customerid':1}"))) If .isOk Then Debug.Print "all is ok", .jObject.stringify(True) Else Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .tearDown End With

Or as a one liner

Debug.Print _ getParsed("VBAParseData").getObjectsByQuery(JSONParse("{'customerid':1}")).jObject.stringify(True)

All methods are chainable

Queries are by example. Default is to get all objects in the class

Get by objectIDUses cJobect for javaScript like object emulation and JSON conversion

With getParsed("VBAParseCustomers") .getObjectById ("SmnyjZKs9m") If .isOk Then Debug.Print .jObject.stringify(True) Else Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If .tearDown End With

Or as a one liner

Debug.Print _getParsed("VBAParseCustomers").getObjectById("SmnyjZKs9m").jObject.stringify(True))

All methods are chainable

Each parse object (like a row) gets assigned a unique ID

JSON is returned from every operationUses cJobect for javaScript like object emulation and JSON conversion

{ "address":"584-5478 Et Road", "city":"Hastings", "company":"Eu Accumsan Sed Inc.", "coordinates":"38.2264, 75.04849", "country":"Comoros", "customerid":100, "email":"tincidunt.nibh@Curabitur.net", "name":"Contreras", "region":"NI", "zip":"12396", "createdAt":"2013-11-26T14:36:40.517Z", "updatedAt":"2013-11-26T14:36:40.517Z", "objectId":"SmnyjZKs9m" }

Results are in the .jObect property of cParseCom

Deleting objectsUses cJobect for javaScript like object emulation and JSON conversion

getParsed(‘a parse class’).batch.parseCom.deleteObjects()

Or just some

getParsed(‘aclass’).batch.parseCom.deleteObjects(JSONParse(“{‘customerID’:1}”))

Delete operations can be ‘batched’

.deleteObjects will delete all objects (rows) that match its query.

Creating objectsUses cJobect for javaScript like object emulation and JSON conversion

getParsed(“aclass”).batch. parseCom.createObject(job)

Delete first, if you don’t want a new object to be created

getParsed(“aclass”). getObjectsByQuery(job).batch.delete().createObject(job)

Create operations can be ‘batched’

.createObjects will create a new class if it doesn’t exist

Updating objectsUses cJobect for javaScript like object emulation and JSON conversion

With getParsed("VBAParseData").batch With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}")) If .isOk Then Debug.Print "all is good", .jObject.stringify Else Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text End If End With .flush.tearDown End With

Or as a one liner

getParsed(“aclass”).batch.updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))

Update operations can be ‘batched’

.createObjects will create a new class if it doesn’t exist

Counting objects in a classUses cJobect for javaScript like object emulation and JSON conversion

Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))

Or total in class

Debug.Print getParsed("VBAParseData").count()

.count() will return the total number of records in the class or that match a query if one is given

Copying a sheet to a parse classUses cJobect for javaScript like object emulation and JSON conversion and cDataSet for Sheet abstraction

' copy two sheets to parse.com populateFromSheet "VBAParseCustomers" populateFromSheet "VBAParseDa

Code for populateFromSheet (minus error handling)

Set parseCom = getParsed(sheetName).batch.parseCom.deleteObjects()Set dset = New cDataSet With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True) For Each job In .children debug.assert parseCom.createObject(job).isOk Next job .tearDown End With debug.assert parseCom.flush.isOk parseCom.tearDown

cDataSet knows how to convert Excel worksheet data to a cJobject

Getting started Get the cDataSet.xlsm workbook from Excel Liberation Downloads

Register with parse.com, create an application and get an applicationID and a restAPI key

Read about how all this works and learn how to do more complex operations at Excel Liberation

See these slideshare primers for cDataSet and cJobject for getting started with JSON and Excel

For help and more information visit me on GooglePlus, join our forum, follow the blog or follow me on twitter .