
Hello everyone,
I hope everything is all right and well. In my previous blog, I shared with you how to convert a zip-compressed XLS file to CSV format without using any code or creating an ESR object.
In this article, I will show you how to convert an Excel file in XLS format to Text format.
In my studies during my junior period, I came across Mr. @engswee's blogs about Format Conversion Bean and Message Transform Bean. I managed to provide this service with the information I obtained mainly from his blogs.
As you know, we cannot send compressed files or Excel files via PO. In order to perform this process, we must first unzip the file compressed on the SFTP sender, and then convert this unzipped file into text format. In this way, the SFTP adapter will be able to transmit our data converted to XML format. We will convert our data in XML format to text format on the File adapter and complete the process. Again, we will not use any code or create any ESR object while doing these operations.
For example, let's say we have a zip-compressed file named VideoGames with the file content as follows:
First, let's unzip the compressed file and convert the resulting Excel file to XML format.
You can find detailed information on how to unzip files with Zip extension in one of my previous blogs:
After the unzip process, we will transmit our Excel file in XML format. To do this, we will use Format Conversion Bean. You can find detailed technical information about this on Mr. @engswee's blog :
First of all, in the SFTP sender channel, under the advanced tab, ASMA (Set Adapter Specific Message Attributes) and File Name boxes are selected :
Then, by entering the following module and parameters in the module tab, we perform unzip and format conversion on the SFTP sender channel:
Below you can find the modules and parameters we used:
1 AF_Modules/PayloadZipBean Local Enterprise Bean zip
2 Custom_AF_Modules/FormatConversionBean Local Enterprise Bean 1
3 localejbs/CallSapAdapter Local Enterprise Bean entry
1 converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
1 documentName MT_Games
1 documentNamespace http://namespace/TEMPTEST
1 fieldNames Name,Genre,NotableCharacters
1 processFieldNames fromConfiguration
1 sheetName Games
zip zip.filenameKey contentType
zip zip.mode unzip
After the procedures here, our file will be converted to XML format.
There are some points I want to mention here :
In order to convert our XML data into text format, the following changes must be made on the File receiver channel. First, we write the location where the file will be saved and the file name and extension:
We need message transform beans to convert XML data to text format. You can find detailed technical information about this on Mr. Engswee's blog :
In order to convert XML formats to text format, we need to use the following information under the module tab:
Below you can find the modules and parameters we used:
1 localejbs/AF_Modules/MessageTransformBean Local Enterprise Bean XML2Plain
2 CallSapAdapter Local Enterprise Bean 0
XML2Plain Transform.Class com.sap.aii.messaging.adapter.Conversion
XML2Plain Transform.ContentType text/plain;charset=ISO-8859-1
XML2Plain xml.addHeaderLine 0
XML2Plain xml.conversionType SimpleXML2Plain
XML2Plain xml.endSeperator 'nl'
XML2Plain xml.fieldSeparator ;
XML2Plain xml.field.Names Name,Genre,NotableCharacters
XML2Plain xml.processFieldNames fromConfiguration
XML2Plain xml.fixedLengthTooShortHandling Ignore
There are some points I want to mention here :
If you remember, we were going to do these operations without creating any ESR object. The important parts here are the Interface and Namespace names. Even if you have not created the objects, you can give dummy names to these objects in the Integrated Configuration side. Remember, we will use the same namespace we gave here in the documentNamespace :
After these steps, our text file will be created without any problems.
Below you can see our file converted to text format:
Thus, we can easily convert Excel files in zip-to-text format without using any code or creating an ESR object.
Thank you, have a great day!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 |