Skip to Content
Author's profile photo Eng Swee Yeoh

FormatConversionBean arrives in CPI!

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.

Assigned Tags

      40 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Naresh Dasika
      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.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog 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

      Author's profile photo Nagesh Chepuri
      Nagesh Chepuri

      Hi Eng Swee,

      I have encrypted .XLSX.PGP file in application server, how to decrypt the XLSX file and move to directory using sender SFTP adapter?

      Regards,

      Nagesh

      Author's profile photo Iddo Rijsdijk
      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.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog 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

       

      Author's profile photo Iddo Rijsdijk
      Iddo Rijsdijk

      Hi Eng Swee,

      I can tell you that I successfully implemented your conversion bean in a CPI scenario where the standard SAP XML-JSON and JSON-XML conversion wasn't capable enough. Something to do with invalid characters in the JSON field names.

      Awesome work!

      Iddo

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Iddo

       

      Thanks for writing back. Real glad to hear that it worked well for your scenario!

       

      Regards

      Eng Swee

      Author's profile photo T. van Rooijen
      T. van Rooijen

      Wow, that is welcome news indeed, I will almost be sorry to see my somewhat messy workarounds go 🙂

       

      Regards

      Tom

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Let me know how it goes if you get around to replacing them with this 😉

      Author's profile photo T. van Rooijen
      T. 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!

       

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog 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.

      Author's profile photo T. van Rooijen
      T. 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;
      	}

       

       

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog 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

      Author's profile photo Shashwat Trivedi
      Shashwat Trivedi

      Hi Tom,

      I was hoping if you could list down the steps you followed to achieve the conversion for scenario XML to flatfile with fixed field lengths. I have a similar requirement and it would be great if you can provide the steps needed to be done on the CPI tenant to achieve this.

       

      Regards,

      Shashwat

      Author's profile photo Varinder singh
      Varinder singh

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

      Thanks for summarizing it.

       

      Regards,

      Varinder

      Author's profile photo Rutuja Thakre
      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog 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

      Author's profile photo Rutuja Thakre
      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

      Author's profile photo Joern Heipmann
      Joern Heipmann

      First of all, thanks for all the work you put in here.

      For whatever reasn I seem to be unable to make use of the XLS2XML conversion. I get a java.lang.reflect.MalformedParameterizedTypeException whenver I add the documentNamespace parameter in the "set parameter" step. I tried my own namespace, but also the one mentioned in your post (urn:equalize:com) both in paranthesis as well as without, I also tried simple once like urn:ns0 but I seem to be unable to figure it out.

      Could you pls. provide a screenshot how exactly it should be specified?

       

      Thx.

      Joern

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Joern

       

      Looks like for some reason, the latest 1.3.0 release is having this MalformedParameterizedTypeException issue in other users' tenant despite it working perfectly well on mine.

       

      I will have to release a new version to reverse the changes.

       

      In the meantime, please use version 1.2.0 - it should work fine. Let me know how it goes.

       

      Regards

      Eng Swee

      Author's profile photo Joern Heipmann
      Joern Heipmann

      Hi Eng

      Thanks for the help, acually I tried 1.2.0 but when it errored I saw you published a version 1.3.1, and that works perfectly for me.

      I guess I did something wrong with he 1.2.0 install, but as I have to hurry with my prototype, I will need to leave it at 1.3.1 for now. Once I find the time I will come back and analyze the 1.2.0 problem.

      Again, thx for all the efoort you put in here.

       

      KR

      Jörn

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Glad to hear that 1.3.1 worked well for you, Joern.

       

      Do let me know if you fine any other issues with it, and also if you do happen to try 1.2.0 and hit some other issue.

       

      Hope your prototype goes well.

      Author's profile photo Vijaya Palla
      Vijaya Palla

      Hi Eng Swee,

      Thank you for getting the bean on to CPI.

      I have a quick question reg deep flat file to xml. Can we convert a flat idoc to idoc xml using this bean? is it supported?

      Thanks,

      Vijaya Palla

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      You can try, but I would imagine it would be quite complex considering how complex an IDoc structure is - be it the flat representation or the XML. No guarantee it will work.

      Author's profile photo Vijaya Palla
      Vijaya Palla

      True, but does this actually confused me.

      • Each substructure type must belong to one and only one parent substructure type. The parent can be either the root element or other substructure types defined

      how will my record structure understand that Items1 and Items2 are at the same level, if I specify it as Order,Items1,Items2?

      <Order>

      <Items1>

      </Items1>

      <Items2>

      </Items2>

      </Order>

       

      Author's profile photo Vijaya Palla
      Vijaya Palla

      I would try using the parent parameter. I would keep you posted on how it works.

      Thanks,

      Vijaya Palla

      Author's profile photo Rhonda Wilcox
      Rhonda Wilcox

      Hello Eng Swee Yeoh, @engswee.yeoh

      This is excellent work!  Thank you so much.

      I am trying to use the XML2DeepPlainConverter in CPI.  I have downloaded converter-core-2.1.0.jar version and uploaded it into CPI.  I have even tested my scenario with an the older version 1.2 to see if it made a difference, but there was no difference.  I am getting the following error in the groovy script.

      java.lang.NullPointerException

      My input message is below:

      <header1>
      <hdrconstant1>header desc 1</hdrconstant1>
      <header2>
      <hdrconstant2>header desc 2</hdrconstant2>
      </header2>
      <details>
      <num>00000001</num>
      <date>20190906</date>
      <acctnumber>1234567891</acctnumber>
      <code>00</code>
      <amount>10.0</amount>
      <name>DIESEL</name>
      </details>
      <details>
      <num>00000002</num>
      <date>20190906</date>
      <acctnumber>1234567892</acctnumber>
      <code>00</code>
      <amount>20.0</amount>
      <name>DIESEL</name>
      </details>
      <trailer>
      <trlconstant1>test</trlconstant1>
      <counter>2</counter>
      <trlconstant2> </trlconstant2>
      <totalamount>30.0</totalamount>
      </trailer>
      </header1>

      Here is my groovy script:

      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
      }

      I have attached a copy of my content modifer.

      Please help me solve this issue!

      Thank you,

      Rhonda

      Author's profile photo Rhonda Wilcox
      Rhonda Wilcox

      Hello, I apologize. My issue has been solved. I had named the groovy script with extension ".GROOVY" instead of ".groovy". It must be lowercase.

      Thank you again @engswee.yeoh for your great work!!!

      Rhonda

      Author's profile photo Shreyashri Kar
      Shreyashri Kar

      Hello @engswee.yeoh

      Thanks for such a great explanatory post.

      I have tried using the XmltoExcel conversion feature in CPI following both of your blogs.
      I have downloaded the required jar files from gitHub and uploaded into the CPI Iflow. I am using the same groovy script as shown here.


      It produces some sort of a distorted output although there is no error in CPI.

      I have attached the screenshots of my content modifer, resources & output over here.
      Can you please help me in understanding the anomaly? Am I missing any step?

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      The output generated is binary data, so you won't be able to view it in textual format on the browser.

      Author's profile photo Manoj Kumar Pothuganti
      Manoj Kumar Pothuganti

      Hello @engswee.yeoh

       

      I am trying to use Excel2XMLConverter in CPI.

      But am facing below error: (Please suggest, if there any further configs required)

      "org.apache.camel.CamelExecutionException: Exception occurred during execution on the exchange:  cause: java.lang.ClassNotFoundException: org.apache.commons.math3.util.ArithmeticUtils"

       

      My configuration As below:

       

      Need few inputs here.

      Author's profile photo Manoj Kumar Pothuganti
      Manoj Kumar Pothuganti

      Thank you again @engswee.yeoh
      My issue resolved after importing "commons-math3-x.x.x.jar".

      Author's profile photo Jonathan Ma
      Jonathan Ma

      Nice blog. Are you aware of any script that can convert and HTML table to XML/CSV/JSON? We are on CPI. The javascripts I found in the web is mainly meant to be run inside a htm page. It is using document.querySelectorAll as the parser which doesn't work in CPI. Is there a way to store the html table we receive in the message body as a real html file somewhere in CPI and thus we can call the document.querySelectorAll?

      Thanks Jonathan.

      Author's profile photo Gopal KS
      Gopal KS

      Thanks Eng Swee for this excellent blog. 

      Author's profile photo Ryan Crosby
      Ryan Crosby

      Cheers Eng Swee,

      Took two minutes to setup and easily generate a file for some users to consume due to a multi-ERP environment, which leads to some difficulty for them.  As a side note from the last time I commented on a blog of yours... CPI is growing on me and I find the camel model super flexible for object reuse (outside of not allowing cross package references yet).  The partner directory API is super handy and despite not using it yet because we just got CPI it's going to help a lot.  Taking a little time to internalize some of the notations and things but it's coming along 🙂

       

      Regards,

      Ryan Crosby

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Ryan, glad to know you got this up and running without much trouble 🙂 And good to know that CPI is growing on you... One day, you may look back and never want to touch PI/PO again 😝

      Author's profile photo Ryan Crosby
      Ryan Crosby

      Hi Eng Swee,

      Oh I'm already there in regards to skipping out on PI/PO, although, I may still have an inkling for using the graphical mapping based tools for that portion when required.  The nice thing is the system is empty from the start, so I'm not carrying forward a ton of legacy content that is crumbling and held together with a combination of duct tape, staples, and bungee cords.  Already have two productive scenarios with some object reuse in the first week - I configured the TMS last week after trying to find blog content that explained the process (well enough 😝).  That's my one knock, is that like many things in SAP you only get the "Hello World" help documentation, which isn't suitable for real world scenarios.

       

      Regards,

      Ryan Crosby

      Author's profile photo Arjun Singh Thakur
      Arjun Singh Thakur

      Hi Eng Swee Yeoh,

      Thanks a lot for sharing. This is very useful.

      One question: Is it possible to create multiple sheets in a single excel using XML2ExcelConverter in CPI?

      Regards,

      Arjun

      Author's profile photo nisha shrivansh
      nisha shrivansh

      Hi Eng Swee,

      I need to convert excel to XML file and i followed the steps mentioned above, while execution I am getting error:

      java.lang.Exception: com.equalize.converter.core.util.ConverterException: Starting row is greater than last row of sheet@ line 106 in script1.groovy

      Could you please let me know how to fix this error?

      Thanks & Regards,

      Nisha Shrivansh

      Author's profile photo Chetan Risbud
      Chetan Risbud

      Hi Eng Swee Yeoh

      Could you please help on this https://answers.sap.com/questions/13800099/file-content-conversion-in-sap-cpi-using-format-co.html

      Regards,

      Chetan Risbud