Reading file from email attachment

[supp]

As part of a weekly process I receive an email with an excel file attached. I need to bring this excel file into a SAS dataset as a data source for a program. Does any one know how to do this  via a SAS program? Is it possible to read an email attachment as a data source directly from a SAS program? 

I have seen some older posts on this topic but haven't been having much luck. I am using EG on a windows device with SAS 9.4 running in Linux.

With EG I have been able to access my inbox using file → open → exchange. This creates a dataset from my emails and identifies if there was an attachment to the email. How do we access the attached file?

[Reeza]

I'd probably recommend having Outlook automatically save the attachment somewhere once the file is received. I believe that's a straightforward task in Outlook and then have the SAS program run against that saved file? How are you triggering the job?

[supp]

Right now I run the job manually after I import the external file. If I could read the file directly from the email  I would turn the job over to our scheduling tool to run on (and after) the email arrives.

You are probably right about using Outlook to place the file somewhere. Then I could use the file to trigger the job. Thanks for the suggestion.

I am still curious if SAS could access the file directly from the email.

[Reeza]

You probably can through a variety of options I just don't think it's worth the effort. I'd probably start out by looking at how VBS/Powershell would access it and then use SAS to push those VBS/PS code instead via x commands. All I really care is that the process is a single command click. You could probably right an Outlook rule that would save the file and then trigger the job if the email comes in with keywords. We used to have our IT team do that for us for data feeds that were not via SFTP.

A quick google search shows that there's a lot of sample code out there to do that. I'm not sure about reading straight from an attachment. If you can somehow end up with a URL/path for the file, then I think it would be.

[SASKiwi]

As far as I'm aware SAS's email functionality is limited to sending email programmatically with or without attachments: https://documentation.sas.com/?docsetId=lestmtsglobal&docsetTarget=n0ig2krarrz6vtn1aw9zzvtez4qo.htm&...

There is no ability to read email. @Reeza 's suggested approach is your best option.

[supp]

@SASKiwi , I can use Enterprise Guide functionality to access my email inbox. File → Open → Exchange .... Admittedly I don't know much about how this works, but it seems SAS can "read" emails. Using this functionality there is a column in the dataset returned that indicates if the email has an attachment. This led me to wondering if SAS could also access the attached file. 

I do think @Reeza 's suggestion might be the easiest approach.

[SASKiwi]

@supp  - Interesting. When I do File - Open in EG I don't see Exchange as an option. What version of EG are you using? I'm using 8.2. I also tried 7.15 and couldn't see it either. I found this link though: https://documentation.sas.com/?docsetId=egamoag&docsetTarget=n13scryvmy9ktkn17g4m879ha0ue.htm&docset...

My comment was based more around a programmatic interface, rather than an EG option. I also believe the SAS Add-in to MS Office product works in Outlook as well so there is another option than might be useful.

[supp]

@SASKiwi , I am using EG 7.15. I didn't think about using the SAS add in with Outlook. Thanks for the tip!

[SASKiwi]

@supp  - So what do you see when you do a File - Open? Can you post a screenshot of the Exchange option? I'm curious to try it myself if I can just figure out how it works...

[supp]

@SASKiwi , here is a screen shot of the exchange file open

After selecting Exchange it opens another window that allows me to select my exchange email address and a folder (e.g. Inbox).

[Reeza]

The problem is the GUI connections are not always available via code 😞

[SASKiwi]

@supp - Thanks! That option doesn't appear for me. I'm guessing that the SAS Add-in to Outlook needs installing as well.

[andreas_lds]

---

@SASKiwi wrote:

@supp - Thanks! That option doesn't appear for me. I'm guessing that the SAS Add-in to Outlook needs installing as well.

---

No, the add-in is not required for the import-option to appear, but maybe a wise admin has disabled the feature via role definition in metadata.

[SASKiwi]

@andreas_lds - Only problem is I'm the wise Admin who should know these things...I tried adding EG Advanced Role (since that was the role listed in the documentation) to my user but that didn't seem to help.

[andreas_lds]

---

@SASKiwi wrote:

@andreas_lds - Only problem is I'm the wise Admin who should know these things...I tried adding EG Advanced Role (since that was the role listed in the documentation) to my user but that didn't seem to help.

---

Interesting, we have disabled the capability in our custom EG-role, but when i switch to EG Advanced Role, the option does not appear in File/Open. Strange. Maybe a problem with EG 7.15 HF6 - will check this as soon as i can spare the time.