cancel
Showing results for 
Search instead for 
Did you mean: 

Excel(.xlsx) to .csv converison in SAP PI XI

Former Member
0 Kudos

Hi All,

Basically the requirement is file to file wherein the source structure is Excel format and target structure is .csv format.

Please help me with the conversion in pi7.0. Many Thanks.

Accepted Solutions (0)

Answers (6)

Answers (6)

former_member933886
Discoverer
0 Kudos

You can use the UDF (User Defined Function) XSLT mapping to convert any Excel (.xlsx) file into .csv format in SAP PI/XI.

The steps to use XSLT mapping for the conversion are as follows:

1. Create an Interface in SAP PI/XI with the file adapter.

2. Edit the interface and double-click on the Graphical mapping editor and select XSLT Mapping as the transformation mode.

3. Create the XSLT mapping program. The program should contain the logic to convert the Excel (.xlsx) file into the .csv format.

4. Once the XSLT mapping is complete, save and activate the mapping program.

5. Execute the interface to start the conversion process.

sateesh437
Participant
0 Kudos

Hi Rajesh,

Please find the below blog.

Regards,

Sateesh.

Former Member
0 Kudos

Hi Sateesh,

Could you please Share me the UDF to map source and target. As far Now I have implemented one to one mapping.

Input Payload:

Former Member
0 Kudos

Hi Sateesh,

Now Getting below error in sender comm channel

Time StampStatusDescription
2015-07-06 16:30:50SuccessSend binary file  "storeXL.xlsx" from FTP server "082.163.0033.13320:/homeUser/STORE_DATA/test_SENDER/", size 12004 bytes with QoS EO
2015-07-06 16:30:50ErrorAttempt to process file failed with com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Object not found in lookup of ExceltoXML.
2015-07-06 16:31:00SuccessSend binary file  "storeXL.xlsx" from FTP server "082.163.0033.13320:/homeUser/STORE_DATA/test_SENDER/", size 12004 bytes with QoS EO
2015-07-06 16:31:00ErrorAttempt to process file failed with com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Object not found in lookup of ExceltoXML.
sateesh437
Participant
0 Kudos

Hi Rajesh,

Module name should be <jndi-name> name in ejb.j2ee-engine.xml. Double check did you give the correct name in your module configuration or not.

Regards,

Sateesh.

sateesh437
Participant
0 Kudos

UDF is available in the same link and take source side only one field and that UDF will split the cell data to the target nodes.

check the same thing in the blog.

Former Member
0 Kudos

Hi Sateesh,

I didnt get you " <jndi-name> name in ejb.j2ee-engine.xml".  You mean in Excel_ModuleJavaCode.txt.zip

Below is myconfigurations:

Former Member
0 Kudos

Hi Sateesh,

Shoul we need to do all the steps mentioned in http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e674...

Looks totally complicated and involves basis kind of work.

In the modules mentioned are:

sateesh437
Participant
0 Kudos

This is not a standard Module, here you are creating a custom module and deploying and it will convert the Excel.

Former Member
0 Kudos

Hi Sateesh,

So you mean to say the custom module should be created and implement all the steps mentioned in below link.

Am I right correct me If I'm wrong.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e674...

sateesh437
Participant
0 Kudos

Hi Rajesh,

The above link is for your reference for how to create & deploy a custom module and code should be used from "A Simple approach in Reading Excel File".

Regards,

Sateesh.

Former Member
0 Kudos

Thanks alot Sateesh. Hope so it will work fine and data movements flow smoothly.

I have did all the steps except below link which involves basis support to do it.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e674...

Former Member
0 Kudos

Hi Rajesh,

I think its better to use XSLT mapping....

If u want to work with Excel in a file to file scenario XSLT is a best option.

Regards,

Srikanth Mavuri.

Former Member
0 Kudos

Hi Srikanth,

I'm new to XSLTmap could you please help me with the code for below source(.xlsx) to target(.csv) mapping.

Thanks....!!

Former Member
0 Kudos

Hi Srikanth,

Please check below code so that I can import archive in IR.

<?xml version="1.0" encoding="UTF-8" ?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ns0="

htttp://EXCEL2CSV.com/xi/StoreConversion" xmlns:ns0="htttp://EXCEL2CSV.com/xi/StoreConversion">

<xsl:template match="/">

<ns0:mt_store_receiver>

<xsl:for-each select="ns0:mt_store/RECORD">

<RECORD>

<HEADER>

<xsl:element name="KEY" select ="KEY"/>

<xsl:value-of select="KEY" />

<xsl:element name="WEEK" select ="WEEK"/>

<xsl:value-of select="WEEK" />

<xsl:element name="STORE_CODE" select ="STORE_CODE"/>

<xsl:value-of select="STORE_CODE" />

.

.

.

.

</HEADER>

</RECORD>

</xsl:for-each>

</ns1:mt_store_receiver>

</xsl:template>

</xsl:stylesheet>

Former Member
0 Kudos

Hi Experts,

Please provide your inputs.

Many Thanks......

iaki_vila
Active Contributor
0 Kudos

Hi Rajesh and Suhale,

I don't know if Eng solution will work in PI 7.0.

You have some alternatives more:

