Mail to IDoc Scenario

[manikandan_shanmugam3]

Dear All,

Scenario: Mail to IDoc Scenario.

We are getting an Excel attachment through mail, PI have to read the attached Excel file content (Row 1 is field name and row 2 is data for that field), need to fetch the relevant field data from the Excel file for the field and map to the IDoc.

Kindly suggest me Idea and links to do the same,

Regards,

Manikandan

[MichalKrawczyk]

hi,

a few ways:

1.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2010/12/06/pixi-reading-ms-excels-xlsx-and-x...

use payload swap bean to use the excel's payload in the mapping as per my blog

2. you can also receive the attachment and in the operation mapping check: use attachment

and just get the excel attachment form the mapping (UDF) and map it to IDOC

Regards,

Michal Krawczyk

[manikandan_shanmugam3]

HI Michal,

Thanks for your reply.

I forgot to add one more point that, Mail has two attachments, one is Excel and another one is pdf file but we have to read only Excel file.

I have gone through your blog and wanted to know that whether this " parameters keyName - content type and keyValue - application/xml;name="xi/worksheets/sheet1.xml" if we'd like to read the first worksheet. We can achieve this by using parameters keyName - content type and keyValue - application/xml;name="xi/worksheets/sheet1.xml" if we'd like to read the first worksheet." will swap only Excel file?

Also want to know that after swapping the attachment , Excel file will become as a payload? if yes then this will be in XML format for still in Excel format? 

Regards,

Manikandan

[MichalKrawczyk]

Hi,

>>> if we'd like to read the first worksheet." will swap only Excel file?

yes - once you unzip it it will only read the sheet1.xml

but please check if this file (unzip locally) has all the data you need first

>>>if yes then this will be in XML format for still in Excel format?

XML - you will receive the sheet1.xml only in the payload (you can see the file by unzipping the excel yourself)

Regards,

Michal Krawczyk 

[manikandan_shanmugam3]

Hi Michal,

Mail adapter Picks the Mail perfectly and Swapbean module also working fine. since we are using "xls" format , i have not used the module for Unzip the file but attachment comes to the integration engine as a Excel file not as a XML. can you guide me the way forward.

shall i write a Java Mapping to read the Excel file when it comes to IE?

Regards,

Manikandan

[MichalKrawczyk]

Hi,

>>shall i write a Java Mapping to read the Excel file when it comes to IE?

yes if you have xsl then you need to write a java mapping to get the data

there are many apis to handle that in java like:

http://jexcelapi.sourceforge.net/

Regards,

Michal Krawczyk

[baskar_gopalakrishnan2]

If you need code samples for the java mapping you can use below sample links

http://www.vogella.com/articles/JavaExcel/article.html

http://sanjaal.com/java/100/java-file/reading-excel-file-using-java-and-apache-poi/

You might also use Apache POI api.