Can we import password protected excel files in SAS Enterprise guide

[naveenraj]

HI All,

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. 

Regards,

Naveen

[SASKiwi]

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?

[naveenraj]

@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.

[ChrisHemedinger]

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.

[naveenraj]

hi @ChrisHemedinger,

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.

Regards,
Naveen 

[ChrisHemedinger]

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.

[naveenraj]

@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.

Regards,

Naveen

[LinusH]

@naveenraj I'm sure that you can solve your issue somehow, but perhaps the question should be a different one.
From my point of view the obvious solution is not to use passwords on file level. It's a crappy security solution. Instead, store the file in a secure location with appropriate access rights.

[rogerjdeangelis]

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

[AllanBowe]

Another option (and, full transparency - we built the product) is Data Controller for SAS®.

This will let you upload any password protected excel file into any target (SAS dataset, CAS or database table).  The excel is opened in the browser, and the raw data uploaded to SAS - where you can access it from Enterprise Guide.

Here's a video: