Excel To XML Conversion

[Former Member]

Hi All

we have a requirement to read the Excel file from the FTP server & Convert it to XML File so that it can be processed in SAP PI.

Do we have any standard Adapter module bean/Standrad Java Code priovided by SAP, for example in PI7.0 for RFC Lookuop we have a java code provided by SAP, similarly for Dynamic Configuration we have a code snippent provided by SAP.

so do we have any standred module/java code provide by SAP in Pi7.0/Pi7.1 to convert the Excel to XML conversion or we have to go for the custom module to conver the Excel file to XML.

Thanks in Advance.

Thanks

Rinku Gangwani

[former_member200962 · Accepted solution]

Did you check this: /people/sap.user72/blog/2005/07/04/read-excel-instead-of-xml-through-fileadapter

Regards,

Abhishek.

[Former Member · Accepted solution]

Hi Abhishek

Thanks for ur quick respondse.

I already go through this blog and i can write a custom module for Excel to XML conversion.

But my intention is instead of writing ur own module if we have some SAP Standard code then we can use that code to write the Adapter Module.

Thanks

Rinku Gangwani

[former_member200962 · Accepted solution]

There seems to be no Standard Code available....you need to develop the Custom Module as per your requirement.

[jyothi_anagani · Accepted solution]

Hi Rinku,

There is no standard code for this. If you tell the requirement like How the data will be in the Excel sheet then I will Provide the Code. I have developed a module to read the Excel FIle. It is very simple. You just tell your requirement.

Thanks.

[Shabarish_Nair · Accepted solution]

Rinku,

if you are looking for any standard code from SAP on this respect unfortunately there are none.

Why dont you refer this blog - /people/shabarish.vijayakumar/blog/2009/04/05/excel-files--how-to-handle-them-in-sap-xipi-the-alternatives

in it i have mentioned an api which can be used and is actually very easy to use to read/write excel files.

You will have to include the logic into a module code.

~~ Shabz

[Former Member · Accepted solution]

Hi Jyoti

i have write the adapter module code, n it is also working.

But the issue is i have written the code for the FTP adapter, i am findijng a new thing when i am testingt he FTP adapter.

As per my knowledge if we have any custom module then it should be called before standard module (CallSapAdapter).

but when i am trying to test the Adapter Module using the FTP Adapter then the Adapter is Connecting to the FTP server using the FTP Parameters provided in the FTP Communication Channel and after that it is going to the Custom Adapter Module & start the processing.

Now what i want, is it possible to connect to the FTP server first using the Custom module and after that it should process the file, because what is happening when we configured the adapter we have to mention the directory & file name so when i am mentioning the file name is the FIleName Parameter of the Adapter it is pciking that file & deleting the file, send message to IE and the same input file is also used for the module.

Thanks

Rinku Gangwani

[jyothi_anagani · Accepted solution]

Hi Rinku,

The process Flow will be like this

  Ftp Parameters will be executed to pick the File
  Then Your Custom module will be called( There you have to write your logic to *Convert ExceL File to XML*)
  Then CallSapAdapter Module is called to send the xml message from Adapter Engine to Integration Engine.
  Now all the pipeline steps will be executed using the XML message that you created.

I think now you have understood the Process flow.

Let me know for any other doubts.

Thanks.

[Former Member · Accepted solution]

Hi Jyoti

yes exactly the same tjhingis happening in my case.

so is it possible to connect to the FTP server using the custom moduule.

Many Thanks

Rinku Gangwani

[jyothi_anagani · Accepted solution]

Hi Rinku,

Do you again want to connect to the FTP server back after picking the file from the FTP Server using FTP Connection parameters.

Why do you want to connect to the FTP Server ffrom Custom module when you already have the same file to be processed.

What is the file name you are giving in File adapter?

It has to be like input.xls

Thanks.

[prateek · Accepted solution]

Could you explain how you want your file to be picked?

is it possible to connect to the FTP server first using the Custom module and after that it should process the file

I don't think this would be possible with modules. You need to have your own adapter in this case.

Regards,

Prateek

[Former Member · Accepted solution]

Hi Jyoti

could you please tell me how we can call the getMessageProperty Method in PI7.1

public ModuleData process(ModuleContext moduleContext, ModuleData inputModuleData)

