Technical Articles
Fixed Length File Generation Scenario Through SAP Cloud Platform Integration
Introduction:
One of the common requirements in SAP Cloud Platform Integration is to generate fixed length format output file for an end application. And this file will have different fields’ length based on application system specification. Generally, in SAP CPI message mapping can be used to produce the target xml using graphical mapping editor which in turn corresponds to the Custom XSD/Schema created for end/receiver application side similar to SAP PI/PO middleware. This final xml is now being passed through XML To CSV Converter module in SAP CPI to generate the file output having field separator as one of below options:
Comma(,),Colon(:) , Dot(.),Pipe(|),Semicolon(;),Space(),Tab(\t)
However, there is no functionality available for generating the fixed length format file using this convertor module in SAP CPI.
Here, I have explained one scenario where business is expecting fixed length format file with content of each field are left justified and remaining unused width of each field’s are padded with blank spaces to maintain every field’s start and end position in a text file output.
I have leveraged the use of a XSLT mapping to achieve this requirement.This XSLT mapping can be leveraged by developers to edit and modify and enhanced according to other business requirements as well.
Prerequisites: You should have CPI tenant access and have understanding on Integration flow development.
Design approach: To explain the scenario, I have used sample xml directly which is having header, detail and trailer records to avoid all the processing steps and directly jumping to the final xml format(used inside content modifier body of SAP CPI iflow as test data xml ) to be converted into a fixed length file format using XSLT and place the file in SFTP folder using SAP CPI SFTP adapter.
Field specification are detailed below for the use case shown in this blog post:
Image 1: Fields specification
Now, Integration flow steps used for our use case demonstration purpose are mentioned below :
Image 2: Sample SAP CPI Integration Flow
Steps configured on Integration flow as below:
Step 1)
Content Modifier :To store the sample file xml to be passed to XSLT mapping in next step.
Image 3: Content modifier(Test xml Input provided for example)
Sample Input Xml for used in content modifier as shown below:
<Records>
<Header>
<HeaderIndicator>H</HeaderIndicator>
<HeaderDate>20210405</HeaderDate>
<HeaderFiller> </HeaderFiller>
</Header>
<Detail>
<DetailIndicator>D</DetailIndicator>
<DetailRecordDate>20210405</DetailRecordDate>
<Name>Deepak Jaiswal</Name>
<Phone>123457890</Phone>
</Detail>
<Detail>
<DetailIndicator>D</DetailIndicator>
<DetailRecordDate>20210405</DetailRecordDate>
<Name>Rajesh Sharma</Name>
<Phone>234578901</Phone>
</Detail>
<Detail>
<DetailIndicator>D</DetailIndicator>
<DetailRecordDate>20210405</DetailRecordDate>
<Name>Lokesh Kumar</Name>
<Phone>345789012</Phone>
</Detail>
<Trailer>
<TrailerIndicator>T</TrailerIndicator>
<TrailerDate>20210405</TrailerDate>
<DetailRecordCount>0000000003</DetailRecordCount>
<TrailerFiller> </TrailerFiller>
</Trailer>
</Records>
Sample Input Xml( used in Content modifier step Screenshot)
Step 2)
XSLT mapping : To convert input xml from step 1) to a fixed length format output file data:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output omit-xml-declaration="yes" indent="yes" method="text"/>
<xsl:strip-space elements="*"/>
<xsl:template match="node()|@*">
<xsl:copy>
<xsl:apply-templates select="node()|@*"/>
</xsl:copy>
</xsl:template>
<xsl:template match="Header">
<xsl:apply-templates />
<xsl:text>
</xsl:text>
</xsl:template>
<xsl:template match="HeaderIndicator">
<xsl:value-of
select="substring(concat(., ' '), 1, 1)"/>
</xsl:template>
<xsl:template match="HeaderDate">
<xsl:value-of
select="substring(concat(., ' '), 1, 10)"/>
</xsl:template>
<xsl:template match="HeaderFiller">
<xsl:value-of
select="substring(concat(., ' '), 1, 100)"/>
</xsl:template>
<xsl:template match="Detail">
<xsl:apply-templates />
<xsl:text>
</xsl:text>
</xsl:template>
<xsl:template match="DetailIndicator">
<xsl:value-of
select="substring(concat(., ' '), 1, 1)"/>
</xsl:template>
<xsl:template match="DetailRecordDate">
<xsl:value-of
select="substring(concat(., ' '), 1, 10)"/>
</xsl:template>
<xsl:template match="Name">
<xsl:value-of
select="substring(concat(., ' '), 1, 50)"/>
</xsl:template>
<xsl:template match="Phone">
<xsl:value-of
select="substring(concat(., ' '), 1, 50)"/>
</xsl:template>
<xsl:template match="Trailer">
<xsl:apply-templates />
<xsl:text>
</xsl:text>
</xsl:template>
<xsl:template match="TrailerIndicator">
<xsl:value-of
select="substring(concat(., ' '), 1, 1)"/>
</xsl:template>
<xsl:template match="TrailerDate">
<xsl:value-of
select="substring(concat(., ' '), 1, 10)"/>
</xsl:template>
<xsl:template match="DetailRecordCount">
<xsl:value-of
select="substring(concat(., ' '), 1, 10)"/>
</xsl:template>
<xsl:template match="TrailerFiller">
<xsl:value-of
select="substring(concat(., ' '), 1, 90)"/>
</xsl:template>
</xsl:stylesheet>
Here, below example shows a part of xslt code for “Name” field which needs to be maintained similarly,for other fields based on their fixed field length value.
Example, for “Name” field, fixed field length value is 50 characters, so, we need to have 50 blank spaces and 50 as field length value maintained as per below highlighted part on XSLT code.
Image 4 : Portion of XSLT mapping to maintained correctly as per field length
Step 3):
SFTP adapter Processing: Final Fixed Length output file created from Step 2) XSLT code placed on the target directory in SFTP through SFTP adapter
Image 5: Receiver SFTP Configuration (example)
Fixed length file got generated at SFTP location configured as below:
Image 6: File Created in SFTP Folder Path
Final Output Fixed Length text file looks like below:
H20210405
D20210405 Deepak Jaiswal 123457890
D20210405 Rajesh Sharma 234578901
D20210405 Lokesh Kumar 345789012
T20210405 0000000003
Image 7 : File Output Notepad++ View
Conclusion:
In this blog post, I demonstrated how to generate a file with fixed length format through SAP CPI integration flow using XSLT mapping.
Hope this blog post will help CPI developers to achieve similar functionality and this can be enhanced more to meet the technical business requirements.
Reference:
https://stackoverflow.com/questions/50332451/how-to-create-an-xsl-to-output-a-fixed-position-text-file
Hi Deepak ,
Thanks for sharing this knowledge and its very useful and informative.
Thanks,
Madhu
Nice blog Deepak
Nice.
Yes XSLT does add some good options to convert flat files and also CSV which is also easy to setup.
I do have to laugh at the part "is to generate fixed length format output file for an end application"
very well elaborated. Thanks for sharing this.
DJ, As always you have come up with another unique technique and this time it's in CPI.
Great blog and information.
Thanks for Sharing.
Hi DJ,
Just wanted to know what changes I need to do to get the output text right justified?
Thanks,
Shashwat
Tried and Found the answer for this:-
select="substring(concat(' ', .),string-length(concat(' ', .))-4)
for a field of length 5.
Hi Deepak, We have a similar requirement wherein we have to use XSLT to convert XML to Text
Below is the XML (it can be assumed that field fixed lengths is already done in mapping through groovy)
<Root>
<Header>
<DC>Header</DC>
<DT> </DT>
<SGC>00000</SGC>
<SC>19091</SC>
</Header>
<Item>
<Indicator> </Indicator>
<Code>4987</Code>
<Flag>1</Flag>
<OF>07F1035</OF>
<SRN> </SRN>
</Item>
<Item>
<DC>item</DC>
<DT>AB</DT>
</Item>
<Tax_Item>
<DC>Tax</DC>
<DT>abc</DT>
<MCO>def</MCO>
<MSC>gh</MSC>
</Tax_Item>
<Trailer>
<DC>Trailer</DC>
<DT>ABC</DT>
<NOR>000000</NOR>
<RNOR>000000</RNOR>
<Filler> </Filler>
</Trailer>
</Root>
We need to convert this using XSLT to Text and below XSLT was used :
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8" indent="no"/>
<xsl:preserve-space elements="*" />
<xsl:template match="/">
<xsl:apply-templates select="*"/>
</xsl:template>
<xsl:template match="Header">
<xsl:value-of select="."/>
<xsl:text> </xsl:text>
</xsl:template>
<xsl:template match="Item">
<xsl:value-of select="."/>
<xsl:text> </xsl:text>
</xsl:template>
<xsl:template match="Tax_Item">
<xsl:value-of select="."/>
<xsl:text> </xsl:text>
</xsl:template>
<xsl:template match="Freight_Item">
<xsl:value-of select="."/>
<xsl:text> </xsl:text>
</xsl:template>
<xsl:template match="Trailer">
<xsl:value-of select="."/>
</xsl:template>
</xsl:stylesheet>
However the out is not appearing in a single line for each Header, Item etc. rather each value is appearing in a separate line
Point to observe is if we use strip space instead of preserve space in XSLT, it does give output but all whitespaces are truncated (Example : <DT> </DT> in Header is truncated)
Preserving white space
Stripping whitespace
Can you please advise on the right course.
Thanks