PowerShell

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

PowerShell

Add-Type -Path "C:\chilkat\ChilkatDotNet47-x64\ChilkatDotNet47.dll"

$success = $false

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

$http = New-Object Chilkat.Http

# Get the JSON we'll be parsing..
$jsonStr = $http.QuickGetStr("https://www.chilkatsoft.com/exampleData/qb_customer_balance_detail_report_2.json")
if ($http.LastMethodSuccess -ne $true) {
    $($http.LastErrorText)
    exit
}

$json = New-Object Chilkat.JsonObject
$json.Load($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.
$csv = New-Object Chilkat.Csv
$csv.HasColumnNames = $true

# Set the column names of the CSV.
$numColumns = $json.SizeOfArray("Columns.Column")
if ($numColumns -lt 0) {
    $("Unable to get column names")
    exit
}

$i = 0
while ($i -lt $numColumns) {
    $json.I = $i
    $csv.SetColumnName($i,$json.StringOf("Columns.Column[i].ColTitle"))
    $i = $i + 1
}

# Let's get the rows.
# We'll ignore the Header and Summary, and just get the data.
$row = 0
$numRows = $json.SizeOfArray("Rows.Row[0].Rows.Row")
if ($numRows -lt 0) {
    $("Unable to get data rows")
    exit
}

while ($row -lt $numRows) {
    $json.I = $row
    $numColumns = $json.SizeOfArray("Rows.Row[0].Rows.Row[i].ColData")
    $col = 0
    while ($col -lt $numColumns) {
        $json.J = $col
        $csv.SetCell($row,$col,$json.StringOf("Rows.Row[0].Rows.Row[i].ColData[j].value"))
        $col = $col + 1
    }

    $row = $row + 1
}

# Show the CSV 
$($csv.SaveToString())

# Save to a CSV file
$success = $csv.SaveFile("qa_output/customerDetailReport.csv")