throws ModuleException {

try

{

Object obj = null;

Message msg = null;

msg = (Message) obj;

Hashtable mp = (Hashtable)

inputModuleData.getSupplementalData("module.parameters");

if (mp != null) fileName = (String)mp.get("FileName");

Channel channel = new Channel(moduleContext.getChannelID());

String FileNameAvail = msg.getMessageProperty("http://sap.com/xi/XI/System/File","FileName");

msg.setMessageProperty("http://sap.com/xi/XI/System/File","FileName", FileName);

}

catch(Exception ex)

{

ModuleException me = new ModuleException(ex);

throw me;

}

return inputModuleData;

}

how we can call this method in PI7.1.

because in PI7.10 the the setMessageProperty method was called as

public void setMessageProperty(java.lang.String namespace,

java.lang.String name,

java.lang.String value)

but in PI7.1 it changes to

public void setMessageProperty(MessagePropertyKey arg0,

java.lang.String name)

could any one of you please tell me how i can get the MessagePropetyKey Value.

Thanks

Rinku Gangwani

[jyothi_anagani · Accepted solution]

Hi Rinku,

For setMessageProperty you have two types.

void 	setMessageProperty(MessagePropertyKey key, String value)

          Sets an additional message field, if supported by the Message protocol implementation.

 void 	setMessageProperty(String namespace, String name, String value)

          Deprecated. Use setMessageProperty(MessagePropertyKey, String), instead.

For getMessageProperty

      String   getMessageProperty(MessagePropertyKey key)

          Returns the value of an additional message property, in case that such an value exists.

     String   getMessageProperty(String namespace, String name)

          Deprecated. Use getMessageProperty(MessagePropertyKey), instead.

You can use these two . Try with these.

Thanks.

[Former Member · Accepted solution]

Hi

I have write the following line of code.

public ModuleData process(ModuleContext moduleContext,

ModuleData inputModuleData)

throws ModuleException {

try

{

String cid = null;

Channel channel = null;

String fileName = null;

com.sap.engine.interfaces.messaging.api.Message msg = (Message) inputModuleData.getPrincipalData();

XMLPayload xmlpayload = msg.getDocument();

Hashtable mp = (Hashtable)inputModuleData.getSupplementalData("module.parameters");

if (mp != null) fileName = (String)mp.get("FileName");

cid = moduleContext.getChannelID();

CPALookupManager lm = CPAFactory.getInstance().getLookupManager();

channel = (Channel) lm.getCPAObject(CPAObjectType.CHANNEL, cid);

String filename = channel.getValueAsString("file.sourceFileName");

MessagePropertyKey mpk = null;

String str2 = msg.getMessageProperty(mpk);

}

catch(Exception e)

{

e.printStackTrace();

throw new ModuleException("Error ",e);

}

return inputModuleData;

}

but when i am trying to print the value of str2 in the audit log it is UNDEFINED.

Thanks

Rinku Gangwani

[jyothi_anagani · Accepted solution]

Hi Rinku,

MessagePropertyKey you have taken as null, May be because of that you are getting like that.

Here MessagePropertyKey is The key (consisting of name and namespace) of the property to set.

getMessageProperty

String getMessageProperty(MessagePropertyKey key)

    Returns the value of an additional message property, in case that such an value exists.

    Parameters:
        key - The key (consisting of name and namespace) of the property to set. 
    Returns:
        The value for the given namespace/name combination, or null, when no such value exists.

MessagePropertyKey is a class. You have defined it like 'null'.

Define like this.

MessagePropertyKey mpk = new MessagePropertyKey(String propertyName, String propertyNamespace)

Try Like this and Let me know for any isues

Thanks.

[former_member187339]

Hi Rinku,

As of now we dont have any Standard module which does this job in SAP PIO.

You need to write your own custom module (using apis like Apache POI etc.). As a start go look through the wiki section.

Regards

Suraj

[Former Member]

so do we have any standred module/java code provide by SAP in Pi7.0/Pi7.1 to convert the Excel to XML conversion or we have to go for the custom module to conver the Excel file to XML.

As far as I know there are no standard modules for this purpose.

This blog gives a good view of all the other options , incase you have not come across

/people/shabarish.vijayakumar/blog/2009/04/05/excel-files--how-to-handle-them-in-sap-xipi-the-alternatives

Regards,

Manjusha.