BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6
Hello,

How do I import a password protected Excel document? My proc imports works fine any non-password protected excel doc. I tried adding 'password = ' to the code below, but got an error with the password= option. Any input will be greatly appreciated.

Thanks!

proc import out= ssn1
datafile= "&Ssn_FILE"
dbms=EXCEL replace;
mixed = yes;
sheet= "Sheet1";
run;
8 REPLIES 8
ArtC
Rhodochrosite | Level 12
Shows what I know. I did not even know that an Excel file could be password protected.

Have you tried passing the password through the PIPE engine on the FILENAME statement? That is just a guess.
Ksharp
Super User
Hi .
Maybe you need build a ODBC connection for that xls file,because SAS treat Excel file as a library,but it is too troubled.Or recommend you to use password to open that Excel file and File-> Save as->xls file to make a xls file without password.


Ksharp
Oleg_L
Obsidian | Level 7
I'm not an expert in VBA but you can use MS Excel programming language (VBA) and PIPE engine to disable the password protection.
The code below sets the password. I'm not the author of this code. I've find it somewhere and It was very helpful as a solution of some tasks in different modifications. For example, I've modified it to rename Excel sheets.
You can change the VBA code to disable password protection.

[pre]
filename IN '.\book1.xls';
filename OUT '.\book1PW.xls';
%let PW = PWvalue;
data _null_;
length script filevar command workbook1 workbook2 $256 pw $16;
script = catx('\',pathname('WORK'),'PASSWORD.vbs');
filevar = script; workbook1 = pathname('in');
workbook2 = pathname('out');
pw = symget('PW');
file dummy1 filevar=filevar;
put 'Const ' workbook1=$quote256.;
put 'Const ' workbook2=$quote256.;
put 'Const ' pw=$quote18.;
put 'Set objExcel = CreateObject("Excel.Application")';
put 'objExcel.Visible = FALSE';
put 'objExcel.DisplayAlerts = FALSE';
put 'Set objWorkbook = objExcel.Workbooks.Open(workbook1)';
put 'objWorkbook.SaveAs workbook2,,pw';
put 'objExcel.Quit';
/* close the script file by opening another file */
filevar = catx('\',pathname('WORK'),'DUMMY2.vbs');
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy3 filevar=script end=eof;
do _n_ = 1 by 1 until(eof); input;
putlog 'NOTE: ' _n_ 3. +1 _infile_; end;
/* call the script */
command = catx(' ','cscript',quote(strip(script)));
infile dummy4 pipe filevar=command end=eof;
do until(eof);
input ;
putlog _infile_;
end;
stop;
run;

[/pre]
KevinC_
Fluorite | Level 6
Thank you all very much for your help! I copied the content of the excel to another excel without password proection then read from the unprotected file. I will try your methods/code above.

Thank you agian!
Bryte
Calcite | Level 5

Hi Oleg.  I realize that this post is from a previous decade but here goes anyway. I am ultimately trying to read a lot of locked Excel data into a dataset.  I am using EG 8.4 and have copied the files to the server.  I tried DDE since the files are copied to the server using another one of your examples but the line  filename cc dde 'Excel|System'; throws this error: ERROR: Invalid device type.ERROR: Error in the FILENAME statement.

 

So I was trying the VBA script option you mention, modified to read the file with password, unprotect it, and re-save without password.   But I'm getting the error that  it doesn't understand the C-shell command:  /bin/bash: cscript: command not found  

 

Any suggestions? If not, I am going to just work outside of SAS. It will just be slow...   

SASKiwi
PROC Star

DDE is only possible if you are running SAS on your PC and the Excel file is accessible from the PC. Sounds like your EG uses a remote Unix SAS server so it isn't going to work and VBA wont run on Unix.

 

The quick fix is to create an un-passworded version and upload that to your SAS server. 

AllanBowe
Barite | Level 11

I appreciate this post is 12 years old, but my team finally added this feature to Data Controller today!

 

Just upload the excel, enter the password, approve the change, and your password-protected excel data is now in your target SAS table ready to process.

Works on ALL versions of SAS (Viya, EBI, Base) and is free for up to 5 users.

 

https://www.youtube.com/watch?v=b2lfBCkdvBI

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 22843 views
  • 3 likes
  • 7 in conversation