Xbase++

QuickBooks - Parse the JSON of a Customer Balance Detail Report

See more CSV Examples

This example is to show how to parse the JSON of a particular Quickbooks report. The techniques shown here may help in parsing similar reports.

The JSON to be parsed is available at Sample Quickbooks Customer Balance Detail Report JSON

Xbase++

LOCAL nSuccess
LOCAL oHttp
LOCAL cJsonStr
LOCAL oJson
LOCAL oCsv
LOCAL nNumColumns
LOCAL i
LOCAL nRow
LOCAL nNumRows
LOCAL nCol

nSuccess := 0

//  This example assumes the Chilkat API to have been previously unlocked.
//  See Global Unlock Sample for sample code.

oHttp := CreateObject("Chilkat.Http")

//  Get the JSON we'll be parsing..
cJsonStr := oHttp:QuickGetStr("https://www.chilkatsoft.com/exampleData/qb_customer_balance_detail_report_2.json")
IF (oHttp:LastMethodSuccess != 1)
    ? oHttp:LastErrorText
    oHttp:destroy()
    RETURN
ENDIF

oJson := CreateObject("Chilkat.JsonObject")
oJson:Load(cJsonStr)

//  As an alternative to manually writing code, use this online tool to generate parsing code from sample JSON: 
//  Generate Parsing Code from JSON

//  Let's parse the JSON into a CSV, and then save to a CSV file.
oCsv := CreateObject("Chilkat.Csv")
oCsv:HasColumnNames := 1

//  Set the column names of the CSV.
nNumColumns := oJson:SizeOfArray("Columns.Column")
IF (nNumColumns < 0)
    ? "Unable to get column names"
    oHttp:destroy()
    oJson:destroy()
    oCsv:destroy()
    RETURN
ENDIF

i := 0
DO WHILE i < nNumColumns
    oJson:I := i
    oCsv:SetColumnName(i, oJson:StringOf("Columns.Column[i].ColTitle"))
    i := i + 1
ENDDO

//  Let's get the rows.
//  We'll ignore the Header and Summary, and just get the data.
nRow := 0
nNumRows := oJson:SizeOfArray("Rows.Row[0].Rows.Row")
IF (nNumRows < 0)
    ? "Unable to get data rows"
    oHttp:destroy()
    oJson:destroy()
    oCsv:destroy()
    RETURN
ENDIF

DO WHILE nRow < nNumRows
    oJson:I := nRow
    nNumColumns := oJson:SizeOfArray("Rows.Row[0].Rows.Row[i].ColData")
    nCol := 0
    DO WHILE nCol < nNumColumns
        oJson:J := nCol
        oCsv:SetCell(nRow, nCol, oJson:StringOf("Rows.Row[0].Rows.Row[i].ColData[j].value"))
        nCol := nCol + 1
    ENDDO
    nRow := nRow + 1
ENDDO

//  Show the CSV 
? oCsv:SaveToString()

//  Save to a CSV file
nSuccess := oCsv:SaveFile("qa_output/customerDetailReport.csv")

oHttp:destroy()
oJson:destroy()
oCsv:destroy()