Hi,

I use IS 6.1 and I am having problem handling Excel email attachment. I scanned through the posts on this forum but could not find the hint I am looking for.

I first defined a file poller that retrieves an Excel file from my file system and I wrote a service that takes the “ffdata” object as input and then transform the Excel file into a java arraylist using the POI open source libraries. Another step then converts the arraylist into a webMethods document for further processing. This works fine.

Then I tried doing the same by replacing the file polling by email polling (receiving an Excel file through an email). So I defined an email poller that scans my pop3 mailbox and set the following properties:

• Invoke service for each part of multipart message = yes
• Include email headers when passing message to content handler = no

To test this process, I simply send out an email from my Outlook client, containing a single Excel file attachment and my fully qualified service name as subject. I made no change to my existing flow service that still takes a “ffdata” object as input parameter.

However my flow service does not work anymore in this situation, it always crashes in my java code while manipulating the “ffdata” input stream to transform it into a java arraylist: “java.io.IOException: Unable to read entire header; 217 bytes read; expected 512 byte” (I debugged my java code that fails on the line “wb = new HSSFWorkbook(fileStream);”. I suppose that my java code is correct as it works fine when getting the input stream directly from the webMethods file poller.

I also tried to create a MIME object, get the number of parts, iterate on each part and then get the part body contents before invoking my Excel to java conversion service but this does not work any better.

I also created a flow service that sends out my Excel file using the webMethods smtp API but no change, still same issues…

Does somebody have an idea what the cause of my problem could be? I attached my package to this post.

Thanks

Philippe
PS : in order to be able to upload my package on this forum (size limitation) I had to remove the external jars from my code/jars package directory. If you want to run it, you’ll need (you can get them from the jakarta web site):
log4j-1.2.9.jar
poi-2.5.1-final-20040804.jar
poi-contrib-2.5.1-final-20040804.jar
poi-scratchpad-2.5.1-final-20040804.jar

Hi,

I believe you must be using email port for receiving email. I am right?

what do you get in the email attachment, in a service that processes these attachment? Is it possible for you to receive data as CSV or something there, that would be easy.

yes processing a CSV file (setting extenstion with .txt or csv)than .xls is easier and it should work fine.

HTH,
RMG.

Hi Chiraq,

you are correct in that I am receiving my email through an email port. Unfortunately I do receive Excel files. You are also correct RMG to hint at CSV files as these Excel files do not contain any formulas or special formatting and are actually CSV files. However I am assuming that I could not get my hundreds of clients to change their processes and therefore trying to find a solution to manipulate these binary Excel files.

Philippe
PS: I have no problem manipulating CSV attachments through the MIME apis (I just tested it successfully), my problem is only with Excel files

Hi Phillipe,

I have seen one smart use of Excel with Macros, that generates an email with a CSV attachment at one of my client in europe.

In our case, the small customers (who do not have EDI Infrastructure) used to be sending orders by filling in the excelsheet and clicking a button, to run the macro.

In your case, if it is an application is generating the data as EXCEL SHEET, it may be possible to configure it to send data as CSV. That may save you the trouble of building custom handler.

Second Option would be to see if email can reach you as Object Type (I am not sure what exact MIME type that needs to be), or check up if there is any existing MIME type that can allow you to receive binary data.

If nothing else, what you can do is try and see if sender can encode excel binary into BASE64, and send it as Text. Then you can convert Base64 Text to binary, and get your excelsheet back.

Hi Chiraq,

Thanks for your response. Actually I do not believe that I would be able to get the clients to modify their programs, but I’ll keep your suggestions in mind.

I just discovered that there are some posts on this forum regarding custom handler, an ezine and a sample Wm Content handler. I will now look into that and try to create my own application/vnd.ms-excel content handler and register it in place of the existing one.

Thanks

Philippe

Hi all,

A question regarding the webMethods content handlers:

I found the code for the webMethods flat file handler (IntegrationServer\packages\WmFlatFile\code\classes\com\wm\ff\net\ContentHandler*_FlatFile.class) but have been unable to locate the default content handler. Would someone know the following:

• what are the content types currently taken in charge by Integration Server?
• for a given content type, what are the input parameters to define for the invoked flow service?
• is there a way to tell IS to process a given content type using one of its own predefined content handler (eg I received *.fic files that are actually standard CSV files) ?

For those interested, I discovered from the Advantage site ([url=“http://advantage.webmethodas.com/cgi-bin/advantage/main.jsp?w=0&targChanId=knowledgebase&oid=1612063554”]http://advantage.webmethodas.com/cgi-bin/advantage/main.jsp?w=0&targChanId=knowledgebase&oid=1612063554[/url]) that the tn.ff.contenttypes properties in the file IntegrationServer\packages\WmTN\config\properties.cnf allows you to specify content types to be processed as flat file by IS but this does not seem to work for me (I suppose that this is only applicable for files coming through TN and not directly to IS)

Thanks

Philippe

I have successfully managed to process my Excel file. Below are the details of the tests I ran to go there

TestConfiguration
• Outlook email client to send out email
• Email poller port configured in IntegrationServer to pop the mailbox with « Invoke service for each part of multipart message = Yes », « Include email headers when passing message to content handler = No » and « Email body contains URL encoded input parameters = No »
• Flow Service to be invoked by the email poller upon reception of a new email. The flow service contains a number of steps such as getTransportInfo, TracePipeline, streamToBytes, … in order to analyse the runtime contents for each test case

TestCase 1
Email with text only body (no attachment) sent out in raw text format from Outlook client with subject = fully qualified name of the flow service to invoke
TestResult
Pipeline is empty except for a ffdata InputStream input parameter, which after conversion (pub.io:streamToBytes + pub.string:bytesToString) gives out the original email body text contents. There is no MIME object to handle in this situation

TestCase 2
The same email with text only body (no attachment) sent out in html format from Outlook client with subject = fully qualified name of the flow service to invoke
TestResult
The service gets invoked twice :
• The first time with the same data as above (body text within ffdata InputStream, no MIME object) except for an additional transport/email/contenttype parameter (output from the getTransportInfo API) set to text/plain in the transportInfo structure (previously not set)
• The second time with a contenttype set to text/html and the html version of the email text body is contained within the contentStream InputStream input parameter

TestCase 3
Email with text body and a xml file attachment sent out in raw text format from Outlook client
TestResult
The service gets invoked twice :
• The first time with the ffdata InputStream containing my email text body and a content type set to text/plain. No MIME object here either
• The second time a node (com.wm.lang.xml.Document) input parameter which gives out (pub.xml:xmlNodeToDocument + pub.xml:documentToXMLString) the attached xml file contents and a filename string input parameter contains the name of the attached xml file. The content type is set to text/xml. Still no MIME object.

TestCase 4
Email with text body and an Excel file attachment sent out in raw text format from Outlook client
TestResult
The service gets invoked twice :
• The first time with the ffdata InputStream containing my email text body and a content type set to text/plain. No MIME object here either
• The second time the contenttype is set to application/vnd.ms-excel, a filename string input parameter contains the name of the attached Excel file and a contentStream InputStream input parameter

Conclusions
• When your service gets invoked for each part, IS takes care of passing each MIME part to the service, so no need to do anything relative to MIME in this situation
• No need to write any custom content handler, IS will always invoke your service with the received attachement as input parameter (or so it seems to me from those tests). The only trick is to know which input parameter name and type to use for your invoked flow service, but this can be found out through the highly useful TracePipeline API. Sure you might need your own piece of code to manipulate non text data contents (such as Excel) but I would not call that a custom content handler (as this is not what the default webMethods content handler

Hi Philippe,

I am not sure if you noticed, that Mail Port works in LIFO manner just in case.

Philippe,

Thanks for sharing detailed explanation.

certainly it helps others.

Some additions to my previous post

TestCase 5
Email in HTML format with a file attachment (Excel in my case)
TestResult
The service is invoked twice:

• The first time on the email body part itself (I mean the body text, not the attachment) with a content-type set to “multipart/alternative” and its content can be retrieved from the “contentStream” input parameter. This InputStream object contains then both the text version and the html version of the body text (and the text version contains some additional ascii characters…).
• The second time (same as for testcase 4) with a content-type set to application/vnd.ms-excel and the attachment contents to be retrieved from the contentStream InputStream object (input parameter of the flow service) and the attachment file name retrieved from the transport/email/filename parameter

Which input parameter to use
Whatever the content-type of the email part passed to the service, the part contents are always available through the transport/email/content InputStream parameter. So no need to bother with ffdata/contentStream/node input parameters, simply get the contents from this parameter (in the case of a xml file attachment you’ll need to convert “content” with XmlStringToXmlNode + xmlNodeToDocument + documentToXmlString in order to get your xml data). As a consequence, your service does not need any input parameter in its signature, it gets all required objects from the pub.flow:getTransportInfo

Email in HTML format
I noted the following behaviour:

1. when you send out an email in html format with no attachment, you receive two parts:

• one with no content type set and that contains the raw email body text
• one with content type set to text/html and that contains the html version of the email body text

2. when you send out an email in html format with a file attachment, you receive two parts:

• one with content type set to multipart/alternative and that contains the raw email body text AND the html version of the email body text, both being separated by the MIME boundary (such as ------=_NextPart_001_0034_01C52F89.5B2BCF70)
• one with content type set to your file type and that contains the file contents attachment

Philippe

hi
i’m a newbie into webMethods
i’ve to read a excel file from a email attachment
could you plz tell me how to write a flow service
were there any samples to get to know how to work on these

thanks in advance
asok

i’ve the same problem, to read a incoming email which will have a fixed excel sheet as a attachment, which service i’ve to invoke through email port

Hi
I’ve to receive subject name from the incoming mail
i.e., what ever emails i recieve i’ve to get the subject name from it

your early help is appreciated
Thanks
Asok

I assume that you have configured Email port and given receiving servicename to invoke when a email arrives.

Did you if you can extract that info using getTransportInfo service from the pipeline.Put a savepipeline under that service and later restore it and make sure pipeline have the subject info.

HTH,
RMG

I’ve configured the email port and given receiving servicename to invoke when a email arrives.
i’m using
1)pub.client:smtp
2)pub.flow:getTransportInfo
3)pub.flow:savePipelineToFile
4)pub.flow:restorePipelineFromFile

