How to extract a ZIPed CSV-file from an e-mail with KNIME?

[carllum]

Hi KNIME users,

I receive the data from a weather station (wind in this case) once a week by e-mail.

This e-mail contains a ZIP file with the weather data in CSV format.

So far, I save the data locally and process it.
Now I would like to optimise this data import process. I have a question. Is there a possibility / a node for processing e-mail attachments using KNIME? Which node would be ideal for this?

Have I overlooked something in the forum / in the directory of sample workflows?

If I have overlooked something, please help me to navigate.

[ipazin]

Hello @carllum,

and welcome to KNIME Community!

Interesting case you have here. There is Get Email node you can give a try. I haven’t used it but from its description you should get file attachment in second output port. I wonder in what format attachment is and can you read it right away… Anyways give it a try and let us know

Br,
Ivan

[qqilihq]

To add to Ivan’s reply: The email attachments will be available as binary data cells. These you can write to a temporary location on disk using Binary Objects to Files and then use the Decompress Files to unzip them. Or alternatively have a look at UnZip Binary Object which can probably do both steps on one node.

Fingers crossed!

–Philipp

[carllum]

Thank you Ivan and Philip for your answers ans sorry for the long silence. I got ill before Christmas…

I installed NodePit and implemented Get Email into a workflow.
My e-mail provider is t-online.de, I entered the required data according t-online like server name, e-mail address, password - the correct one :-). But the reading does not work… On the figure you see the conventional workflow above (saving the csv-files in a folder and read them…) and the Get Email configuration. I continue the configuration until it works.

I have not yet tested Philipp’s approach, but I will try and give feedback.

[qqilihq]

Hi there, happy to hear back …

@carllum

But the reading does not work…

If you’re not able to fix it, could you show the error message which is generated by the node - (1) once by hovering with the mouse over the red cross on the node and (2) the output which is additionally shown in the “Console” tab?

–Philipp

[carllum]

Thanks Philipp.

The status is:

Blockzitat

Execute failed: Invalid login

I also tested the Outlook settings Status: Access.

[qqilihq]

@carllum I just tried this with a test t-online.de address and it worked fine for me.

The only thing to consider is that that T-Online obviously has dedicated passwords just for SMTP and IMAP, which can be different from the account password. Probably worth looking into that?

Beside that, your settings look fine, except that I had to set INBOX instead of Posteingang.

–Philipp

PS: And thank you for posting that screenshot - I just realized that there’s obviously a bug with the node which prevents the proper icon form showing up. This is just a cosmetic issue which I’ll fix during the next days

[carllum]

@qqilihq

INBOX

Thank you very much for your advises.

With a correct setting, the “Get E-Mail” node operates as expectet .

Now I will take care for filtering the correct messages and extract the zipped files.

[carllum]

Again, thanks for your support to find a useful solution.

OK, Get E-Mail and sensor specific row filtering was successful. The result is:
4064 e-mails in total

When using the output port #1 (e-mails), and aply the rule-based row filter on “Messhistorie”, then the result is 62 filter e-mails, but without the bin. data.

When the output port #2 (parts) is used, then the rule-based filtered count result is 6. It there a date-time based filter in the Get E-mail node?

Binary Objects to Files and Decompress files node are operating as expected.
Now I am looking for the correct loop, which supports the extraction process of the ZIP-files into one CSV-file folder.