I have a giant spreadsheet that many people have added notes or comments to by right-clicking on the cell and saying "new comment" or "new note". Is there a way for fme to figure out which cells have comments?
Hi @jakethepainter
There is an existing enhancement request for reading comments in an XLSX file using the Excel reader--this request is not planned at the moment.
Since XLSX files are zipped XML files, it is possible to unzip the XLSX file using FME and read the XML directly to get the comment cell values. I have attached an example of this approach.
Note: since the TempPathnameCreator transformer is used in the workspace, I would recommend not using Feature Caching when running the workspace.
I'm not familiar with Notes and it looks like the version of Excel I'm working with do not have the ability to create notes. But it looks like they are similar to comments so it may be possible to read them using the same method.
I hope this helps.
Hi @debbiatsafe that is a great approach. I'm running 2019.2 right now and your feature reader won't load for me. Any chance you can just post a screen shot of the parameters?
Hi @debbiatsafe that is a great approach. I'm running 2019.2 right now and your feature reader won't load for me. Any chance you can just post a screen shot of the parameters?
Hi @jakethepainter
This is how FeatureReader is configured in the workspace.
Yes, that could be done. But would not be very easy.
Use something to XMLUpdate the file that contains the notes and update the XML with your new Note. Write this new file to disk and then Zip all the original and new files into a file with the extention .xlsx
As long as you see an .xlsx file as a simple zip file containing xml files. And know how to read, write and edit xml files, you could test what changes are made to the file when notes are edited and replicate this behavior. And the result should again be a zip file containing xml files where .zip is replaced by .xlsx
I fixed it. You need to install "openpyxl," a Python library for reading/writing Excel files.
Then use PythonCaller.
Thanks for sharing