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.
- poi-3.17.jar
- poi-ooxml-3.17.jar
- poi-ooxml-schemas-3.17.jar
- xmlbeans-2.6.0.jar
- commons-collections4-4.1.jar
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.
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.
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
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
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.
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
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
Hi Iddo
Thanks for writing back. Real glad to hear that it worked well for your scenario!
Regards
Eng Swee
Wow, that is welcome news indeed, I will almost be sorry to see my somewhat messy workarounds go 🙂
Regards
Tom
Let me know how it goes if you get around to replacing them with this 😉
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:
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!
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.
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:
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
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
These features are really developer friendly. Can't wait to try it in my next assignment.
Thanks for summarizing it.
Regards,
Varinder
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
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
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
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
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
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
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.
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
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.
True, but does this actually confused me.
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>
I would try using the parent parameter. I would keep you posted on how it works.
Thanks,
Vijaya Palla
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
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
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?
The output generated is binary data, so you won't be able to view it in textual format on the browser.
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.
Thank you again @engswee.yeoh
My issue resolved after importing "commons-math3-x.x.x.jar".
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.
Thanks Eng Swee for this excellent blog.
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
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 😝
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
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
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
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