04-01-2015 11:58 AM
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.
04-02-2015 04:36 AM
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.
04-02-2015 04:54 AM
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;
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.
04-02-2015 07:03 AM
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.
04-02-2015 07:45 AM
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.