topic Re: Missing comments after converting from .xml to .xlsx in ODS and Base Reporting

Missing comments after converting from .xml to .xlsx

mehong19 — Tue, 24 Apr 2018 22:31:46 GMT

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;

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

Reeza — Tue, 24 Apr 2018 22:57:31 GMT

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

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

mehong19 — Tue, 24 Apr 2018 23:03:04 GMT

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

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

ballardw — Tue, 24 Apr 2018 23:25:08 GMT

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.

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

mehong19 — Wed, 25 Apr 2018 00:19:11 GMT

I found an indirect manually conversion works.

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

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

mehong19 — Wed, 25 Apr 2018 01:02:33 GMT

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;