Groovy Script to convert deep CSV to JSON

Question

I have a groovy script that converts flat file CSV with Headers, Line items and Footer into a JSON. I am using a simple code as shown below but its not giving me the desired output. My expectation is that line items should come under each header and footer should be at the same level as the Header node and it should come at the end.

Please help me out in this regard.

GROOVY CODE:

def lines = '''
H   01  FATXXX  08012111    08012000    1549999 CAD 
I   1545093 01.01.074.HBNY.002              D           71.34                                                   
I   1545093 01.01.HDK.1240              C           71.34                                                   
F   2   71.34   71.34
'''.split('\n')


def json = [:]
def items = []

lines.each { line ->
    def parts = line.split('\t')
    def type = parts[0]

    if (type == 'H') {
        json['header'] = [
            'CompanyCode': parts[1],
            'JournalEntryType': parts[2],
            'PostingDate': parts[3],
            'JournalEntryDate': parts[4],
            'ReferenceDocNumber': parts[5],
            'TransCurr': parts[6],
            'DocText': parts[7]
        ]
    } else if (type == 'I') {
        items << [
            'ReferenceDocNumber': parts[1],
            'GL_Account': parts[2],
            'Cost_Center': parts[3],
            'Profit_Center': parts[4],
            'WBS_Element': parts[5],
            'D_C_Indicator': parts[6],
            'Item_Text': parts[7],
            'Assignment': parts[8],
            'AmountinTransactionCurrency': parts[9],
            'LocalCurrency': parts[10],
            'AmountinLocalCurrency': parts[11],
            'GroupCurrency': parts[12],
            'AmountinGroupCurrency': parts[13],
            'ParallelCurrency': parts[14],
            'AmountinParallelCurrency': parts[15],
            'TransactionType': parts[16],
            'HouseBank': parts[17],
            'BankAccountID': parts[18],
            'ValueDate': parts[19],
            'TradingPartner': parts[20],
            'IFF_VENDOR': parts[21],
            'IFF_CUSTOMER': parts[22]
        ]
    } else if (type == 'F') {
        json['footer'] = [
            'Total_Records': parts[1],
            'Total_Debit': parts[2],
            'Total_Credit': parts[3]
        ]
    }
}

json['items'] = items

def jsonString = JsonOutput.toJson(json)
println(jsonString)
}

EXPECTED OUTPUT:

{

  "GL_Data": {

    "Headers": [

      {

        "CompanyCode": 1,

        "JournalEntryType": "APPURREG",

        "PostingDate": 8302023,

        "JournalEntryDate": 8302023,

        "ReferenceDocNumber": 493890,

        "TransCurr": "CAD",

        "DocText": "",

        "Items": [

          {

            "ReferenceDocNumber": 493890,

            "GL_Account": "01.00.001.0011",

            "Cost_Center": "",

            "Profit_Center": "",

            "WBS_Element": "",

            "D_C_Indicator": "D",

            "Item_Text": "",

            "Assignment": "",

            "AmountinTransactionCurrency": 10,

            "LocalCurrency": "",

            "AmountinLocalCurrency": "",

            "GroupCurrency": "",

            "AmountinGroupCurrency": "",

            "ParallelCurrency": "",

            "HouseBank": "",

            "BankAccountID": "",

            "TradingPartner": "",

            "IFF_VENDOR": "",

            "IFF_CUSTOMER": ""

          },

          {

            "ReferenceDocNumber": 493890,

            "GL_Account": "01.01.000.0012",

            "Cost_Center": "",

            "Profit_Center": "",

            "WBS_Element": "",

            "D_C_Indicator": "C",

            "Item_Text": "",

            "Assignment": "",

            "AmountinTransactionCurrency": 10,

            "LocalCurrency": "",

            "AmountinLocalCurrency": "",

            "GroupCurrency": "",

            "AmountinGroupCurrency": "",

            "ParallelCurrency": "",

            "HouseBank": "",

            "BankAccountID": "",

            "TradingPartner": "",

            "IFF_VENDOR": "",

            "IFF_CUSTOMER": ""

          }

        ]

      },

      {

        "CompanyCode": 1,

        "JournalEntryType": "APPURREG",

        "PostingDate": 8302023,

        "JournalEntryDate": 8302023,

        "ReferenceDocNumber": 493890,

        "TransCurr": "CAD",

        "DocText": "",

        "Items": [

          {

            "ReferenceDocNumber": 493890,

            "GL_Account": "01.00.001.0001",

            "Cost_Center": "",

            "Profit_Center": "",

            "WBS_Element": "",

            "D_C_Indicator": "D",

            "Item_Text": "",

            "Assignment": "",

            "AmountinTransactionCurrency": 10,

            "LocalCurrency": "",

            "AmountinLocalCurrency": "",

            "GroupCurrency": "",

            "AmountinGroupCurrency": "",

            "ParallelCurrency": "",

            "HouseBank": "",

            "BankAccountID": "",

            "TradingPartner": "",

            "IFF_VENDOR": "",

            "IFF_CUSTOMER": ""

          },

          {

            "ReferenceDocNumber": 493890,

            "GL_Account": "01.01.000.0002",

            "Cost_Center": "",

            "Profit_Center": "",

            "WBS_Element": "",

            "D_C_Indicator": "C",

            "Item_Text": "",

            "Assignment": "",

            "AmountinTransactionCurrency": 10,

            "LocalCurrency": "",

            "AmountinLocalCurrency": "",

            "GroupCurrency": "",

            "AmountinGroupCurrency": "",

            "ParallelCurrency": "",

            "HouseBank": "",

            "BankAccountID": "",

            "TradingPartner": "",

            "IFF_VENDOR": "",

            "IFF_CUSTOMER": ""

          }

        ]

      }

    ]

  }

}

