How to process an excel file attached to an email

[Former Member]

Hi Folks,

We have a scenario to process excel file (Consists of about 30 columns), which is attached to an email through PI system and send that data to the receiving system in file format. We are on PI 7.11.

I have searched in SDN for this , but got link for only reading a xml file attachment.

Any inputs regarding this are highly appreciated.

Thanks in advance,

Ramesh

[former_member190624]

Hi Ramesh,

As Amit said , you can right a adapter module for reading excel sheet. Below link explained how to read excel sheet using adapter module.

SAPTechnical.COM - XI - Step-by-step guide to develop Adapter Module to read Excel file.

2nd option will be using Java mapping , below link will explain about Java mapping

Java Mapping for PI 7.1 using Abstract transformation class

Thanks

Hari. 

[Former Member]

Well, i do not know clearly how to help you solve your problem, and as you are a teacher, i believe you alway work with Word or Excel file, so here i can do my litter contribution..If you are faced with MS file lost someday, here professional MS office file recovery tool can be a right-hand man of u!

[Former Member]

Hi Hareesh,

Thanks for your reply. My main concern is for the conversion of Excel sheet data attached to the Mail into XML format. Later I wanted to do some mapping and then send the data to a file

[gagandeep_batra]

Hi Ramesh

First convert the data to xml format.

then create two receiver one for mail and second for file, add two mapping for that and at receiver side of email you can use payloadswap bean for converting as attachment

& for file you can use second mapping

Regards

Gb

[Former Member]

Hi,

Actually what is my query is.. I am getting excel as attachemnt in a mail. I want to read that excel sheet from the mail and need to convert that excel into xml...

In excel i am getting data in rows and coloumns...

Thanks,

Ramesh...

[Former Member]

Hello,

U have to write custom code (AM) to convert excel sheet data into xml

u can check this alsio

Thanks

Amit Srivastava

[Former Member]

Hi Ramesh

The easiest design for this will be writing an java map which will do the following

1. Read the attachment.

2. read the excel. You can use JEXCEL api for excel reading.

3. Populate the flat file structure file in java mapping.

So you don't need to create the xml structure for inbound interface and also don't need any content conversion in receiver side.

Check these blogs

1. Reading attachment

2. Read excel

Excel and Java - Read and Write Excel with Java - Tutorial

[former_member184720]

Hi Ramesh - What do you mean by file format?

And have a look at the below blog for handling email attachments..

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/04/02/creating-file-name-from-mail-atta...