I’m able to get only the attachment and the transport status

The transport info contains the needed information like subject and datereceived, but it is not giving the output

Your early help is appreciated

Thanks,
Ashok

Hello Everyone,
I do have a typical scenerio where I have to add two attachments to the email . we have configured the Smtp port and I created the the attachment ( which is an XML file ). Now partner wants us to sign the XML message and send that signature file as another attachment. So the message will be a MULTIPART message. My problem is how do I create a Signature file for the XML document and send that as a seperate file to the Partner? Please let me know

Thanks for your support
Kris

I´m having problems to read a .txt [SIZE=-1][COLOR=Black]attached file. [/color]

Any tips about what services to use?

[SIZE=-1]Thnks
[/size][/SIZE]

Hi PMO,

Do you mind attaching the package since you have this working? Also, any related files in order to process it successfully would be great. Thanks.

Hi,

Is there any way to read an email attachment without configuring the email port configuration.

I want to know how to write a service that will connect to my email box and read the attachment and covert that in to document.

Waiting for a quick response.

Regards,
Amrendra

Pls review this useful article too…

[URL]wmusers.com

HTH,
RMG

Hi PMO,

Can you send your package to wwwdotnada@gmail.com. It would be better to assist you if I see what you have thus far. Are the excel files you’re reading unstructured (ie, graphics, macros, etc.)?