Tcl

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

Tcl

load ./chilkat.dll

set success 0

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

set http [new_CkHttp]

# Get the JSON we'll be parsing..
set jsonStr [CkHttp_quickGetStr $http "https://www.chilkatsoft.com/exampleData/qb_customer_balance_detail_report_2.json"]
if {[CkHttp_get_LastMethodSuccess $http] != 1} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkHttp $http
    exit
}

set json [new_CkJsonObject]

CkJsonObject_Load $json $jsonStr

# 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.
set csv [new_CkCsv]

CkCsv_put_HasColumnNames $csv 1

# Set the column names of the CSV.
set numColumns [CkJsonObject_SizeOfArray $json "Columns.Column"]
if {$numColumns < 0} then {
    puts "Unable to get column names"
    delete_CkHttp $http
    delete_CkJsonObject $json
    delete_CkCsv $csv
    exit
}

set i 0
while {$i < $numColumns} {
    CkJsonObject_put_I $json $i
    CkCsv_SetColumnName $csv $i [CkJsonObject_stringOf $json "Columns.Column[i].ColTitle"]
    set i [expr $i + 1]
}

# Let's get the rows.
# We'll ignore the Header and Summary, and just get the data.
set row 0
set numRows [CkJsonObject_SizeOfArray $json "Rows.Row[0].Rows.Row"]
if {$numRows < 0} then {
    puts "Unable to get data rows"
    delete_CkHttp $http
    delete_CkJsonObject $json
    delete_CkCsv $csv
    exit
}

while {$row < $numRows} {
    CkJsonObject_put_I $json $row
    set numColumns [CkJsonObject_SizeOfArray $json "Rows.Row[0].Rows.Row[i].ColData"]
    set col 0
    while {$col < $numColumns} {
        CkJsonObject_put_J $json $col
        CkCsv_SetCell $csv $row $col [CkJsonObject_stringOf $json "Rows.Row[0].Rows.Row[i].ColData[j].value"]
        set col [expr $col + 1]
    }
    set row [expr $row + 1]
}

# Show the CSV 
puts [CkCsv_saveToString $csv]

# Save to a CSV file
set success [CkCsv_SaveFile $csv "qa_output/customerDetailReport.csv"]

delete_CkHttp $http
delete_CkJsonObject $json
delete_CkCsv $csv