Read a zipped Excel file ??

[KevinC_]

Hello Everyone,

I am trying to read an Excel file that has been zipped.  The Excel file name is new_file.xlsx.  It has been zipped and sits in c:\docs\new_file.zip.

How do I read this file without manually unzipping it first?  I have tried Proc Import and x command.  Here is my attempt using x command:

"cd C:\Program Files\7-Zip\";

'7z.exe e -o C:\docs\OUTPUT.xlsx C:\docs\new_file.zip';

When I run this code I don't get any errors.  It does not seem to produce any output either.

Any suggestions would be greatly appreciated.

Thank you! 

[andreas_lds]

With SAS 9.4 you can use the filename statement to access a zip file. Have a look at FILENAME Statement, ZIP Access Method :: SAS(R) 9.4 Statements: Reference, Third Edition.

[Kurt_Bremser]

To get the output of external commands into the log, use this construct:

filename oscmd pipe "your_command 2>&1";* the 2>&1 redirects stderr output to stdout;

data _null_;

infile oscmd;

input;

put _infile_;

run;

You won't be able to do it all in one step, though; the ZIP file access method can only read text from zipped files. So you will have to unzip the file first and then use one of the excel access methods (probably libname xlsx) to read from the .xlsx.

[jakarman]

When it is only an excel file xlsx type being zipped it makes no sense.  The xlsx file is an zip-file having xml files and references inside it. You can open an xlsx file with 7zip yourself yourself.

[ChrisHemedinger]

As Andreas pointed out, FILENAME ZIP can be used to extract the file, and then you can read the result with PROC IMPORT.  See examples of using FILENAME ZIP in this blog post.

Chris