How to convert the csv file with multiple line headers and line items to xml in Sap Cpi ?

[subramanaym]

I am using the below csv data to convert it to xml and i am using the groovy script  i have attached the code i am using along with output i am receiving. 

input csv data : i just provided few records consider having bulk data 

RecordType,RecordID,InvoiceIssueDate,InvoiceID,AccountCategory,TermsOfPayment,HandlingCharge,AdditionalCharge,ShippingCost,TaxOnHeader,TotalAmountDue,TotalGross,CurrencySymbol,SupplierCode,SupplierFirm,SupplierReference,AddressCodeForPayment,TypeOfDocument,InitialInvoiceNumber,InitialInvoiceDate,DeliveryAddress,ModeOfPayment,PaymentTermsNotes

RecordType,InvoiceReferenceID,LineItemID,ItemNumber,ProductDescription,ProductCategory,PurchaseOrderID,OrderItemNumber,UnitPrice,ItemQuantity,UnitMeasurement,LineTotalAmount,ShippingAmountDistributed,MiscellaneousAmountDistributed,HandlingAmountDistributed,AccountingSegment1,AccountingSegment2,AccountingSegment3,AccountingSegment4,FinancialAccountName,FinancialAccountCode

RecordType,InvoiceReferenceID,LineItemReferenceID,SplitIdentifier,SequenceOfAllocation,PercentageAllocated,AllocatedAmount,DistributedShippingCharge,DistributedHandlingCharge,DistributedMiscCharge,FinancialSegment1,FinancialSegment2,FinancialSegment3,AccountCodeReference,FinancialSegment4,AccountHolderName

Header,202,2024-10-10T09:00:00Z,INVTEST1010-20,Premium Account,NET60,15,25,35,12,5000,5000,USD,40,GLOBAL ENTERPRISES,TEST0015,TEST0015,Remit Address
3,78901,2024-10-10,Delivery Address 3,credit_card,#<Updated Payment Terms>
Line,202,400,1,Updated Product A,New Category,PO-115,5,3000,,15000,,,,,,SegmentA,SegmentB,SegmentC,SegmentD,AccountNameA,AccountCodeA
Line Split,202,400,1,1,50,2500,,5000,,25000,25000,25000,25000-25000-25000-0005,10,AccountHolderA

Header,203,2024-11-05T11:00:00Z,INVTEST1105-25,Standard Account,NET90,20,30,40,15,5500,5500,GBP,41,TECHNOLOGY INNOVATIONS,TEST0016,TEST0016,Remit Address 4,89012,2024-11-05,Delivery Address 4,bank_transfer,#<Revised Payment Terms>
Line,203,401,2,Updated Product B,New Category,PO-116,6,3500,,21000,,,,,,SegmentX,SegmentY,SegmentZ,SegmentW,AccountNameB,AccountCodeB
Line Split,203,401,2,1,40,2800,,5600,,28000,28000,28000,28000-28000-28000-0006,10,AccountHolderB

Groovy code :

import com.sap.gateway.ip.core.customdev.util.Message
import groovy.xml.MarkupBuilder
import java.io.StringWriter

