Skip to Content

Update 3 Oct 2018: Added Excel converters

 

Introduction

If you have worked on SAP PI over the past few years, there is a possibility that you may have come across FormatConversionBean – One Bean to rule them all! It is an open source project I developed, providing various format conversion functionalities (complementing SAP’s offering) packaged in a custom PI adapter module.

 

Although CPI provides similar capabilities, some of its converters lack customising options for more complex scenarios.

 

The good news is – FormatConversionBean is now available in CPI! Over the past weeks, I’ve began porting over the development to fit into CPI’s Camel-based framework, and the bulk of functionality is now ready to be used.

 

Converters

The following table lists the converters that are available as part of FormatConversionBean in CPI. The reference link for each converter provides more details on the available configuration options/parameters.

 

Note: The converter classes are in a different package (com.equalize.converter.core) compared to their PI counterparts.

 

 

Usage in Integration Flow

Due to differences between the nature of the design of integration flows in CPI compared to PI, the approach of using it can be summarised in the following steps.

1) Upload JAR file

2) Configure parameters (via Content Modifier or script)

3) Add Groovy script

 

Below is a sample integration flow utilising FormatConversionBean.

 

Further details of each step are as follows:-

Step 1 – Upload JAR file

i) Download latest release of converter-core-x.x.x.jar from GitHub repository.

ii) Use Resources view to upload JAR file as an Archive into Integration Flow.

 

Note: To use the Excel converters, the following Apache POI 3.17 libraries need to be uploaded into the Integration Flow as well.

 

Step 2 – Configure parameters

Parameters are passed into FormatConversionBean using Exchange Properties. These can be configured via Content Modifier (sample below) or Script (Groovy or Javascript). Refer to each converter’s blog post for details on available parameters.

 

Step 3 – Add Groovy script

This is the entry point for the execution of FormatConversionBean. Add a Groovy script step in the Integration Flow with the following code.

import com.sap.gateway.ip.core.customdev.util.Message
import com.equalize.cpi.converter.FormatConversionBean

def Message processData(Message message) {

	def fcb = new FormatConversionBean(message.exchange, message.getProperties())
	def output = fcb.convert()
	message.setBody(output)
	return message
}

 

That’s it! It is as simple as that! 🙂

 

Source Code, Enhancements & Collaboration

The source code for FormatConversionBean is hosted at the following GitHub repository.

https://github.com/engswee/equalize-cpi-converter

It is a Maven-based project with the following attributes:

  • Mixed Java and Groovy development
  • Unit tests written in Spock
  • Automated Maven build process

 

Anyone and everyone is welcome to fork/clone the repository to further enhance privately or collaborate publicly on this project.

 

More details utilising Eclipse/Maven to develop and build the project to follow in a future blog post.

 

Bugs & Feature Requests

If you encounter any bugs or would like to request for a particular feature, the preferred approach is to raise an issue at the following GitHub repository.

https://github.com/engswee/equalize-cpi-converter/issues

