The SAS Output Delivery System and reporting techniques

Missing comments after converting from .xml to .xlsx

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Missing comments after converting from .xml to .xlsx

I noticed that after I run the following program to convert .xml to .xlsx file, the existing comments in some fields in .xml are missing in the converted .xlsx doc.

 

I wanted to know if anything I missed or I should add to carry over the comments to .xlsx. Many thanks!

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

Accepted Solutions
Solution
‎04-24-2018 09:02 PM
Contributor
Posts: 23

Re: Missing comments after converting from .xml to .xlsx

I found solution myself.

 

By a indirect conversion from .xml, .xls. to .xlsx via SAS DDE procedure, I can work it out.

 

Below is the code I modified.

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xls"'",56)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xls"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

View solution in original post


All Replies
Super User
Posts: 24,026

Re: Missing comments after converting from .xml to .xlsx

Does this happen if you manually save the XML as XLSX or just when using DDE?

Contributor
Posts: 23

Re: Missing comments after converting from .xml to .xlsx

I found this situation happens for both, manually converted and DDE conversion.

Super User
Posts: 13,947

Re: Missing comments after converting from .xml to .xlsx

If you mean some cells have longish free form text the column might just be inheriting a width from something above where the width is just to narrow to display or the justification ends up too high or low to display in the default. Have you clicked in any of those cells in a spreadsheet program to see if text appears in the cell content window?

 

If no joy with that can you post the cell description text description from the XML of one or more cells with  with this problem using a plain text editor to copy and paste into a forum code box using the {I} menu icon? I suggest the plain text and code box as the main window might want to interpret content pasted into the main window differently.

And example might look a bit like this:

<Row ss:AutoFitHeight="1" ss:Height="15">
<Cell ss:StyleID="rowheader" ss:Index="2"><Data ss:Type="String">% Enroll</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="3"><Data ss:Type="Number">18.8</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="4"><Data ss:Type="Number">18.8</Data></Cell>
</Row>

 

The key bits are likely between the <Data> and </Data> tags. In the above the first cell of the row would contain "string" data with the value of "% Enroll" and the second cell would have "number" data with the value 18.8. But we are looking for cells that data does not appear in the xlsx. 

 

If the cell doesn't contain "data" then it likely is being ignored by XLSX conversion. If the type is something other than "String" or "Number" that could also be a problem for the conversion.

Contributor
Posts: 23

Re: Missing comments after converting from .xml to .xlsx

I found an indirect manually conversion works.

 

I firstly saved in .xls file, comments are retained. Then, save the .xls to .xlsx.

 

Solution
‎04-24-2018 09:02 PM
Contributor
Posts: 23

Re: Missing comments after converting from .xml to .xlsx

I found solution myself.

 

By a indirect conversion from .xml, .xls. to .xlsx via SAS DDE procedure, I can work it out.

 

Below is the code I modified.

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xls"'",56)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xls"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 328 views
  • 2 likes
  • 3 in conversation