def Message processData(Message message) {
// Log the beginning of the script execution
println "Script execution started."

// Retrieve CSV data from message body
def csvData = message.getBody(String)

// Log the retrieved CSV data
println "Received CSV data: ${csvData}"

// Check if CSV data is null or empty
if (!csvData || csvData.trim().isEmpty()) {
throw new Exception("CSV data is empty or null.")
}

// Split CSV data into lines and filter out empty lines
def csvLines = csvData.split('\n').findAll { it.trim() }

// Initialize containers for data
def headers = [:]
def lines = []
def lineSplits = []

// Process each line
csvLines.each { csvLine ->
def values = csvLine.split(',').collect { it.trim() }

if (values.size() < 1) {
println "Skipping line with insufficient values: ${csvLine}"
return
}

def type = values[0]?.trim()

switch (type) {
case 'Header':
if (values.size() < 23) {
println "Skipping header with insufficient values: ${csvLine}"
return
}
def headerId = values[1]?.trim()
headers[headerId] = values
break
case 'Line':
if (values.size() < 21) {
println "Skipping line with insufficient values: ${csvLine}"
return
}
lines << values
break
case 'Line Split':
if (values.size() < 16) {
println "Skipping line split with insufficient values: ${csvLine}"
return
}
lineSplits << values
break
default:
println "Unknown type: ${type}"
}
}

// Prepare XML output
def xmlBuilder = new StringWriter()
def xml = new MarkupBuilder(xmlBuilder)

xml.root {
headers.each { headerId, header ->
Header {
recordType(header[0]?.trim())
recordID(header[1]?.trim())
invoiceIssueDate(header[2]?.trim())
invoiceID(header[3]?.trim())
accountCategory(header[4]?.trim())
termsOfPayment(header[5]?.trim())
handlingCharge(header[6]?.trim())
additionalCharge(header[7]?.trim())
shippingCost(header[8]?.trim())
taxOnHeader(header[9]?.trim())
totalAmountDue(header[10]?.trim())
totalGross(header[11]?.trim())
currencySymbol(header[12]?.trim())
supplierCode(header[13]?.trim())
supplierFirm(header[14]?.trim())
supplierReference(header[15]?.trim())
addressCodeForPayment(header[16]?.trim())
typeOfDocument(header[17]?.trim())
initialInvoiceNumber(header[18]?.trim())
initialInvoiceDate(header[19]?.trim())
deliveryAddress(header[20]?.trim())
modeOfPayment(header[21]?.trim())
paymentTermsNotes(header[22]?.trim())

// Append associated Lines
Lines {
lines.findAll { line ->
line[1]?.trim() == headerId // Match line invoice-id with header id
}.each { line ->
Line {
recordType(line[0]?.trim())
invoiceReferenceID(line[1]?.trim())
lineItemID(line[2]?.trim())
itemNumber(line[3]?.trim())
productDescription(line[4]?.trim())
productCategory(line[5]?.trim())
purchaseOrderID(line[6]?.trim())
orderItemNumber(line[7]?.trim())
unitPrice(line[8]?.trim())
itemQuantity(line[9]?.trim())
unitMeasurement(line[10]?.trim())
lineTotalAmount(line[11]?.trim())
shippingAmountDistributed(line[12]?.trim())
miscellaneousAmountDistributed(line[13]?.trim())
handlingAmountDistributed(line[14]?.trim())
accountingSegment1(line[15]?.trim())
accountingSegment2(line[16]?.trim())
accountingSegment3(line[17]?.trim())
accountingSegment4(line[18]?.trim())
financialAccountName(line[19]?.trim())
financialAccountCode(line[20]?.trim())
}
}
}

// Append associated LineSplits
LineSplits {
lineSplits.findAll { split ->
split[1]?.trim() == headerId // Match lineId with header id
}.each { split ->
LineSplit {
recordType(split[0]?.trim())
invoiceReferenceID(split[1]?.trim())
lineItemReferenceID(split[2]?.trim())
splitIdentifier(split[3]?.trim())
sequenceOfAllocation(split[4]?.trim())
percentageAllocated(split[5]?.trim())
allocatedAmount(split[6]?.trim())
distributedShippingCharge(split[7]?.trim())
distributedHandlingCharge(split[8]?.trim())
distributedMiscCharge(split[9]?.trim())
financialSegment1(split[10]?.trim())
financialSegment2(split[11]?.trim())
financialSegment3(split[12]?.trim())
accountCodeReference(split[13]?.trim())
financialSegment4(split[14]?.trim())
accountHolderName(split[15]?.trim())
}
}
}
}
}
}

// Convert XML to string and set as the message body
def xmlString = xmlBuilder.toString()
println "Generated XML:\n${xmlString}"
message.setBody(xmlString)

return message
}

output :

<root>

