AutoIt

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

AutoIt

Local $bSuccess = False

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

$oHttp = ObjCreate("Chilkat.Http")

; Get the JSON we'll be parsing..
Local $sJsonStr = $oHttp.QuickGetStr("https://www.chilkatsoft.com/exampleData/qb_customer_balance_detail_report_2.json")
If ($oHttp.LastMethodSuccess <> True) Then
    ConsoleWrite($oHttp.LastErrorText & @CRLF)
    Exit
EndIf

$oJson = ObjCreate("Chilkat.JsonObject")
$oJson.Load($sJsonStr)

; 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 = ObjCreate("Chilkat.Csv")
$oCsv.HasColumnNames = True

; Set the column names of the CSV.
Local $iNumColumns = $oJson.SizeOfArray("Columns.Column")
If ($iNumColumns < 0) Then
    ConsoleWrite("Unable to get column names" & @CRLF)
    Exit
EndIf

Local $i = 0
While $i < $iNumColumns
    $oJson.I = $i
    $oCsv.SetColumnName($i,$oJson.StringOf("Columns.Column[i].ColTitle"))
    $i = $i + 1
Wend

; Let's get the rows.
; We'll ignore the Header and Summary, and just get the data.
Local $iRow = 0
Local $iNumRows = $oJson.SizeOfArray("Rows.Row[0].Rows.Row")
If ($iNumRows < 0) Then
    ConsoleWrite("Unable to get data rows" & @CRLF)
    Exit
EndIf

While $iRow < $iNumRows
    $oJson.I = $iRow
    $iNumColumns = $oJson.SizeOfArray("Rows.Row[0].Rows.Row[i].ColData")
Local $iCol = 0
    While $iCol < $iNumColumns
        $oJson.J = $iCol
        $oCsv.SetCell($iRow,$iCol,$oJson.StringOf("Rows.Row[0].Rows.Row[i].ColData[j].value"))
        $iCol = $iCol + 1
    Wend
    $iRow = $iRow + 1
Wend

; Show the CSV 
ConsoleWrite($oCsv.SaveToString() & @CRLF)

; Save to a CSV file
$bSuccess = $oCsv.SaveFile("qa_output/customerDetailReport.csv")