Background
I'm running SAS 9.4 M6 on a Windows Server 2016 Standard (64 bit) machine. I'm trying to update and add some xml files embedded inside an Excel xlsx file (an Excel xlsx file is a Zip archive) in order to update a custom property, a label that marks the file as either "Internal" or "External." If I manually unzip the xlsx file and manually change the embedded xml files using Windows Notepad and then manually re-zip everything, I can a) open xlsx file with Excel, b) the data displays correctly, and c) the Internal/External label works correctly.
Problem
However, if I use the SAS Zip engine to do the very same thing, I a) cannot open the xlsx file with Excel, and b) if I try to use WinZip to read the files, I get CRC32 checksum errors. See screen print.
I suspect that SAS is updating the XML but not re-syncing the CRC32 check sums properly.
Questions
Has anyone encountered something like this before? Is there a way to manually cause SAS to resync all the CRC32 check sums in a Zip archive?
My program is below. You should be able to execute the program on any SAS system, both Unix type and Windows. I have tried running the four xml updates/adds in different order as well as individually one at a time to try to identify if one of the four is the problem, but no combination of changes work when all four are performed. However, if I run any one of the four by itself without running all four, I can open and read the results using WinZip. Something is getting corrupted when multiple operations are performed on a single xlsx file.
Jim
Specific xml files being updated or added
- _rels/.rels (update)
- docProps/app.xml (update)
- docProps/custom.xml (add)
- [Content_Types].xml (update)
Detailed Error Messages
I notice that even files that I am not touching are getting corrupted. To me this would appear to indicate that the SAS Zip engine is not working correctly.
Extracting file: C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS\Class_3.ZIP Extracting to "C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS\Class_3\" Use Path: yes Overlay Files: no Extracting xl\worksheets\sheet1.xml Extracting xl\sharedStrings.xml Extracting docProps\core.xml Extracting xl\styles.xml Central and local directory mismatch for file "xl/styles.xml" (crc-32 - local: 1148BA33 hex central: 3E9D0B61 hex). Extracting docProps\app.xml Extracting _rels\.rels Central and local directory mismatch for file "_rels/.rels" (crc-32 - local: 1148BA33 hex central: 52D5847E hex). Extracting xl\_rels\workbook.xml.rels Unable to find the local header for xl\_rels\workbook.xml.rels. Extracting xl\workbook.xml Central and local directory mismatch for file "xl/workbook.xml" (crc-32 - local: 721EAEAE hex central: 4B8E0F38 hex). Severe Error: Local and central CRC values don't match.
Program
%Time_Stamp(START);
** Set slash to back slash for Windows or forward slash for LINUX/UNIX **;
%LET Slash = %SYSFUNC(IFC(%EVAL(&SYSSCP = WIN),\,/));
*%LET Iteration = 0;
%IF %SYMEXIST(Iteration) %THEN
%DO;
%LET Iteration = %EVAL(&Iteration + 1);
%END;
%ELSE
%DO;
%LET Iteration = 1;
%END;
%PUT &Nte1 Running Iteration &Iteration;
%LET Path = C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS;
*%LET Path = I:\commercial\user\jbarbou3\ODS_Excel;
%LET File_Name = &Path.&Slash.Class_&Iteration..xlsx;
** Create an xlsx file with Proc Export **;
PROC EXPORT DATA=sashelp.class FILE="&File_Name" REPLACE DBMS=xlsx;
RUN;
FILENAME XL_File ZIP "&File_Name";
**------------------------- Relationships --------------------------------**;
** Read in the .rels relationships file, add a reference to custom.xml, and write out a SAS data set containing the modified .rels xml **;
DATA WORK.Rels_XML;
DROP _:;
INFILE XL_File(_rels/.rels) LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
_Position = FIND(Line, '</relationships>', 'i');
IF _Position THEN
DO;
PUTLOG "&Nte1 Adding custom.xml entry to spreadsheet iteration &Iteration";
SUBSTR(Line,_Position) = '<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml"/></Relationships>';
END;
RUN;
**------------------------- Content Types --------------------------------**;
** Read in the Content Types XML file, add a reference to custom.xml, and write out a SAS data set containing the modified Content Types XML. **;
DATA WORK.Content_Types_XML;
DROP _:;
INFILE XL_File([Content_Types].xml) LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
_Position = FIND(Line, '</Types>', 'i');
IF _Position THEN
DO;
PUTLOG "&Nte1 Adding custom.xml entry to spreadsheet iteration &Iteration";
SUBSTR(Line,_Position) = '<Override PartName="/docProps/custom.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/></Types>';
END;
RUN;
**--------------------------- Custom.XML --------------------------------**;
** Create a SAS data set that contains the custom XML that will set the label to either External or Internal **;
DATA WORK.Custom_XML;
INFILE DATALINES4 LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
DATALINES4;
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Enabled"><vt:lpwstr>true</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SetDate"><vt:lpwstr>2022-11-05T21:48:26Z</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="4" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Method"><vt:lpwstr>Privileged</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="5" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name"><vt:lpwstr>External Label</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="6" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SiteId"><vt:lpwstr>db05faca-c82a-4b9d-b9c5-0f64b6755421</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="7" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ActionId"><vt:lpwstr>6d6ffa67-7505-4457-b8c1-4592fb83e3c9</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="8" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ContentBits"><vt:lpwstr>0</vt:lpwstr></property></Properties>
;;;;
RUN;
**--------------------------- App.XML --------------------------------**;
** Create a SAS data set that contains the App.XML that will correspond to the rest of the XML **;
DATA WORK.App_XML;
INFILE DATALINES4 LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
DATALINES4;
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector size="4" baseType="variant"><vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant><vt:variant><vt:lpstr>Named Ranges</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector size="2" baseType="lpstr"><vt:lpstr>Class_25</vt:lpstr><vt:lpstr>Class_25</vt:lpstr></vt:vector></TitlesOfParts><Company>SAS Institute Inc</Company><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>16.0300</AppVersion></Properties>
;;;;
RUN;
** Update/add the XML files to the xlsx file which is a Zip archive **;
DATA _NULL_;
LENGTH Mem_Name $128;
INFILE DATALINES4;
INPUT Mem_Name $;
FILE XL_File MEMVAR=Mem_Name;
PUTLOG "&Nte1 1. " _N_= Mem_Name=;
IF Mem_Name = 'docProps/custom.xml' THEN
DO;
** Create the custom.xml file from the SAS data set that contains the custom XML that will set the label to either External or Internal **;
PUTLOG "&Nte1 Processing " Mem_Name;
DO UNTIL (End_Custom);
SET WORK.CUSTOM_XML END=End_Custom;
Leng = length(line);
PUT Line $Varying32767. Leng;
END;
END;
IF Mem_Name = 'docProps/app.xml' THEN
DO;
** Create the app.xml file from the SAS data set that contains the App XML **;
PUTLOG "&Nte1 Processing " Mem_Name;
DO UNTIL (End_App);
SET WORK.App_XML END=End_App;
Leng = length(line);
PUT Line $Varying32767. Leng;
END;
END;
IF Mem_Name = '_rels/.rels' THEN
DO;
** Write the modified .rels xml to the .rels file **;
PUTLOG "&Nte1 Processing " Mem_Name;
DO UNTIL (End_Rels);
SET WORK.Rels_XML END=End_Rels;
Leng = length(line);
PUT Line $Varying32767. Leng;
END;
END;
IF Mem_Name = '[Content_Types].xml' THEN
DO;
** Write the modified Content Types XML to the Content Types XML file **;
PUTLOG "&Nte1 Processing " Mem_Name;
DO UNTIL (End_Content);
SET WORK.Content_Types_XML END=End_Content;
Leng = length(line);
PUT Line $Varying32767. Leng;
END;
END;
DATALINES4;
_rels/.rels
docProps/custom.xml
docProps/app.xml
[Content_Types].xml
;;;;
RUN;
%Time_Stamp(STOP);
Excel error message.
When I try to open the xlsx file with Excel, I receive the following message. Any recovery attempt(s) fail.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1220 views
-
5 likes
-
-
