Hitachi Vantara Pentaho Community Forums
Results 1 to 29 of 29

Thread: Read an unknown excel file as an email attachment

  1. #1

    Default Read an unknown excel file as an email attachment

    Hi,

    I want to arrange a system where I will recieve mails and then will check their attachments (mostly Excel files). As a result I will send another email, or anyway, execute another action, depending on which is the value of some concrete cell in the Excel file. The point is that starting the transformation (after "Get emails from POP" step) with "Excel Input" step force me to specify the name of this Excel file I will analyze cell by cell (e.g), and I don´t know the name of that incoming file...

    What can I do in order to implement this process?

    Thanks in advanced!

    Cheers,

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    If you and your data supplier can't agree on a certain filename, you'll have to ensure that each mail goes to a distinct target folder.
    If you know the target folder, you should be able to determine the XLS filename.
    So long, and thanks for all the fish.

  3. #3

    Default

    Quote Originally Posted by marabu View Post
    If you and your data supplier can't agree on a certain filename, you'll have to ensure that each mail goes to a distinct target folder.

    Thanks
    Marabú for your answer. Sorry, but I don´t get the point. Why? Which is the relationship between the name of the file and target folders?

    Quote Originally Posted by marabu View Post
    If you know the target folder, you should be able to determine the XLS filename.
    Same question...

    Thanks in advanced!

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    My point is, if you know the folder where to look for an unknown file you can use step Get-File-Names to determine the filename.

  5. #5
    Join Date
    Nov 2013
    Posts
    382

    Default

    Get email. Save the file(s) to whatever folder you like. With GetFileNames you get the file names. Execute a transaction for each input row (filename). Open with Excel step and process it. Delete the file (or move it to a Done folder).

    Edited. You can also process all files on the folder at once using a regexp. A sort unique at the end will allow you to move/delete the file.
    Last edited by DepButi; 11-20-2017 at 12:14 PM.

  6. #6

    Default

    Ok, thanks Marabu for your help!

  7. #7

    Default

    Quote Originally Posted by DepButi View Post
    Get email. Save the file(s) to whatever folder you like. With GetFileNames you get the file names. Execute a transaction for each input row (filename). Open with Excel step and process it. Delete the file (or move it to a Done folder).

    Edited. You can also process all files on the folder at once using a regexp. A sort unique at the end will allow you to move/delete the file.
    Thanks DepButi!

    A part from that, the transaction/operation I want to do would be based in a kind of OCR: maybe reading directly the email´s body message and if there´s a concrete word there, send the attachment to a concrete folder. Otherwise, if there´s another word, send the attachment to another folder, and so on...What can I use to search a word in an email (or in an Excel file). Is it posible without using User Defined Java Class or without JavaScript?

    Thanks in advanced!

  8. #8
    Join Date
    Nov 2013
    Posts
    382

    Default

    You can use a RegExp Evaluation step to search the desired text on your data. For an excel file you will need to decide in which column (cell) it must be applied.

    Some kind of knowledge over the structure of the received data will be needed.

    Could you post some examples of possible input and desired word search?

  9. #9

    Default

    Quote Originally Posted by DepButi View Post
    For an excel file you will need to decide in which column (cell) it must be applied.

    How can I do it? Which step? Sorry but I´m quite lost...


    Quote Originally Posted by DepButi View Post
    Some kind of knowledge over the structure of the received data will be needed.

    Could you post some examples of possible input and desired word search?
    E.g, receiving an standard email where the words we are looking for are "loan" or "mortgage". We need to identify them in the email´s body and if it´s "loan", send the attachment included to the folder "loan" and if there´s the word "mortgage" included, we need to send the attachment to the folder called "mortgage". This could be an eventual situation that we will find.

    Thanks in advanced!

  10. #10
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by Peptaho_BCN
    How can I do it? Which step? Sorry but I´m quite lost...
    I mean, you have to compare one field in your input row with the word you are looking for. Every excel row will be one row on your pie. Every cell will be a field in your pipe row.
    Do you know in which cell (column) will be the text?
    If you do, no problem, compare the field with the desired word using a Regex Step.
    If you don't ... you will have to compare every single field (i.e. as many regex steps as columns you want to consider) ... or concatenate all fields (cells) into one single field.


    E.g, receiving an standard email where the words we are looking for are "loan" or "mortgage". We need to identify them in the email´s body and if it´s "loan", send the attachment included to the folder "loan" and if there´s the word "mortgage" included, we need to send the attachment to the folder called "mortgage". This could be an eventual situation that we will find.
    That's easy, simply compare the body field using a regex step.

    Maybe you could post the job/transformation (reduced to its minimum expression) as far as you have it now so we can take a look at what you have done ..

    PS. Tens una mica de lio, oi?

  11. #11
    Join Date
    Nov 2013
    Posts
    382

    Default

    I have been testing a little while ... and cannot find a way to get body+attachment at the same time, maybe someone can enlighten us!

    You can use a JOB get mail step to get all attachments + a transformation with an Excel input step to process the attachments.
    You can use a TRANSFORMATION get mail step to get the body and process it.

    But cannot imagine how to get both at the same time

  12. #12

    Default

    Quote Originally Posted by DepButi View Post
    PS. Tens una mica de lio, oi?
    Quote Originally Posted by DepButi View Post


    Pufff...molt! :S



    Quote Originally Posted by DepButi View Post
    That's easy, simply compare the body field using a regex step.

    So should I fix the field "Fields to evaluate" as "body field"? I cannot fix any value with the drop-down list, it´s empty. And btw, it is the first time I heard about Regex Evaluation :S

  13. #13

    Default

    Maybe only checking the email body will be enough!

  14. #14
    Join Date
    Nov 2013
    Posts
    382

    Default

    Start with this example, try to understand every single step ... there are plenty of tricks there.

    On the transformation there are two diferent pipes, one deals with the attachments coming from the job. The other reads the mail messages.
    Attached Files Attached Files

  15. #15
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by Peptaho_BCN View Post
    Maybe only checking the email body will be enough!
    As far as I understand and just as a simple example:

    If the body contains the word LOAN move the attachment to some_parent_folder/LOANS

    Well, we can check the body, detect the word LOAN and ... and now we don't have the attachment anywhere so we cannot move it!. Or we have the attachment in a folder along with a dozen attachments, there is no way to match a specific attachment to a specific body!

  16. #16

    Default

    Quote Originally Posted by DepButi View Post
    Start with this example, try to understand every single step ... there are plenty of tricks there.

    On the transformation there are two diferent pipes, one deals with the attachments coming from the job. The other reads the mail messages.
    OMG! Definitely I missed many many things Depbuti! I will try to arrange some workflow with this excellent help you gave me!

    Moltíssimes gràcies company!

  17. #17

    Default

    Quote Originally Posted by DepButi View Post
    As far as I understand and just as a simple example:

    If the body contains the word LOAN move the attachment to some_parent_folder/LOANS

    Well, we can check the body, detect the word LOAN and ... and now we don't have the attachment anywhere so we cannot move it!. Or we have the attachment in a folder along with a dozen attachments, there is no way to match a specific attachment to a specific body!
    I just need to check the body, check if there is the word LOAN, and then, move the attachment included in that mail to a folder called LOAN, whatever it is this attachment, without matching them, no worries

  18. #18
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by Peptaho_BCN View Post
    I just need to check the body, check if there is the word LOAN, and then, move the attachment included in that mail to a folder called LOAN, whatever it is this attachment, without matching them, no worries
    Let me know how do you do it ... I will not be able to do so! When reading the body there is no way to have the attachment. While reading the attachment there is no way to have the body. So after detecting the word LOAN on the body, how will you move the attachment? What attachment? Yo don't have it anywhere!

  19. #19
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I'm sure the job entry allows to specify a body filter.

  20. #20
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by marabu View Post
    I'm sure the job entry allows to specify a body filter.
    Yes it does but never tested it with a Regex ...

    Edited. I mean ... tested but it doesn't work
    Last edited by DepButi; 11-22-2017 at 05:50 AM.

  21. #21
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Why regex?

    It's a substring search.

  22. #22
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by marabu View Post
    Why regex?

    It's a substring search.
    Wow! That's nice! Thanks marabu.

  23. #23

    Default

    Quote Originally Posted by DepButi View Post
    Wow! That's nice! Thanks marabu.
    Thanks for your help, DepButi, Marabú,


    However, I´m so sorry, but I don´t get the point about the
    "subject (or body) searched via
    SubjectTerm" :S

  24. #24
    Join Date
    Nov 2013
    Posts
    382

    Default

    Ok, again ...
    using the GetMail JOB step you can get the attachment but not the body, so no way to look for "LOAN" on the body and decide what to do with the attachment.
    using the GetMail TRANSFORMATION step you can get the body but not the attachment, so you can look for "LOAN" but once found you cannot move the attachment because you don't have it.

    Marabu pointed out that you can use the JOB step and on the filters option simply write the word LOAN on the body filter box, so only the mails containing the word LOAN on the body will be processed, and the attacment can be moved.

    Hope it's clear now!

  25. #25

    Default

    Quote Originally Posted by DepButi View Post

    Marabu pointed out that you can use the JOB step and on the filters option simply write the word LOAN on the body filter box, so only the mails containing the word LOAN on the body will be processed, and the attacment can be moved.
    I tried like this, a simple transformation, but didn´t differenciate between mails with "loan" in the body and not...

    Screenshot attached.

    Name:  loan.jpg
