SAP Integration Suite, Cloud Integration

Managing Nested CSV Structures in SAP Cloud Integration: A Generic Groovy Script Solution

webadmin

Introduction

File-based integrations are a common use case in SAP Cloud Integration (CI), enabling the exchange of structured or unstructured data between systems using file formats such as CSV, XML, or flat files.

Below are the Business Use Cases for File-Based Integrations in SAP CI,

  1. Employee Data Transfer: A company using SuccessFactors for HR management needs to send employee payroll data in CSV format to an external payroll vendor. SAP CI retrieves the CSV file from an SFTP server, transforms it into the required format (e.g., XML), and delivers it to the vendor’s system.
  2. Order Management: An e-commerce platform generates daily order details in CSV format, which needs to be integrated into an ERP system like SAP S/4HANA. SAP CI reads the CSV file, maps it to the required format, and posts it to the ERP system via an OData or IDoc interface.
  3. Inventory Updates: A retail chain sends CSV files with inventory updates from multiple stores to a central warehouse system. SAP CI processes these files, aggregates the data, and updates the inventory database in real-time.
  4. Bank Statement Processing: Banks provide daily account statements in CSV format, which businesses need to reconcile with their ERP systems. SAP CI retrieves these files, converts them into XML or JSON, and posts them to the ERP system for reconciliation.
  5. EDI File Pre-Processing: Trading partners send EDI files embedded within CSV files. SAP CI extracts the EDI content, processes it using Trading Partner Management (TPM), and forwards it to the downstream systems.
  6. Data Migration: During a system migration project, legacy data stored in CSV files needs to be imported into a new system. SAP CI reads these files, performs necessary transformations, and loads the data into the target system.
  7. Periodic Reporting: A manufacturing company generates production reports in CSV format and shares them with stakeholders via email or SFTP. CI automates this process by fetching the files, applying transformations, and distributing them to designated recipients.

Handling CSV Files in SAP CI Using CSV to XML Converter

SAP CPI handles CSV files using the CSV to XML Converter to transform flat-file data into a structured XML format, making it easier to process and integrate with other systems. The converter allows you to define the structure of the CSV file, including delimiters, headers, and field mappings, and then generates XML output based on this configuration. This transformation simplifies downstream processing, such as applying mapping logic, enriching data, or routing messages. Additionally, the converter supports handling complex scenarios like multi-line records, optional fields, and data validation, ensuring seamless integration of CSV files into various enterprise systems like SAP S/4HANA or third-party applications.

Limitation with Standard CSV to XML Converter

The standard CSV to XML converter is particularly useful for transforming CSV files into flat XML structures. However, when dealing with input CSV files containing nested structures—such as Header and Line record values—the standard converter falls short, as it does not support hierarchical transformations.

This document introduces an innovative approach to address such scenarios by utilizing a reusable Groovy script. This script is designed to handle nested CSV structures and convert them into the required XML format. The solution is versatile and can be applied across different CSV files with nested structures, ensuring consistent and efficient transformation.

By implementing this Groovy script, integration developers can overcome the limitations of the standard CSV to XML converter and streamline the handling of complex CSV structures within SAP CI.

Example Scenario

The CSV file below contains customer details (Identifier, Customer Name, ID, City) followed by order details (Identifier, Order ID, Order Name, Order Quantity) along with a footer.

Expected XML Structure

Steps

To achieve the above transformation, a customized Groovy script can be utilized. This script is designed to be reusable for similar requirements without requiring modifications.

The only requirement is to pass the necessary properties to the script using a Content Modifier. This approach ensures flexibility and simplifies the configuration process, making the script adaptable to various scenarios.

Content Modifier

Create a Content Modifier step, add the exchange properties, and assign values to them as described below.

  • Delimiter – Specify the delimiter based on the Input file
  • Main_Record_1 – Main record name in the xml structure (ex: Customer)
  • Main_Record_2 – Sub Record name in the xml structure (ex: Record)
  • Header_RecordHeader – Record name in the xml structure (ex: Customer_Details)
  • Header_Columns – Column names of the header record (ex: Identifier, Customer_Name, Customer_ID, Customer_City). Values should be separated by delimiter.
  • Line_Item_Record – LineItem record name in the xml structure (ex: Order_Details).
  • Line_Items – Column names of the LineItem record (ex: Identifier, Order_ID, Order_Name, Order_Quantity). Values should be separated by delimiter.
  • Footer_Required – Based on the need of footer values in the xml structure we need to set this value as YES/NO.
  • Footer_Record – If footer is required then footer record name in the target xml should be given here (ex: Footer). If footer is not required, then value should be NA.
  • Footer – If footer is required then column names of the footer record should be given here (ex: Identifier, Date). If footer not required, then value should be NA.

