One Star ms
One Star

Retrieve email from Pop and store in Database

I downloaded TalendOpenStudio 2.2.2 to evaluate if it can satisfy some of our business use cases.
I have a use case to pull emails from a pop3 server and insert them in a database table (table which has columns to store separate fields of emails such as "to email", "from email", content, header, subject etc). I found some samples which indicate that I can do this by using tPop node, which will dump the email in a file, and then I read email from file in a tFileInputMail node, which can then be connected to a tMssqlOutput node.
My use can also requires me to insert additional records in database, when an email is received (these records may not contain any data of the email received, but some other data).
However, I am a bit confused about how to map emails fields to database columns. Has anyone tried this before?
In addition to this, is there a way by which I can move email directly from tPop into one of databases, without storing the email in a file?
Any help is highly appreciated
12 REPLIES
Community Manager

Re: Retrieve email from Pop and store in Database

Hi
You can use tFileInputMail component to get datas from email and store in database. Please see the screenshot.
In your database, define a table with the same fileds as the schema defined tFileInputMail.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Retrieve email from Pop and store in Database

Hi all,
I want to retrive the files which would send to mail for every 10min and store in Database. How could I do it?
Please tell me the procee flow..
tFileInpumail--->tMssqlOutput
If I use above flow Does it works?
Please anyone reply me.
Thanks in advance.
Four Stars

Re: Retrieve email from Pop and store in Database

Hi ms,
tPop component would download mail in the form of a file. This file is read by using tFileInputmail component having standard metadata..
https://help.talend.com/search/all?query=tFileInputMail&content-lang=en
Attachment is also stored in with the file only... you can extract file from the mail and insert into the table using a routine, which will convert your input file to text/byte code..
https://community.talend.com/t5/Design-and-Development/Inserting-BLOB-or-CLOB-data-into-a-database/t...
I hope this will help.
Thanks
Vaibhav
One Star

Re: Retrieve email from Pop and store in Database

Thanks for your prompt reply Vaibav
Can u please tell me the flow
tPop--->tFileInpuMail--->tMssqlOutput
Does it works?
Four Stars

Re: Retrieve email from Pop and store in Database

tPrejob-->tDBConnection
tPop-->
OnSubjobOk--> 
tFileList-->tFileinputMail-->tJavarow(ContextVariables)
OnSubjobOk--> 
tFixedFlow-->tMSSQLOutput
Use routine to convert file into the bytecode or text contents to insert file into the database table
Vaibhav
Community Manager

Re: Retrieve email from Pop and store in Database

Hi 
Vaibhav has indicated that how to achieve this kind of demand, but your requirement is not clear.
Can you describe your request with more details? Do you want to extract data from the file and store the data into database or you want to store the whole file as a Blob data in database?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Retrieve email from Pop and store in Database

Thanks for reply
What is blob i don't know?
But my requirement is, we'll get the files into mail for every 5min, the data which is there in files must be stored into Database.
One Star

Re: Retrieve email from Pop and store in Database

Hi Vaibav,
Please could  you tell me where routine has to be run/works in above flow...
Thank you.
Four Stars

Re: Retrieve email from Pop and store in Database

In case if you are inserting attachment in single column, then the job of routine is to read the file contents, convert it to appropriate data type and make it available for insertion... both different methods are available in above url.
Vaibhav
One Star

Re: Retrieve email from Pop and store in Database

Hi,
What are the settings I have to change in tPop settings to get particular messages only.
Thank you
Seventeen Stars

Re: Retrieve email from Pop and store in Database

hi,
don't forget to search in Talend Help.
https://help.talend.com/search/all?query=tPOP&content-lang=en
If I read documentation, it tell me that I can use file Name pattern to filter mail file
regards
laurent

Re: Retrieve email from Pop and store in Database

Hi everyone,
I'm trying to use tPOP to extract the attachments from the latest email that was sent to me. When I ticked the box in tPOP settings "newer email first" and set the number of emails to retrieve to one, I expected to get the job done. However, Talend starts to read only with the second email from the top in this case. What should I do? Can someone help me out?
The mail box is on Microsoft office 365, I'm using imap protocol. 
Thanks in advance,
Stanislav