06-16-2016 10:52 AM
Is it possible to import password protected excel files into sas enterprise guide. I think we can do it with 'Allow XCMD' option enabled.
But is it possible to do it if that option is not enabled in the system. Curently in our environment this option is not enabled and our admins are reluctant to enable it citing security concerns.
06-16-2016 03:55 PM
I don't think you can do this with either the EG import wizard or with SAS/ACCESS to PC Files (PROC IMPORT, LIBNAME). DDE might work but you can't use that in EG.
How are you intending to do it with X commands?
06-17-2016 04:54 AM
@SASKiwiI saw some code where they are using vbs script and pipe to load data into sas. I might be wrong in my assumption that it uses xcmd option.
Can you please give details on DDE option you mentioned. I have read a few documents on the DDE method but have some doubts. Should microsoft excel be installed on server or on user machine and will it work if Allow XCMD option is unchecked in sas server. Sorry if these are idiotic questions as this is a completely new method for me and need to understand the pros and cons.
06-17-2016 08:38 AM
Here's a trick you can try for an interactive import task. SAS Enterprise Guide can read the content of a password-protected file IF you open it with Excel first. So:
1. Open the file in Microsoft Excel and specify the password.
2. Use the SAS Enterprise Guide File->Import Data task to read the same Excel file, while it's still open in Excel.
Neither SAS Enterprise Guide nor PROC IMPORT can read password-protected Excel files. If you need to run this in a "batch" mode then I think you're correct -- you would need to "delegate" the opening/reading of the Excel file to an external process, probably Excel itself, and then pipe the results into SAS. VB Script is one approach; Windows PowerShell is another.
objExcel = new-object -comobject excel.application $objExcel.Workbooks.Open("yourfile.xlsx",0,$False,1,"password")
You could then use PowerShell to save the workbook without a password (into a temp space) and then use SAS to import, or save the sheet you want as CSV, or read the records from Excel and pipe them into SAS as input records.
06-23-2016 08:55 AM
When i tried your method to
1) Open the file in Microsoft Excel and specify the password.
2) Use the SAS Enterprise Guide File->Import Data task to read the same Excel file, while it's still open in Excel.
I am getting the attached error.
I am using a client-server model. Is it a requirement that microsoft office should be installed in the server also for this method to work. I have excel 2007 installed on my local desktop.
Also, Allow xcmd option is diabled, so even with dde we wont be able to import. Please correct me if i am wrong.
06-23-2016 11:03 AM
Are you using 64-bit Enterprise Guide and 32-bit MS Office? That might be the issue for this case, where EG cannot use the local data providers to get access to your Excel file directly.
I think you're going to have to find a way to strip away the password from the Excel file (perhaps in a copy) before importing into EG.
06-23-2016 11:24 AM - edited 06-24-2016 04:16 AM
@ChrisHemedingerYes i am using 2007 excel. it should be 32 bit version as 64-bit applications for Office are available only for Office 2010.. That is the reason i am getting the error. One doubt regarding this. Why am i not getting any similar error when using import for excel files without any password protection.
As you were saying to strip away the password, is it possible using VBscripts. Can you please give details. This is not my area of expertise. So i am asking.
06-17-2016 12:21 PM
06-20-2016 10:59 AM
Along the lines of saving you password protected file to a restricted access folder without excel passwords, the VBA script below will remove the workbook and worksheet passwords
If you have full local SAS you should be able to execute this code from SAS or SAS calling R.
Sub ChDirNet(szPath As String) SetCurrentDirectoryA szPath End Sub Sub RemovePasswords() Dim SaveDriveDir As String Dim FName As Variant Dim FNum As Long Dim mybook As Workbook, ws As Worksheet Dim workbookpass As String Dim sheetpass As String workbookpass = ThisWorkbook.Sheets(1).Range("B1").Value sheetpass = ThisWorkbook.Sheets(1).Range("B2").Value SaveDriveDir = CurDir ChDir ThisWorkbook.Path FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xl*), *.xl*", _ MultiSelect:=True) If IsArray(FName) Then For FNum = LBound(FName) To UBound(FName) Set mybook = Nothing Set mybook = Workbooks.Open(FName(FNum), UpdateLinks:=0, ReadOnly:=False, Password:=workbookpass) On Error GoTo 0 If Not mybook Is Nothing Then On Error Resume Next With mybook mybook.Unprotect Password:=sheetpass For Each ws In Worksheets ws.Unprotect Password:=sheetpass Next ws End With Application.DisplayAlerts = False mybook.SaveAs Password:="" mybook.Close Application.DisplayAlerts = True End If Next FNum End If End Sub