This provides a better avenue to track, communicate and collaborate on the issue instead of the comment section of this blog post.

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Naresh Dasika

     

    That’s a great news and thanks a lot for publishing. Hopefully this makes the developer job easy when it comes to nested/deeper structure conversions.

    (1) 
    1. Eng Swee Yeoh
      Post author

      Hi Naresh

       

      Thanks for your comment. Yes, that is the whole intention – this should hopefully provide an alternative if the standard converters do not meet the integration requirement.

       

      Regards

      Eng Swee

      (0) 
  2. Iddo Rijsdijk

    That’s excellent news! I’ve been using your bean in lots of integration scenario’s. I’m really looking forward to use this version in the CPI project I’m currently working on.

    (1) 
    1. Eng Swee Yeoh
      Post author

      Hi Iddo

       

      Thanks for your comment. It’s great to hear from someone who has been using the PI version of this in real integration scenarios – I rarely get much feedback on how it is doing out there unless someone hits an issue!

      In terms of functionality, not much has changed since the last PI version release, but I do hope to be able to work on enhancing this CPI version further once I complete the whole port (Excel converters coming soon).

      Would love to hear from you how this CPI version gets along, so do write back 😉

       

      Regards

      Eng Swee

       

      (0) 
      1. Tom van Rooijen

        I just tried out the scenario XML to flatfile with fixed field lengths, it works like a charm!

        It must be said that I do not have a deep structure, I have a flat but diverse structure.

        The xml  structure consists of a root node with 7 different record types below it at the same level. Each record type can occur multiple times, like this:

        Sage
        	 Etat_Civil
        	 Bilan_Social
        	 Lieu_de_Travail
        	 Affectation
        	 DADS-U
        	 Salaire
        	 immatriculation

        As the go-live date of the solution is somewhere this week, I guess I won’t replace it.

        it would have saved me a week of work on the workaround 🙂

        Also the excel conversion is interesting, I’m going to test that as well..

         

        Thanks!

         

         

        (0) 
        1. Eng Swee Yeoh
          Post author

          Glad to hear it is working 🙂

          Yes, it does work with with simple/flat structures as well as deep ones too. Historically, PI already had standard functionality to handle simple/flat structures, so FormatConversionBean was to cater for the deep ones. For CPI, the CSV<>XML converters are still quite basic, so FormatConversionBean can possibly cater for more use cases.

          (0) 
        2. Tom van Rooijen

          Haven’t been able to get the XmltoExcel conversion working.

          It produces some sort of an xlsx, there is no error in CPI, but when I look inside the generated file it seems to be empty.

           

          I kept my parameters to a minimum:

          import com.sap.gateway.ip.core.customdev.util.Message;
          import java.util.HashMap;
          import java.text.DateFormat;
          import java.text.SimpleDateFormat;
          import java.util.Date;
          
          def Message processData(Message message) {
          	
          	//Body 
          	def body = message.getBody();
          	message.setProperty("converterClass","com.equalize.converter.core.XML2ExcelConverter")
          	return message;
          	}

           

           

           

          (0) 
          1. Eng Swee Yeoh
            Post author

            Hi Tom

             

            Maybe something to do with the input XML content. Try checking the following blog and see if you can reproduce the XLSX output with the same input XML.

            https://blogs.sap.com/2014/11/03/exceltransformbean-part-2-convert-simple-xml-to-various-excel-formats-easily/

             

            Unlike the Flat file converter, the Excel converter only caters for simple XML structures, i.e. just one type of segment.

             

            If it still doesn’t work, please open an issue on the GitHub repository (details in the blog part above), and attach a copy of the input XML file.

             

            Regards

            Eng Swee

            (0) 
  3. vikram singh

    These features are really developer friendly. Can’t wait to try it in my next assignment.

    Thanks for summarizing it.

     

    Regards,

    Varinder

    (1) 
  4. Rutuja Thakre

    Hi Engg Swee Yeoh,

     

    I have tried implementing it in CPI but I am getting below error:

    java.lang.Exception: java.lang.ClassNotFoundException: com.equalize.converter.core is an invalid converter class@ line 23 in script1.groovy

    I have already uploaded the latest JAR converter-core-1.1.0 as Archive in Resource.

    I am missing any step?

    Regards,

    Rutuja Thakre

    (0) 
    1. Eng Swee Yeoh
      Post author

      Hi Rutuja

       

      Thanks for your interest in this.

       

      The error is because the value com.equalize.converter.core in property converterClass is incomplete. You need to refer to each converter’s blog post to get the correct value, e.g. com.equalize.converter.core.XML2JSONConverter.

       

      I think the screenshot on step 2 might have been a bit misleading as the value is truncated in the UI – I have modified the screenshot now to show an example of the full value that should be in the property.

       

      Regards

      Eng Swee

      (0) 
      1. Rutuja Thakre

        Hi Eng Swee,

         

        Thanks for your quick reply.

        I have made the changes in converterClass and its working fine now.

        Great Work.

         

        Regards,

        Rutuja Thakre

        (1) 

Leave a Reply