DataFlex

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

DataFlex

Use ChilkatAx-win32.pkg

Procedure Test
    Boolean iSuccess
    Handle hoHttp
    String sJsonStr
    Handle hoJson
    Handle hoCsv
    Integer iNumColumns
    Integer i
    Integer iRow
    Integer iNumRows
    Integer iCol
    String sTemp1
    Boolean bTemp1

    Move False To iSuccess

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

    Get Create (RefClass(cComChilkatHttp)) To hoHttp
    If (Not(IsComObjectCreated(hoHttp))) Begin
        Send CreateComObject of hoHttp
    End

    // Get the JSON we'll be parsing..
    Get ComQuickGetStr Of hoHttp "https://www.chilkatsoft.com/exampleData/qb_customer_balance_detail_report_2.json" To sJsonStr
    Get ComLastMethodSuccess Of hoHttp To bTemp1
    If (bTemp1 <> True) Begin
        Get ComLastErrorText Of hoHttp To sTemp1
        Showln sTemp1
        Procedure_Return
    End

    Get Create (RefClass(cComChilkatJsonObject)) To hoJson
    If (Not(IsComObjectCreated(hoJson))) Begin
        Send CreateComObject of hoJson
    End
    Get ComLoad Of hoJson sJsonStr To iSuccess

    // 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.
    Get Create (RefClass(cComChilkatCsv)) To hoCsv
    If (Not(IsComObjectCreated(hoCsv))) Begin
        Send CreateComObject of hoCsv
    End
    Set ComHasColumnNames Of hoCsv To True

    // Set the column names of the CSV.
    Get ComSizeOfArray Of hoJson "Columns.Column" To iNumColumns
    If (iNumColumns < 0) Begin
        Showln "Unable to get column names"
        Procedure_Return
    End

    Move 0 To i
    While (i < iNumColumns)
        Set ComI Of hoJson To i
        Get ComStringOf Of hoJson "Columns.Column[i].ColTitle" To sTemp1
        Get ComSetColumnName Of hoCsv i sTemp1 To iSuccess
        Move (i + 1) To i
    Loop

    // Let's get the rows.
    // We'll ignore the Header and Summary, and just get the data.
    Move 0 To iRow
    Get ComSizeOfArray Of hoJson "Rows.Row[0].Rows.Row" To iNumRows
    If (iNumRows < 0) Begin
        Showln "Unable to get data rows"
        Procedure_Return
    End

    While (iRow < iNumRows)
        Set ComI Of hoJson To iRow
        Get ComSizeOfArray Of hoJson "Rows.Row[0].Rows.Row[i].ColData" To iNumColumns
        Move 0 To iCol
        While (iCol < iNumColumns)
            Set ComJ Of hoJson To iCol
            Get ComStringOf Of hoJson "Rows.Row[0].Rows.Row[i].ColData[j].value" To sTemp1
            Get ComSetCell Of hoCsv iRow iCol sTemp1 To iSuccess
            Move (iCol + 1) To iCol
        Loop

        Move (iRow + 1) To iRow
    Loop

    // Show the CSV 
    Get ComSaveToString Of hoCsv To sTemp1
    Showln sTemp1

    // Save to a CSV file
    Get ComSaveFile Of hoCsv "qa_output/customerDetailReport.csv" To iSuccess


End_Procedure