topic Re: Read a zipped Excel file ?? in SAS Procedures

Read a zipped Excel file ??

KevinC_ — Wed, 01 Apr 2015 15:58:06 GMT

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!

Re: Read a zipped Excel file ??

andreas_lds — Thu, 02 Apr 2015 08:36:00 GMT

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.

Re: Read a zipped Excel file ??

Kurt_Bremser — Thu, 02 Apr 2015 08:54:07 GMT

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.

Re: Read a zipped Excel file ??

jakarman — Thu, 02 Apr 2015 11:03:21 GMT

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.

Re: Read a zipped Excel file ??

ChrisHemedinger — Thu, 02 Apr 2015 11:45:56 GMT

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