Groovy Script

Create a Groovy script step and use the attached code as-is, following the Content Modifier step

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import java.io.StringWriter;
import java.io.*;
def Message processData(Message message) {
    //Body 
       def body = message.getBody(java.lang.String) as String;
       
       def h=[]
       def hn=[]
       def ln=[]
       def ft=[]
       
    //reading the property values
       map = message.getProperties();
       
       def delimiter=map.get("Delimiter");
       def HeaderColumn=map.get("Header_Columns");
       def HeaderRecord=map.get("Header_Record");
       def LineItems=map.get("Line_Items");
       def LineItemRecord=map.get("Line_Item_Record");
       def MainRecord=map.get("Main_Record_1");
       def SubRecord=map.get("Main_Record_2");
       def footer=map.get("Footer");
       def FooterRecord=map.get("Footer_Record");
       def FooterRequired=map.get("Footer_Required");
       
       StringBuilder sb = new StringBuilder();
       
       String s1="<"+MainRecord+">"+"<"+HeaderRecord+">";
       String s2="<"+MainRecord+">"+"<"+SubRecord+">"+"<"+HeaderRecord+">";
       String s3="</"+LineItemRecord+">"+"</"+MainRecord+">";
       String s4="</"+LineItemRecord+">"+"</"+SubRecord+">"+"</"+MainRecord+">";
       String s5="</"+LineItemRecord+">"+"<"+HeaderRecord+">";
       String s6="</"+LineItemRecord+">"+"</"+SubRecord+">"+"<"+SubRecord+">"+"<"+HeaderRecord+">";
       String s7="</"+FooterRecord+">"+"<"+HeaderRecord+">";
       String s8="</"+FooterRecord+">"+"</"+SubRecord+">"+"<"+SubRecord+">"+"<"+HeaderRecord+">";
       String s9="</"+FooterRecord+">"+"</"+MainRecord+">";
       String s10="</"+FooterRecord+">"+"</"+SubRecord+">"+"</"+MainRecord+">";
       
        sb.append("<").append(MainRecord).append(">");
       
       //Splitting the CSV, line by line
       def b=body.split('\n');
       
       hn=HeaderColumn.split(delimiter);
       ln=LineItems.split(delimiter);
       ft=footer.split(delimiter);
       int n=hn.length;
       int l=ln.length;
       int k=ft.length;
       for (int i=0;i<b.size();i++)
       {
           h=b[i].split(delimiter);
              if(h[0].equals('H'))           //header
              {
                sb.append("<").append(HeaderRecord).append(">");
                for (int j=0;j<n;j++)
                {
                  sb.append("<").append(hn[j]).append(">").append(h[j]).append("</").append(hn[j]).append(">"); 
                }
               sb.append("</").append(HeaderRecord).append(">");
              }
           
           if(h[0].equals('L'))             //lineitems
           {
              sb.append("<").append(LineItemRecord).append(">"); 
              for (int j=0;j<l;j++)
              {
                  sb.append("<").append(ln[j]).append(">").append(h[j]).append("</").append(ln[j]).append(">"); 
              }
               sb.append("</").append(LineItemRecord).append(">");
              
           }
           
           if(h[0].equals('F') && (FooterRequired.equals('YES')))  //footer
           {
              sb.append("<").append(FooterRecord).append(">"); 
              for (int j=0;j<k;j++)
              {
                  sb.append("<").append(ft[j]).append(">").append(h[j]).append("</").append(ft[j]).append(">"); 
              }
               sb.append("</").append(FooterRecord).append(">");
            }
       }
     
       sb.append("</").append(MainRecord).append(">")
message.setBody(sb.toString().replaceAll(s1,s2).replaceAll(s3,s4).replaceAll(s5,s6).replaceAll(s7,s8).replaceAll(s9,s10));
       
       return message;
}

Other Scenarios

The same approach is equally effective for CSV files with multiple headers, below is the example CSV file.

Note

  • This script assumes that the header line starts with “H,” the line item starts with “L,” and the footer line starts with “F”.
  • Please ensure that the properties in the Content Modifier are defined with the same names as mentioned above, paying attention to case sensitivity.
Rating: 5 / 5 (1 votes)