Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
-Mustafa_Onur-
Explorer
451

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:

Mustafa_Onur_0-1718817987294.png

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:

https://community.sap.com/t5/enterprise-resource-planning-blogs-by-members/unzip-and-send-a-zip-file...

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 :

https://community.sap.com/t5/technology-blogs-by-members/formatconversionbean-one-bean-to-rule-them-...

 First of all, in the SFTP sender channel, under the advanced tab, ASMA (Set Adapter Specific Message Attributes) and File Name boxes are selected :

Mustafa_Onur_1-1718817987300.png

Then, by entering the following module and parameters in the module tab, we perform unzip and format conversion on the SFTP sender channel:

Mustafa_Onur_2-1718817987305.png

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 :

  • We enter the message type name in the documentName field. Since there is no ESR object in our service, we use dummy names here.
  • We enter the namespace name in the documentNamespace field. Since there is no ESR object in our service, we use dummy names here. Remember, we will use the namespace we gave here in the Integrated configuration as well.
  • If we used fromConfiguration in the processFieldNames section, we define the field names.
  • In the fieldNames section, we enter the field names in the Excel file here.
  • In the sheetName section, we enter the sheet name in the Excel file.

 

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:

Mustafa_Onur_3-1718817987309.png

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 :

https://community.sap.com/t5/technology-blogs-by-members/fcc-parameter-reference-for-messagetransfor...

In order to convert XML formats to text format, we need to use the following information under the module tab:

Mustafa_Onur_4-1718817987314.png

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 :

  • We used the value 'nl' for the xml.endSeperator field. nl stands for new line. Quotation marks must be used.
  • In the xml.fieldSeparator section, we specify which character will separate the fields in the CSV file. Here we wanted our areas to be separated by semicolon.
  • We entered the Ignore value for the xml.fixedLengthTooShortHandling field. Means that the system copies the value completely, regardless of how long it takes.
  • The field names given in the xml.field.Names parameter must be the same as the field names in the XLS file
  • If we set the xml.processFieldNames parameter fromConfiguration, we have to use the xml.field.Names parameter.
  • During this process, never use ASMA settings on the receiver side.

 

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 :

 

Mustafa_Onur_5-1718817987317.png

After these steps, our text file will be created without any problems.

Mustafa_Onur_6-1718817987328.png

Below you can see our file converted to text format:

Mustafa_Onur_7-1718817987331.png

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!

 

 

 

 

Labels in this area