Groovy脚本将深度CSV转换为JSON

5fjcxozz  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(96)

我有一个groovy脚本,它可以将带有页眉、行项目和页脚的平面文件CSV转换为JSON。我使用一个简单的代码如下所示,但它没有给我想要的输出。我的期望是,行项目应该在每个页眉和页脚下,应该在同一级别的页眉节点,它应该在最后。
请在这方面帮助我。

密码:

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)
}

预期输出:

{

  "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": ""

          }

        ]

      }

    ]

  }

}

问候,Arkesh

y0u0uwnf

y0u0uwnf1#

我想我明白你想要什么,但我可能要走了。我觉得你应该这么做。更改此行:

json['items'] = items

对此:

json['Headers'].Items = items

您还需要将代码中的大小写与所需的输出相匹配(即“headers”->“Headers”和“footer”->“Footer”),以获得您想要的结果。

nue99wik

nue99wik2#

如果你有一个多分组的CSV,你应该使用list而不是map来收集你的条目:

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 )

您将得到以下输出,其中H-I-F元素被分组到单个列表条目中:

[
    {
        "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"
        }
    }
]

相关问题