Regards.

Former Member
0 Kudos

Hi Inaki,

I have implemented Michal solution blog getting below error in SXMB_MONI- . Please help.

MAPPING DURING EXECUTE ERROR

arkesh_sharma
Active Participant
0 Kudos

Hi Rajesh,

As per my understanding, CallSapAdapter module's position should be in the end. I tried a similar scenario where I used PayloadZipBean with CallSapAdapter and ran into issues.

Also, please note that PayloadZipBean do not work when you are uploading zip files in AL11 directories. There is some encoding issues.

Thanks,

Arkesh

Former Member
0 Kudos

Hi Arkesh,

When I use only CallSapAdapter  module it is fetching the .xlsx through file adapter from FTP without FCC but getting below error in SXMB_MONI:

arkesh_sharma
Active Participant
0 Kudos

Hi Rajesh,

CallSapAdapter is a default module and is part of File Adapter. If you remove it then it may cause problems.

If there is a proper excel file then you either use Java Mapping or XSLT mapping to achieve your requirement. In my case, we achieved Excel to XML conversion using Java Mapping.

There are lot of codes available on the internet which can help you achieve that. You just need to search for it and then put it in the right format acceptable by PI. Unfortunately, I do not have the code with me otherwise would have shared it with you. But we did it by searching and then fixing the bugs in NWDS. You can also use Eclipse for this purpose.

On the target side you can use the FCC and save it as CSV file. That would help you solve the problem on the target side.

Regards,

Arkesh

Former Member
0 Kudos

Hi Arkesh,

Thanks alot the suggestion.

Former Member
0 Kudos

Hi Rajesh,

Please follow the Eng Swee's Blog.

Regards,

Suhale Shaik.

Former Member
0 Kudos

Hi Suhale,

I have implemented Eng solution getting below error. Please help.

arkesh_sharma
Active Participant
0 Kudos

Hi Rajesh,

In the first glance, I can see that it is a File adapter and so there should be a "CallSapAdapter" module used by default. I do not see in your snapshot so I am assuming that it has been removed.

Please include it in your list of modules.

Also, you need to maintain the order of the modules. After performing all conversions by the other modules, please use the CallSapAdapter module at the end.

Thanks,

Arkesh

Former Member
0 Kudos

Hi Arkesh,

Custom_AF_Modules/FormatConversionBean is throwing an error as mentioned abovee.


Now I have used only below modules in processing sequence and no parameter value mentioned at module configuration module.

AF_Modules/PayloadZipBean   1

CallSapAdapter                 3

localejbs/CallSapAdapter    5

Getting below error at sender comm channel:


2015-07-06 15:45:13SuccessTrying to put the message into the send queue.
2015-07-06 15:45:13SuccessMessage successfully put into the queue.
2015-07-06 15:45:13SuccessThe application sent the message asynchronously using connection File_http://sap.com/xi/XI/System. Returning to application.
2015-07-06 15:45:13ErrorAttempt to process file failed with java.lang.NullPointerException


arkesh_sharma
Active Participant
0 Kudos

Please let me know purpose of FormatConversionBean ?

CallSapAdapter should come only once. I can see that you have used twice. Remove the second entry. Its in incorrect format. Also, the order of the modules should be properly maintained.

The CallSapAdapter entry should look something like this -

Former Member
0 Kudos

Hi Arkesh,

On Using above two modules it is fetching the .xlsx through file adapter from FTP without FCC but getting below same error in SXMB_MONI:

Former Member
0 Kudos

Hi Rajesh,

Kindly observe the error log closely.

As per the error log, you have to use "localejbs/Custom_AF_Modules/FormatConversionBean".

Instead you have used "Custom_AF_Modules/FormatConversionBean" in the processing sequence section. The part in the path "localejbs" is missing.

Kindly try that out if it works and let me know.

Kind Regards,

Souvik

Former Member
0 Kudos

Hi Souvik,

On Using "localejbs/Custom_AF_Modules/FormatConversionBean" module in Processing sequence and I didn't mention any parameter Name in Module configuration.

I'm Facing below error in comm channel:

Time StampStatusDescription
2015-07-07 11:32:10SuccessTrying to put the message into the send queue.
2015-07-07 11:32:10SuccessMessage successfully put into the queue.
2015-07-07 11:32:10SuccessThe application sent the message asynchronously using connection File_http://sap.com/xi/XI/System. Returning to application.
2015-07-07 11:32:10ErrorAttempt to process file failed with com.sap.engine.services.jndi.persistent.exceptions.NameNotFoundException: Path to object does not exist at Custom_AF_Modules, the whole lookup name is localejbs/Custom_AF_Modules/FormatConversionBean.
Former Member
0 Kudos

If I mention only localejbs/CallSapAdapter or CallSapAdapter modules.

The ftp is fetching the excel worksheet but unable to view the payload.

In sxmb_moni the file is being doqnlaoded and

MAPPINGEXCEPTION_DURING_EXECUTE is occured

Former Member
0 Kudos

Hi Rajesh,

Please mention the parameter names as well like converter class etc. as suggested by Suhale from Eng Swee's blog. Let me know if it still doesn't work.

Kind Regards,

Souvik