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
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?
@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.
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?
Or Powershell?
https://stackoverflow.com/questions/44811375/reading-comments-from-excel-file-with-powershell
I'll leave it to you to Google Python, etc 🙂
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).
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.