Hitachi Vantara Pentaho Community Forums
Page 1 of 3 123 LastLast
Results 1 to 10 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?

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.