Views: 45
Size:  19.8 KB

    Should I need to use " .* " ?

  26. #26
    Join Date
    Nov 2013
    Posts
    382

    Default

    It should work (it works for me).

  27. #27

    Default

    Quote Originally Posted by DepButi View Post
    It should work (it works for me).
    Mmmmm... I tried sending to myself two mails, both with attachments, and one with "loan" in the body and other one without "loan". And the job processes both mails and take both attachments... :S

  28. #28

    Default

    Fixed! Thanks so much Depbuti & Marabú! In the case of a plane email, it is clear!

    But as you know, requirements sometimes are changing every half an hour,...or even less...XD

    In case of we recieve an Excel file as an attachment, should I leave the idea of use a Regexp as well, according the development that you sent me DepButi? I mean in order to search for this word "loan" but this time as a value of a cell in the Excel file...

  29. #29
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by Peptaho_BCN View Post
    Fixed! Thanks so much Depbuti & Marabú! In the case of a plane email, it is clear!

    But as you know, requirements sometimes are changing every half an hour,...or even less...XD

    In case of we recieve an Excel file as an attachment, should I leave the idea of use a Regexp as well, according the development that you sent me DepButi? I mean in order to search for this word "loan" but this time as a value of a cell in the Excel file...
    Yes, using the job get mail step and the "top" pipeline of the transaction I sent as an example, you can check the existence of a specific word on the cells.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.