- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I want to read emails from an Outlook folder into a SAS table. I'm following the ODBC approach in http://support.sas.com/resources/papers/proceedings10/086-2010.pdf but I'm getting an error:
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] The specified
DSN contains an architecture mismatch between the Driver and Application
ERROR: Error in the LIBNAME statement.
I'm guessing this is some 32/64bit incompatibility. I'm using Outlook and Access 32 bit, but SAS 64bit (9.4TS1M3). Can this be made to work?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
As a minimum I suggest showing your LIbname statement. Someone may have an idea for what may be missing or incorrect.
Also since the reference paper is from 2010 and references Outlook 2007 and Access 2007 it may be fun digging out the changes that Micro$oft has made to the office interfaces. It might be and Office ODBC driver that needs to be updated.
Did you get the Access bit to connect to Outlook okay without involving SAS in any way?
Also can you connect to Access with a simple table or two successfully?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The libname is simply:
libname LISin odbc dsn=LISin;
LISin is configured in the ODBC configuration to point to an access database (2000 format). The Access database can read the Outlook emails file. I haven't managed to get SAS to read any Access database. After reading some more, I'm sure the problem is a 64/32 bit compatibility issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
By any chance do you have the SAS/Access Interface to PCFiles licensed? If so a Libname lisin PCFILES path="path and name of accessdb" might work.
If you do have the Interface to PCFiles but things aren't working you might try downloading the PCFILES server which is one of the ways around the 64/32 bit issue. https://support.sas.com/downloads/browse.htm?fil=&cat=63
I no longer have Microsoft access and the one time I did the ODBC connection was with SAS 9.2 and was 32 bit, so that might be the issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Got it working (sort of) after installing the 32 bit version of PC files server. My code is now:
proc import out=temp replace datatable="emails" dbms=accesscs ; database="emails.accdb"; memosize=32767; run;
Unfortunately, the engine can only read long text fields up to 32767 bytes. The email content I want read is larger than this - so I'll have to think of some other approach.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@BruceBrad wrote:
Got it working (sort of) after installing the 32 bit version of PC files server. My code is now:
proc import out=temp replace datatable="emails" dbms=accesscs ; database="emails.accdb"; memosize=32767; run;Unfortunately, the engine can only read long text fields up to 32767 bytes. The email content I want read is larger than this - so I'll have to think of some other approach.
As I mentioned I don't have Access. But a generic problem comes from using ProC Import in that the guessing procedure does just that and truncates long values. You might be able to write a data step to read the table and read the long field into multiple variables. Might.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A summary of my resolution to this. First, I'm not sure how 'unix' got into the subject line. No unix involved in this.
My goal was to read incoming emails (from the Outlook client) into SAS. I now have a procedure that writes each email to a text file (which can be read by sas). The strategy is:
1) Configure an Outlook Rule that runs a VBA script against every candidate incoming email. In recent versions of Outlook you need to change a registry value to permit this.
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Outlook\Security]"EnableUnsafeClientMailRules"=dword:00000001
2) Use a VBA script as per the following code. (You need to enable a reference to Microsoft Scripting Runtime for the FileSystemObject to work). (There is an alternative Savefile method in VBA but this wraps long lines of text).
Sub SaveLIS(Item As Outlook.MailItem) Dim Received As String Dim Subject As String Received = Format(Item.ReceivedTime, "yyyy-mm-dd-hh-MM-ss") Subject = strLegalFileName(Item.Subject) Dim objFS As Scripting.FileSystemObject Dim objOutputFile As Scripting.TextStream Set objFS = New Scripting.FileSystemObject Set objOutputFile = objFS.CreateTextFile("C:\temp\Returns\" & Subject & " R; " & Received & ".txt", True) objOutputFile.Write Item.Body objOutputFile.Close End Sub ' Utility function to tidy up filename Function strLegalFileName(strFileNameIn As String) As String Dim i As Integer Const strIllegals = "\/|?*<>"":" strLegalFileName = strFileNameIn For i = 1 To Len(strIllegals) strLegalFileName = Replace(strLegalFileName, Mid$(strIllegals, i, 1), "_") Next i End Function