CPI: BAI file: Convert a text file to XML using groovy script

[Former Member]

Hi Guys,

I have a text fie from the bank which needs to be converted to complex type WSDL file and send to S4 HANA cloud via SOAP protocol. I checked many blogs and realized a CSV to XML converter can be used only for simple XML files. I believe a groovy script has to be used to convert the text file to WSDL. Has anybody else encountered a similar issue and if yes how did you resolve it?

Note: I have put a snippet of the XML and WSDL file to give an idea of how it would look like. Depending on the first two digits the values/fields that follow in that line varies.

Text file format:

01,BOFMCAM2,10000833,200611,1038,5,80,1,2/ 02,10000833,BOFMCAM2,1,200610,,USD,2/ 03,00014628973,USD,015,15789137,,,901,15789137,,, 88,010,15789137,,,030,15789137,,, 88,040,15789137,,,060,15789137,,, 88,066,15789137,,/ 49,110523959,5/ 03,00011240341,CAD,901,0,,,015,72520353,,, 88,010,-2907305,,,040,0,,, 88,100,75427658,9,V,200610,,066,0,,, 88,030,72520353,,,060,72520353,,/ 16,275,2907305,V,200609,,,/ 88,Zero Balance Credit 88,0001-1025-852 88,VIREMENT-TRESORERIE ZERO 16,195,152060,V,200610,,,/ 88,Incoming Wire Payment 88,RECEPTION D"UN PAIEMENT 88,CA, CONCUR TECHNOLOGIES I 16,142,9252963,V,200610,,,/ 88,Direct Deposit 88,GE LEBLANC MSP/DIV 16,142,344925,V,200610,,,/ 88,Direct Deposit 88,STC BPY/FAC 16,142,3042526,V,200610,,,/ 88,Direct Deposit

WSDL file:

-<xsd:complexType name="ElectronicBankStatementHeader"> -<xsd:sequence> <xsd:element type="xi0:BankNumber_GFN" name="BankNumber" minOccurs="0"/> <xsd:element type="xi0:BankCountry_GFN" name="BankCountry" minOccurs="0"/> <xsd:element type="xi0:SWIFTCode_GFN" name="SWIFTCode" minOccurs="0"/> <xsd:element type="xi0:BankAccountID" name="BankAccount" minOccurs="0"/> <xsd:element type="xi0:BankAccountHolderName" name="BankAccountHolderName" minOccurs="0"/> <xsd:element type="xi0:BankAccountAdditionalName_GFN" name="BankAccountAdditionalName" minOccurs="0"/> <xsd:element type="xi0:BankAccountType_GFN" name="BankAccountType" minOccurs="0"/> <xsd:element type="xi0:BankStatement_GFN" name="BankStatement" minOccurs="0"/> <xsd:element type="xi0:Date" name="BankStatementDate" minOccurs="0"/> <xsd:element type="xi0:Date" name="BankStatementPeriodStartDate" minOccurs="0"/> <xsd:element type="xi0:Date" name="BankStatementPeriodEndDate" minOccurs="0"/> <xsd:element type="xi0:CurrencyCode" name="Currency" minOccurs="0"/> <xsd:element type="xi0:Amount" name="OpeningBalAmtInBankAcctCrcy" minOccurs="0"/> <xsd:element type="xi0:Amount" name="TotalDebitAmtInBkAcctCrcy" minOccurs="0"/> <xsd:element type="xi0:Amount" name="TotalCreditAmtInBkAcctCrcy" minOccurs="0"/> <xsd:element type="xi0:Amount" name="ClsgBalAmtInBkAcctCrcy" minOccurs="0"/> <xsd:element type="xi0:BankStatementNumberOfItems_GFN" name="BankStatementNumberOfItems" minOccurs="0"/> <xsd:element type="xi0:SenderIBAN_GFN" name="SenderIBAN" minOccurs="0"/> </xsd:sequence> </xsd:complexType> -<xsd:complexType name="ElectronicBankStatementLineItems"> -<xsd:sequence> <xsd:element type="xi0:Date" name="BankPostingDate" minOccurs="0"/> <xsd:element type="xi0:Date" name="ValueDate" minOccurs="0"/> <xsd:element type="xi0:Time" name="ValueDateTime" minOccurs="0"/> <xsd:element type="xi0:Amount" name="AmountInBankAccountCurrency" minOccurs="0"/> <xsd:element type="xi0:Amount" name="FeeAmountInTransactionCrcy" minOccurs="0"/> <xsd:element type="xi0:CurrencyCode" name="OriginalCurrency" minOccurs="0"/> <xsd:element type="xi0:Amount" name="AmountInOriginalCurrency" minOccurs="0"/> <xsd:element type="xi0:Amount" name="FeeAmountInOriginalCrcy" minOccurs="0"/> <xsd:element type="xi0:PaymentTransactionText_GFN" name="PaymentTransactionCode" minOccurs="0"/> <xsd:element type="xi0:PartnerBankCountry_GFN" name="PartnerBankCountry" minOccurs="0"/> <xsd:element type="xi0:PartnerBank_GFN" name="PartnerBank" minOccurs="0"/> <xsd:element type="xi0:PartnerBankSWIFTCode_GFN" name="PartnerBankSWIFTCode" minOccurs="0"/> <xsd:element type="PartnerBankAccount" name="PartnerBankAccount" minOccurs="0"/> <xsd:element type="xi0:BusinessPartner_GFN" name="BusinessPartner" minOccurs="0"/> <xsd:element type="xi0:DaybookEntry_GFN" name="DaybookEntry" minOccurs="0"/> <xsd:element type="xi0:Check_GFN" name="Check" minOccurs="0"/> <xsd:element type="xi0:BankStatementItemDescription1_GFN" name="BankStatementItemDescription1" minOccurs="0"/> <xsd:element type="xi0:BankStatementItemDescription2_GFN" name="BankStatementItemDescription2" minOccurs="0"/> <xsd:element type="xi0:ExchangeRateRate" name="ExchangeRate" minOccurs="0"/> <xsd:element type="xi0:Amount" name="OverdueChargeAmtInBkAcctCrcy" minOccurs="0"/> <xsd:element type="xi0:PartnerBankIBAN_GFN" name="PartnerBankIBAN" minOccurs="0"/> <xsd:element type="xi0:NoteToPayeeInBankStatement_GFN" name="NoteToPayeeInBankStatement" maxOccurs="unbounded" minOccurs="0"/> </xsd:sequence>

