Question 247332 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Groovy Script to convert deep CSV to JSON

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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"
        }
    }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search