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
295

Hello everyone,

I hope everything is all right and well. In this article, I will show you how to convert an Excel file from XLS format to CSV format. Thanks to Mr. Engswee, I got over the difficult times I went through when I was a junior, thanks to the blogs he shared.

When this service came to our team for the first time, no one in our team had done such a procedure before. At that time, I had done a lot of research and during one of my sleepless nights, I came across his 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. To perform this operation, first, we need to decompress the file in the SFTP sender and then convert this unzipped file to XML format. In this way, the SFTP adapter will be able to transfer our converted data in XML format. We will convert our data in XML format to CSV format on the File adapter and complete the process. We will not use any code or create any ESR objects while performing these operations.

For example, let's say we have a zip-compressed excel file named VideoGames with the file content as follows:

Mustafa_Onur_0-1718811104245.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 the 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-1718811104250.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-1718811104254.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 CSV 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-1718811104258.png

We need message transform beans to convert XML data to CSV 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 CSV format, we need to use the following information under the module tab:

Mustafa_Onur_4-1718811104262.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.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. This means that the system copies the value completely, regardless of how long it takes.

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 names to these objects on the Integrated Configuration side. Remember, we will use the same namespace we gave here in the documentNamespace as well :

Mustafa_Onur_7-1718812045841.png

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

Mustafa_Onur_5-1718811104273.png

Below you can see our file converted to CSV format:

Mustafa_Onur_6-1718811104279.png

Thus, we can easily convert Excel files in zip to CSV format without using any code or creating an ESR object.

Thank you, have a great day!

Labels in this area