<Header>
<recordType>Header</recordType>
<recordID>202</recordID>
<invoiceIssueDate>2024-10-10T09:00:00Z</invoiceIssueDate>
<invoiceID>INVTEST1010-20</invoiceID>
<accountCategory>Premium Account</accountCategory>
<termsOfPayment>NET60</termsOfPayment>
<handlingCharge>15</handlingCharge>
<additionalCharge>25</additionalCharge>
<shippingCost>35</shippingCost>
<taxOnHeader>12</taxOnHeader>
<totalAmountDue>5000</totalAmountDue>
<totalGross>5000</totalGross>
<currencySymbol>USD</currencySymbol>
<supplierCode>40</supplierCode>
<supplierFirm>GLOBAL ENTERPRISES</supplierFirm>
<supplierReference>TEST0015</supplierReference>
<addressCodeForPayment>TEST0015</addressCodeForPayment>
<typeOfDocument>Remit Address 3</typeOfDocument>
<initialInvoiceNumber>78901</initialInvoiceNumber>
<initialInvoiceDate>2024-10-10</initialInvoiceDate>
<deliveryAddress>Delivery Address 3</deliveryAddress>
<modeOfPayment>credit_card</modeOfPayment>
<paymentTermsNotes>#&lt;Updated Payment Terms&gt;</paymentTermsNotes>
<Lines>
<Line>
<recordType>Line</recordType>
<invoiceReferenceID>202</invoiceReferenceID>
<lineItemID>400</lineItemID>
<itemNumber>1</itemNumber>
<productDescription>UpdatedProductA</productDescription>
<productCategory>NewCategory</productCategory>
<purchaseOrderID>PO115</purchaseOrderID>
<orderItemNumber>5</orderItemNumber>
<unitPrice>3000</unitPrice>
<itemQuantity></itemQuantity>
<unitMeasurement>15000</unitMeasurement>
<lineTotalAmount></lineTotalAmount>
<shippingAmountDistributed></shippingAmountDistributed>
<miscellaneousAmountDistributed></miscellaneousAmountDistributed>
<handlingAmountDistributed></handlingAmountDistributed>
<accountingSegment1></accountingSegment1>
<accountingSegment2>SegmentA</accountingSegment2>
<accountingSegment3>SegmentB</accountingSegment3>
<accountingSegment4>SegmentC</accountingSegment4>
<financialAccountName>SegmentD</financialAccountName>
<financialAccountCode>AccountNameA</financialAccountCode>
</Line>
</Lines>
<LineSplits>
</LineSplits>
<Header>
<recordType>Header</recordType>
<recordID>203</recordID>
<invoiceIssueDate>2024-11-05T11:00:00Z</invoiceIssueDate>
<invoiceID>INVTEST1105-25</invoiceID>
<accountCategory>Standard Account</accountCategory>
<termsOfPayment>NET90</termsOfPayment>
<handlingCharge>20</handlingCharge>
<additionalCharge>30</additionalCharge>
<shippingCost>40</shippingCost>
<taxOnHeader>15</taxOnHeader>
<totalAmountDue>5500</totalAmountDue>
<totalGross>5500</totalGross>
<currencySymbol>GBP</currencySymbol>
<supplierCode>41</supplierCode>
<supplierFirm>TECHNOLOGY INNOVATIONS</supplierFirm>
<supplierReference>TEST0016</supplierReference>
<addressCodeForPayment>TEST0016</addressCodeForPayment>
<typeOfDocument>Remit Address 4</typeOfDocument>
<initialInvoiceNumber>89012</initialInvoiceNumber>
<initialInvoiceDate>2024-11-05</initialInvoiceDate>
<deliveryAddress>Delivery Address 4</deliveryAddress>
<modeOfPayment>bank_transfer</modeOfPayment>
<paymentTermsNotes>#&lt;Revised Payment Terms&gt;</paymentTermsNotes>
<Lines>
</Lines>
<LineSplits>
<LineSplit>
<recordType>Line Split</recordType>
<invoiceReferenceID>203</invoiceReferenceID>
<lineItemReferenceID>401</lineItemReferenceID>
<splitIdentifier>2</splitIdentifier>
<sequenceOfAllocation>1</sequenceOfAllocation>
<percentageAllocated>40</percentageAllocated>
<allocatedAmount>2800</allocatedAmount>
<distributedShippingCharge></distributedShippingCharge>
<distributedHandlingCharge>5600</distributedHandlingCharge>
<distributedMiscCharge></distributedMiscCharge>
<financialSegment1>28000</financialSegment1>
<financialSegment2>28000</financialSegment2>
<financialSegment3>28000</financialSegment3>
<accountCodeReference>28000-28000-28000-0006</accountCodeReference>
<financialSegment4>10</financialSegment4>
<accountHolderName>AccountHolderB</accountHolderName>
</LineSplit>
<LineSplit>
<recordType>Line Split</recordType>
<invoiceReferenceID>203</invoiceReferenceID>
<lineItemReferenceID>400</lineItemReferenceID>
<splitIdentifier>1</splitIdentifier>
<sequenceOfAllocation>1</sequenceOfAllocation>
<percentageAllocated>50</percentageAllocated>
<allocatedAmount>2500</allocatedAmount>
<distributedShippingCharge></distributedShippingCharge>
<distributedHandlingCharge>5000</distributedHandlingCharge>
<distributedMiscCharge></distributedMiscCharge>
<financialSegment1>25000</financialSegment1>
<financialSegment2>25000</financialSegment2>
<financialSegment3>25000</financialSegment3>
<accountCodeReference>25000-25000-25000-0005</accountCodeReference>
<financialSegment4>10</financialSegment4>
<accountHolderName>AccountHolderA</accountHolderName>
</LineSplit>
</LineSplits>
</Header>
</root>