Regards, Arkesh

Answer 1

I think I understand what you want, but I might be off. Here is what I think you need to do. Change this line:

json['items'] = items

To this:

json['Headers'].Items = items

You'll also want to match up the casing in your code with the desired output (ie "headers" -> "Headers" and "footer" -> "Footer") to get what you're after.

Answer 2

If you have a multi-grouped CSV, you should be using a list instead of the map to collect your entries:

import groovy.json.*

def lines = '''\
H 01 FATREG 08012023 08012023 1545093 CAD 
I 1545093 01.01.074.7420.002 D 71.34 
I 1545093 01.01.074.1240 C 71.34 
H 01 FATREG 08012023 08012023 1545094 CAD 
I 1545094 01.01.074.7420.002 D 71.34 
I 1545094 01.01.074.1240 C 71.34 
F 16659 3609886.19 3609886.19'''

def list = []
def currJson

lines.splitEachLine( /\s+/ ){ parts ->
    switch (parts[0]) {
        case 'H':
            if( currJson ) list << currJson
            currJson = [ 
                         header:[
                            CompanyCode: parts[1],
                            JournalEntryType: parts[2],
                            PostingDate: parts[3],
                            JournalEntryDate: parts[4],
                            ReferenceDocNumber: parts[5],
                            TransCurr: parts[6],
                            DocText: parts[7]
                         ], 
                         items:[] 
                      ]
            break
        case  'I':
            currJson.items << [
                'ReferenceDocNumber': parts[1],
                'GL_Account': parts[2],
                'Cost_Center': parts[3],
                'Profit_Center': parts[4],
                // cut for brevity
            ]
            break
        case 'F':
            currJson.footer = [
                'Total_Records': parts[1],
                'Total_Debit': parts[2],
                'Total_Credit': parts[3]
            ]
            break
    }
}
if( currJson ) list << currJson

def jsonString = JsonOutput.prettyPrint JsonOutput.toJson( list )

You will get the following ouptut, where the H-I-F elements are groupped into a single list entry:

[
    {
        "header": {
            "CompanyCode": "01",
            "JournalEntryType": "FATREG",
            "PostingDate": "08012023",
            "JournalEntryDate": "08012023",
            "ReferenceDocNumber": "1545093",
            "TransCurr": "CAD",
            "DocText": null
        },
        "items": [
            {
                "ReferenceDocNumber": "1545093",
                "GL_Account": "01.01.074.7420.002",
                "Cost_Center": "D",
                "Profit_Center": "71.34"
            },
            {
                "ReferenceDocNumber": "1545093",
                "GL_Account": "01.01.074.1240",
                "Cost_Center": "C",
                "Profit_Center": "71.34"
            }
        ]
    },
    {
        "header": {
            "CompanyCode": "01",
            "JournalEntryType": "FATREG",
            "PostingDate": "08012023",
            "JournalEntryDate": "08012023",
            "ReferenceDocNumber": "1545094",
            "TransCurr": "CAD",
            "DocText": null
        },
        "items": [
            {
                "ReferenceDocNumber": "1545094",
                "GL_Account": "01.01.074.7420.002",
                "Cost_Center": "D",
                "Profit_Center": "71.34"
            },
            {
                "ReferenceDocNumber": "1545094",
                "GL_Account": "01.01.074.1240",
                "Cost_Center": "C",
                "Profit_Center": "71.34"
            }
        ],
        "footer": {
            "Total_Records": "16659",
            "Total_Debit": "3609886.19",
            "Total_Credit": "3609886.19"
        }
    }
]