2

I am working with a reports API from an Application which converts an HTML table into JSON using a method very similar to that shown in posts in Stack Overflow (example: HTML Table to JSON).

The JSON has an array of columns (for the NAMES of VALUES), then there is an array of rows which contain cells (for the VALUES).

I want to map this report to a canonical data model but it is horrible to work with. What I want to do is run some sort of script on the JSON which reverse what the original script put in place and turns it into an array that contains individual records, much like the rows of a CSV file.

Here's an example of a report I am referring to - horrible isn't it :)

My Question

Is there a way of turning this format of JSON (where it has an array for column names, an array for sections and inside an array of rows which relate to the column names), into a table of some sort?

{
"Header": {
    "Time": "2016-03-30T16:10:19-07:00",
    "ReportName": "GeneralLedger",
    "ReportBasis": "Accrual",
    "StartPeriod": "2016-01-01",
    "EndPeriod": "2016-03-31",
    "Currency": "GBP",
    "Option": [
        {
            "Name": "NoReportData",
            "Value": "false"
        }
    ]
},
"Columns": {
    "Column": [
        {
            "ColTitle": "Date",
            "ColType": "tx_date"
        },
        {
            "ColTitle": "Transaction Type",
            "ColType": "txn_type"
        },
        {
            "ColTitle": "No.",
            "ColType": "doc_num"
        },
        {
            "ColTitle": "Name",
            "ColType": "name"
        },
        {
            "ColTitle": "Memo/Description",
            "ColType": "memo"
        },
        {
            "ColTitle": "Split",
            "ColType": "split_acc"
        },
        {
            "ColTitle": "Amount",
            "ColType": "subt_nat_amount"
        },
        {
            "ColTitle": "Balance",
            "ColType": "rbal_nat_amount"
        }
    ]
},
"Rows": {
    "Row": [
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Current",
                        "id": "144"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Bill Payment (Cheque)",
                                "id": "181"
                            },
                            {
                                "value": "1"
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": "104478"
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "-600.0"
                            },
                            {
                                "value": "-600.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill Payment (Cheque)",
                                "id": "184"
                            },
                            {
                                "value": "2"
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": "104478"
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "-120.0"
                            },
                            {
                                "value": "-720.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Deposit",
                                "id": "180"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": "Opening Balance"
                            },
                            {
                                "value": "Opening Balance Equity",
                                "id": "137"
                            },
                            {
                                "value": "2400.0"
                            },
                            {
                                "value": "1680.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-23"
                            },
                            {
                                "value": "Payment",
                                "id": "186"
                            },
                            {
                                "value": "345678"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "216.0"
                            },
                            {
                                "value": "1896.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Current"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "1896.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Debtors",
                        "id": "140"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "-Split-",
                                "id": ""
                            },
                            {
                                "value": "216.0"
                            },
                            {
                                "value": "216.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "-Split-",
                                "id": ""
                            },
                            {
                                "value": "108.0"
                            },
                            {
                                "value": "324.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-23"
                            },
                            {
                                "value": "Payment",
                                "id": "186"
                            },
                            {
                                "value": "345678"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Current",
                                "id": "144"
                            },
                            {
                                "value": "-216.0"
                            },
                            {
                                "value": "108.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Debtors"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "108.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Stock Asset",
                        "id": "136"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-01"
                            },
                            {
                                "value": "Stock Starting Value",
                                "id": "173"
                            },
                            {
                                "value": "START"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": "Round Neck T Shirt - Opening stock and value"
                            },
                            {
                                "value": "Opening Balance Equity",
                                "id": "137"
                            },
                            {
                                "value": "0.0"
                            },
                            {
                                "value": "0.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "-12.0"
                            },
                            {
                                "value": "-12.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "-24.0"
                            },
                            {
                                "value": "-36.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Stock Qty Adjust",
                                "id": "177"
                            },
                            {
                                "value": "2"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Shrinkage",
                                "id": "141"
                            },
                            {
                                "value": "0.0"
                            },
                            {
                                "value": "-36.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Stock Qty Adjust",
                                "id": "182"
                            },
                            {
                                "value": "3"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Shrinkage",
                                "id": "141"
                            },
                            {
                                "value": "-36.0"
                            },
                            {
                                "value": "-72.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Stock Qty Adjust",
                                "id": "182"
                            },
                            {
                                "value": "3"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Shrinkage",
                                "id": "141"
                            },
                            {
                                "value": "-564.0"
                            },
                            {
                                "value": "-636.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Stock Qty Adjust",
                                "id": "177"
                            },
                            {
                                "value": "2"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Shrinkage",
                                "id": "141"
                            },
                            {
                                "value": "600.0"
                            },
                            {
                                "value": "-36.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Stock Qty Adjust",
                                "id": "182"
                            },
                            {
                                "value": "3"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Shrinkage",
                                "id": "141"
                            },
                            {
                                "value": "0.0"
                            },
                            {
                                "value": "-36.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "178"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "600.0"
                            },
                            {
                                "value": "564.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Stock Asset"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "564.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Creditors",
                        "id": "138"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Bill Payment (Cheque)",
                                "id": "181"
                            },
                            {
                                "value": "1"
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Current",
                                "id": "144"
                            },
                            {
                                "value": "-600.0"
                            },
                            {
                                "value": "-600.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill Payment (Cheque)",
                                "id": "184"
                            },
                            {
                                "value": "2"
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Current",
                                "id": "144"
                            },
                            {
                                "value": "-120.0"
                            },
                            {
                                "value": "-720.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "185"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "British Power",
                                "id": "72"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Utilities",
                                "id": "129"
                            },
                            {
                                "value": "192.15"
                            },
                            {
                                "value": "-527.85"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "183"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Printing Ink Supplies",
                                "id": "71"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "-Split-",
                                "id": ""
                            },
                            {
                                "value": "1920.0"
                            },
                            {
                                "value": "1392.15"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "178"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Stock Asset",
                                "id": "136"
                            },
                            {
                                "value": "720.0"
                            },
                            {
                                "value": "2112.15"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Creditors"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "2112.15"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "VAT Control",
                        "id": "142"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "36.0"
                            },
                            {
                                "value": "36.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "18.0"
                            },
                            {
                                "value": "54.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "185"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "British Power",
                                "id": "72"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "-9.15"
                            },
                            {
                                "value": "44.85"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "183"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Printing Ink Supplies",
                                "id": "71"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "-320.0"
                            },
                            {
                                "value": "-275.15"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Bill",
                                "id": "178"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Teddy's T Shirt Supplier",
                                "id": "70"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Creditors",
                                "id": "138"
                            },
                            {
                                "value": "-120.0"
                            },
                            {
                                "value": "-395.15"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for VAT Control"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "-395.15"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Opening Balance Equity",
                        "id": "137"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-01"
                            },
                            {
                                "value": "Stock Starting Value",
                                "id": "173"
                            },
                            {
                                "value": "START"
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": "Round Neck T Shirt - Opening stock and value"
                            },
                            {
                                "value": "Stock Asset",
                                "id": "136"
                            },
                            {
                                "value": "0.0"
                            },
                            {
                                "value": "0.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-17"
                            },
                            {
                                "value": "Deposit",
                                "id": "180"
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "",
                                "id": ""
                            },
                            {
                                "value": ""
                            },
                            {
                                "value": "Current",
                                "id": "144"
                            },
                            {
                                "value": "2400.0"
                            },
                            {
                                "value": "2400.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Opening Balance Equity"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "2400.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Sales of Product Income",
                        "id": "133"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "60.0"
                            },
                            {
                                "value": "60.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "120.0"
                            },
                            {
                                "value": "180.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Sales of Product Income"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "180.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Services",
                        "id": "131"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": "Print on Pocket"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "60.0"
                            },
                            {
                                "value": "60.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": "Print on Pocket"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "30.0"
                            },
                            {
                                "value": "90.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Services"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "90.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Cost of sales",
                        "id": "134"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "176"
                            },
                            {
                                "value": "1014"
                            },
                            {
                                "value": "Maxamillion Enterprises",
                                "id": "68"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "24.0"
                            },
                            {
                                "value": "24.0"
                            }
                        ],
                        "type": "Data"
                    },
                    {
                        "ColData": [
                            {
                                "value": "2016-03-16"
                            },
                            {
                                "value": "Invoice",
                                "id": "179"
                            },
                            {
                                "value": "1015"
                            },
                            {
                                "value": "Hope Reality Limited",
                                "id": "69"
                            },
                            {
                                "value": "Round Neck T Shirt"
                            },
                            {
                                "value": "Debtors",
                                "id": "140"
                            },
                            {
                                "value": "12.0"
                            },
                            {
                                "value": "36.0"
                            }
                        ],
                        "type": "Data"
                    }
                ]
            },
            "Summary": {
                "ColData": [
                    {
                        "value": "Total for Cost of sales"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": "36.0"
                    },
                    {
                        "value": ""
                    }
                ]
            },
            "type": "Section"
        },
        {
            "Header": {
                "ColData": [
                    {
                        "value": "Stock Shrinkage",
                        "id": "141"
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    },
                    {
                        "value": ""
                    }
                ]
            }

etc. I had to cut this JSON short, limited to 30000 characters.

VLAZ
  • 26,331
  • 9
  • 49
  • 67
Matt Lightbourn
  • 597
  • 3
  • 20
  • It's quite nice of you to share with us what you'd like to do. Did you have a specific question you'd like to share with us as well? You've not asked a question at all. – Ken White Mar 31 '16 at 02:13
  • apologies for not being clear, I want to know if there is a way of turning this format of JSON where it has an array for column names, an array for sections and inside an array of rows which relate to the column names, into a table of some description? – Matt Lightbourn Mar 31 '16 at 02:15
  • So [edit] your post and add that information where it can be seen. Fair warning: *I want to know if there's a way* is pretty broad in scope, and it may not be well received. – Ken White Mar 31 '16 at 02:17
  • fair point, thanks for that - I haven't been here for a while and have forgotten to write my questions methodically. I'll give it some thought and update, thanks, Matt – Matt Lightbourn Mar 31 '16 at 02:18
  • 3
    Congratulations! According [this query on Stack Exchange Data Explorer](http://data.stackexchange.com/stackoverflow/query/44363), currently you is the owner of the longest question in the whole StackOverflow site! :D – Bruno Peres Oct 20 '17 at 16:40

1 Answers1

1

What have you tried so far? This seems pretty straightforward. To get the rows into an array format you would do something like the following:

var data = {
  "Header": {
    "Time": "2016-03-30T16:10:19-07:00",
    "ReportName": "GeneralLedger",
    "ReportBasis": "Accrual",
    "StartPeriod": "2016-01-01",
    "EndPeriod": "2016-03-31",
    "Currency": "GBP",
    "Option": [{
      "Name": "NoReportData",
      "Value": "false"
    }]
  },
  "Columns": {
    "Column": [{
      "ColTitle": "Date",
      "ColType": "tx_date"
    }, {
      "ColTitle": "Transaction Type",
      "ColType": "txn_type"
    }, {
      "ColTitle": "No.",
      "ColType": "doc_num"
    }, {
      "ColTitle": "Name",
      "ColType": "name"
    }, {
      "ColTitle": "Memo/Description",
      "ColType": "memo"
    }, {
      "ColTitle": "Split",
      "ColType": "split_acc"
    }, {
      "ColTitle": "Amount",
      "ColType": "subt_nat_amount"
    }, {
      "ColTitle": "Balance",
      "ColType": "rbal_nat_amount"
    }]
  },
  "Rows": {
    "Row": [{
      "Header": {
        "ColData": [{
          "value": "Current",
          "id": "144"
        }, {
          "value": ""
        }, {
          "value": ""
        }, {
          "value": ""
        }, {
          "value": ""
        }, {
          "value": ""
        }, {
          "value": ""
        }, {
          "value": ""
        }]
      },
      "Rows": {
        "Row": [{
          "ColData": [{
            "value": "2016-03-16"
          }, {
            "value": "Bill Payment (Cheque)",
            "id": "181"
          }, {
            "value": "1"
          }, {
            "value": "Teddy's T Shirt Supplier",
            "id": "70"
          }, {
            "value": "104478"
          }, {
            "value": "Creditors",
            "id": "138"
          }, {
            "value": "-600.0"
          }, {
            "value": "-600.0"
          }],
          "type": "Data"
        }, {
          "ColData": [{
            "value": "2016-03-17"
          }, {
            "value": "Bill Payment (Cheque)",
            "id": "184"
          }, {
            "value": "2"
          }, {
            "value": "Teddy's T Shirt Supplier",
            "id": "70"
          }, {
            "value": "104478"
          }, {
            "value": "Creditors",
            "id": "138"
          }, {
            "value": "-120.0"
          }, {
            "value": "-720.0"
          }],
          "type": "Data"
        }, {
          "ColData": [{
            "value": "2016-03-17"
          }, {
            "value": "Deposit",
            "id": "180"
          }, {
            "value": ""
          }, {
            "value": "",
            "id": ""
          }, {
            "value": "Opening Balance"
          }, {
            "value": "Opening Balance Equity",
            "id": "137"
          }, {
            "value": "2400.0"
          }, {
            "value": "1680.0"
          }],
          "type": "Data"
        }, {
          "ColData": [{
            "value": "2016-03-23"
          }, {
            "value": "Payment",
            "id": "186"
          }, {
            "value": "345678"
          }, {
            "value": "Maxamillion Enterprises",
            "id": "68"
          }, {
            "value": ""
          }, {
            "value": "Debtors",
            "id": "140"
          }, {
            "value": "216.0"
          }, {
            "value": "1896.0"
          }],
          "type": "Data"
        }]
      }
    }]
  }
};

function parse(data) {
  var rows = [],
    row, curRow, rowSegment;
  for (var i = 0; i < data.Rows.Row.length; ++i) {
    rowSegment = data.Rows.Row[i].Rows.Row;
    for (var j = 0; j < rowSegment.length; ++j) {
          row = [];
      curRow = rowSegment[j].ColData;
      for (var x = 0; x < curRow.length; ++x) {
        row.push(curRow[x].value);
      }
      rows.push(row);
    }
  }
  return rows;
}
var parsed = parse(data);
var rowEl, outEl = document.getElementById('html-out'),
  val;
for (var i = 0; i < parsed.length; ++i) {
  rowEl = document.createElement("div");
  rowEl.setAttribute("class", "row");
  rowEl.appendChild(document.createTextNode(parsed[i].join(', ')));
  outEl.appendChild(rowEl);
}
<div id="html-out"></div>

Also, you would probably want to add the columns as the first row but this looks like it would get you the CSV-type data you are going for.

Matt
  • 621
  • 1
  • 6
  • 24
  • thanks for this, I will check it out and get back to you - very much appreciated. Matt – Matt Lightbourn Mar 31 '16 at 03:37
  • just run the code snippet and that is awesome - that's a new function on this website I believe. Thanks a million – Matt Lightbourn Mar 31 '16 at 03:39
  • seriously, this is awesome, how would I run this on my machine as a utility? I take it it is a jquery but have never put something like this on my own page before. thanks, Matt – Matt Lightbourn Mar 31 '16 at 03:55
  • 1
    @MattLightbourn Oh as a utility? You could put it into an html page with the data or you could make a node.js app those would be two of the easier ways off the top of my head – Matt Mar 31 '16 at 04:01
  • @MattLightbourn It would also be simple to port the code to python as well which would give you an easy way to run the utility right from the command line – Matt Mar 31 '16 at 04:10