Thanks in advance.

[oumaimayahyaoui]

Hello, do you the groovy script in which you have to calculate the Opening balance amount total debit credit and closing balance amount? I will appreciate that! thanks

[MortenWittrock · Accepted solution]

Hi Nisha

A couple of notes. First off, you are not creating WSDL. Rather, you need to create the request format of the SOAP service, which is what the WSDL describes.

Second, if you are completely new to Groovy scripting, I would take the advice from ramvijay and use some of the facilities that don't require coding for now.

Mastering the Script step is easy, but learning how to program in Groovy takes a lot longer. Luckily, you do have other options in the meantime.

Regards,

Morten

[Former Member · Accepted solution]

Thanks a lot Morten. It was very helpful.

[former_member666312]

Hi,

In your case you can simply convert the CSV file to XML (by creating the XSD and provide to iflow as Resouce ) and do a mapping with xsd and wsdl as source and target it should work fine.

Thanks and Regards,

Vijay.

[Former Member]

Hi ramvijay,

Thanks for your input.

But i am facing a challenge using the CSV to XML converter. My XML file is for BAI and it has a header and item. Also the header and item is differentiated by the first two digits( which again varies ) in each line of the CSV. I have a snippet of how the CSV looks in my initial message.

CSV Sample:

01,BOFMCAM2,10000833,200611,1038,5,80,1,2/ 02,10000833,BOFMCAM2,1,200610,,USD,2/ 03,00014628973,USD,015,15789137,,,901,15789137,,, 88,010,15789137,,,030,15789137,,, 88,040,15789137,,,060,15789137,,, 88,066,15789137,,/ 49,110523959,5/ 03,00011240341,CAD,901,0,,,015,72520353,,, 88,010,-2907305,,,040,0,,, 88,100,75427658,9,V,200610,,066,0,,, 88,030,72520353,,,060,72520353,,/ 16,275,2907305,V,200609,,,/ 88,Zero Balance Credit 88,0001-1025-852 88,VIREMENT-TRESORERIE ZERO 16,195,152060,V,200610,,,/ 88,Incoming Wire Payment 88,RECEPTION D"UN PAIEMENT 88,CA, CONCUR TECHNOLOGIES I 16,142,9252963,V,200610,,,/ 88,Direct Deposit 88,GE LEBLANC MSP/DIV 16,142,344925,V,200610,,,/ 88,Direct Deposit 88,STC BPY/FAC 16,142,3042526,V,200610,,,/ 88,Direct Deposit

For example :

The line starting 01, 03 are related to header. 88 is item.

Can we handle this in the converter?

[former_member666312]

Hi ,

Can you attach the sample file of csv to convert to xml.

Thanks,

Vijay

[Former Member]

Hi Vijay Kumar Kapuganti,

I have attached the CSV(from bank) formats as bai-test-4.txt.

The XSD/XML format I am not able to attach, so attached as text file as sap-example.txt.

[Former Member]

Hi Vijay Kumar Kapuganti,

I tried writing a groovy script to create a XML with the relevant fields. So that then i can map this my XML. But since each of the lines in the text file have different number of fields, it is causing an issue.

Could you help me resolve this issue.

Thanks in advance.

[oumaimayahyaoui]

H

[former_member1924]

Hi nishadivakaran,

The simplest way I could think of is to write a groovy script to read each line and group each block of header and line item and then use MarkupBuilder to create the required XML.

• Group

H,header1

L,lineitem

L,lineitem

H,header2

L,lineitem

L,lineitem

so it should look like this.

[ [ [H,header1], [L,lineItem], [L,lineitem] ], [ [H,header2], [L,lineItem], [L,lineitem] ] ... ]

• Post this step you could use MarkupBuilder to convert this into xml.

hope this helps !

thanks and regards,

Praveen T

[Former Member]

Hi Praveen,

Thank a lot for the response. I am very new to CPI and groovy scripts. Do you have a sample for how this could be achieved? I looked a bit into MarkupBuilder and saw it is also a class which is used in Groovy script.

Thanks in advance.

Nisha