topic Re: Retrieve comments from an Excel file in SAS Programming

Retrieve comments from an Excel file

ghosh — Fri, 01 Mar 2019 04:50:35 GMT

Is there a way to retrieve comment from Excel 2013 xlsx worksheets using SAS Enterprise Guide? Perhaps through xml?

When I try to save the xlsx file in xml format I get an error message "cannot save XML data because the workbook does not contain any XML mappings". However, I am not sure how I would create mappings for comments since they occur in several columns.

Thanks

Re: Retrieve comments from an Excel file

andreas_lds — Fri, 01 Mar 2019 05:59:35 GMT

@ghosh wrote:

Is there a way to retrieve comment from Excel 2013 xlsx worksheets using SAS Enterprise Guide? Perhaps through xml?

When I try to save the xlsx file in xml format I get an error message "cannot save XML data because the workbook does not contain any XML mappings". However, I am not sure how I would create mappings for comments since they occur in several columns.

Thanks

You could extract the xlsx file, search for the xml-file containing the comments (could be more than one) and read that xml-file using libname. I would do nearly anything to avoid such a programming task 😉

But there seems to be no other way to import the comments.

Re: Retrieve comments from an Excel file

ScottBass — Fri, 01 Mar 2019 07:25:49 GMT

I Googled "excel vba extract comments".

Here is one hit: https://trumpexcel.com/get-list-of-comments-in-a-worksheet-excel/

Perhaps this will get you what you need?

Re: Retrieve comments from an Excel file

ScottBass — Fri, 01 Mar 2019 07:27:55 GMT

Or Powershell?

https://stackoverflow.com/questions/44811375/reading-comments-from-excel-file-with-powershell

I'll leave it to you to Google Python, etc 🙂

Re: Retrieve comments from an Excel file

s_lassen — Fri, 01 Mar 2019 09:29:06 GMT

I think an Excel file is actually a zipped xml-file. Meaning that you can get all the stuff out of the file if you allocate it as a ZIP filename and read it as an XML or a text file. But I cannot help you with the details of where to find the comments.

You could start by renaming/copying your .XLSX file to .ZIP, extract it to an XML-file, and open it in your favourite text editor (UltraEdit and Notepad++ come to mind as good possibilities).

Re: Retrieve comments from an Excel file

ghosh — Fri, 01 Mar 2019 14:07:41 GMT

I did not understand how I could extract the xlsx until I read the message s_lassen below. However, thank you for the lead.

Re: Retrieve comments from an Excel file

ghosh — Fri, 01 Mar 2019 14:10:44 GMT

Thanks for both suggestions, I did see these approaches when I had googled the problem, still hoping to find a SAS solution if possible before going the VBA route.

Re: Retrieve comments from an Excel file

ghosh — Fri, 01 Mar 2019 14:26:56 GMT

s_lassen Thanks for this lead, I did not know that a xlsx file was actually a zip container. When I renamed an xlsx file to zip and extracted all the xml files, I found comments are stored in several xml files.

Now I am trying to establish a link between the xml files and the worksheets and cell refs.

Thanks again.

Re: Retrieve comments from an Excel file

ghosh — Fri, 01 Mar 2019 23:25:40 GMT

This works quite well. Thanks for the link