Excel read/write workflow any way to automate the manual steps

Hello - novice Knime user here, and I can’t help but wonder if I can save steps or eliminate some of the manual steps that I do when I am required to run new raw file data through an existing workflow that I’ve created. It feels more cumbersome than another ETL platform i’ve used in the past. TIA for your suggestions!

current steps:

  1. download/save raw data from email to my PCs hard drive

  2. in excel reader, go to settings and point to the raw data file

  3. do step 2 two more times because I have 3 worksheets/tabs in the same file that I need read in the [output] excel writer. this step also requires to manually select ‘sheet selection’ again because it defaults to the first radial button.

  4. go to excel writer settings and manually rename the file to today’s date

  5. execute

Could I ask why data comes from your email?

As for step 4, I’ve provided a solution below.
date_as_excel_file_name.knar.knwf (14.2 KB)

2 Likes

It should be possible to automatically save your emails/attachments with your email client. Haven’t done anything like that before, I can look into that tomorrow.

Everything else can be automated with KNIME quite easily. This is how it could look like, the actual workflow depends on your exact requirements. I’m assuming you want to read in all sheets and write the data in a new file with a single sheet. Maybe you can tell us a bit more? I’m sure we can cook something up for you that fits your needs.

The Read Excel Sheet Names is the only node you’ll configure manually. It outputs a table with one row for each sheet. We loop over it to collect all data. After that me make a new filename and write the table to a new workbook. The last step is similar to victor, but I calculate the current date, so that’s not so optimal after a vacation :wink:


Excel read-write automation.knwf (50.8 KB)

3 Likes

The file comes from another person via email (outlook).

@mb7846 you can combine all sorts of functions. You can scan a folder for Excel files. See information about them, extract parts of their names or use the current time stamp as file name …

1 Like

It would be more optimal if that person that sends you the files instead saves it to a drive both of you have access to. Or a program/script does the job. I’m aware that this might not be possible in your company, but this would solve the root cause for all the manual clicking.

That being said, Outlook can be set up to automatically save attachments, using automatic rules and this script:
How to automatically download/save attachments from Outlook to a certain folder? (extendoffice.com)

I also found this forum post by Armin that mentions Selenium nodes to access and download emails/attachments, but that’s unknown territory for me:

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.