Merge cell comments from different Calc files

English
,
#1

I am in need of assistance with using macros in Calc. Recently, someone in my office accidentally deleted some cell comments containing important data, and I need to restore them from an old backup. My plan is to merge the comments from the old file with the updated sheet.

Although I have experience in scripting and OOP, I have never worked with macros before, not even in Excel. Therefore, I would appreciate some guidance on how to get started.
I have noticed that Calc supports several macro languages, but I am unsure whether I should use Python or BASIC. Could you please provide your input on this matter?

Thank you in advance for your comments and suggestions.

EDIT:
I have attached anonymized sheets for reference. The old file is “10 MAYO 2022.ods,” and the new one is “17 DE FEB DE 2023.ods.” The sheet name is the same as the file name, corresponding to the date.
10 MAYO 2022.ods (166.2 KB)
17 DE FEB DE 2023.ods (1005.9 KB)

#3

You should clearly state how much the structure of the document and of its sheets has changed since the mentioned backup and what you think are “some” deleted comments. Next question would be in what way you can identify annotations needing to be re-created an how the offset for the insertion can be determined. Then: Are there still annotations edited since the backup and do they need protection against overwriting …
I cannot think of a general abstract answer.covering all the possible cases.
This does not depend on the macro/script language you want to use.

BTW: Comments are not made for data.

#4

Thank you for your response. I work at a rehab institute in the labor or job office, and we use a sheet to store information about our residents. This sheet includes fields for ID number, first name, last name, current commission, and other fields that are not relevant to the issue I’m facing. Unfortunately, the current commission column is where we have saved the work history in the form of comments.

I am aware that comments are not an appropriate way to store data, and I am looking into reorganizing the document to avoid this issue in the future. As for the missing comments, the backup has the full work history and on the updated sheet only the inmates who have changed jobs recently have comments in the current commission field.

Again, thank you for your response, I appreciate your input on this matter.

#5

Are you actually only concerned about one sheet of the document?
Do you want to re-create missing comments?
Do you want to check for still existing comments in the target sheets?
Can there be an offset in position?
If a comment exists, but the related comment from the backup has different content: How to handle this?

Much reduced and anonymized sheets “asisnow” and “asfrombackup” showing all the systematic relations and attached to your question would help much in helping.

#6

You might want to take the opportunity to merge the information from the remaining comments (annotations) and the annotations from the backup with the same address into a new sheet under the same address, or under two adjacent ones.
Once that is done and reliably cleaned up, the annotations previously misused for data can be deleted.
This is simpler to do by code, and it’s much better to handle for the future.

#7

Thank you for your response. Currently, my main concern is only one sheet of the document, but in the future, I plan to create forms/dialogs for data entry and automate report generation, which might involve several sheets.

Regarding the current issue, I have removed personal names from the old and new files, which I have attached for reference. To address your questions, I would prefer to restore the old comments, but if possible, I would like to recreate them with the latest data. Ideally, I would concatenate the most recent data (at the beginning) to the comments in the old file (at the end of the field)

In response to your suggestion, I have attached anonymized sheets to my initial post for reference. The old file is “10 MAYO 2022.ods,” and the new one is “17 DE FEB DE 2023.ods.” The sheet name is the same as the file name, corresponding to the date.

Thank you for your assistance.

#8

LibreOffice comes with a database component, input forms and report generator. All this spreadsheet madness leads to nowhere.

#9

The documents attacherd now to the question do not show any reliable structure. Attempting to extract relatable information from the old backup is like taking a gamble.
…
Therefore, without further reference to the specific task, I will only demonstrate how to create ordinary content from annotations.
See attachment.
date2022_05_10WithCode.ods (168.9 KB)
Having checked the included code for the absence of malign parts
run the Sub ‘storeSheetAnnotationsToSeparateDocument’

Having finally extracted the old info you seem to still need, you shouldn’t start to invent a new DatabaseBySheets. There are already too many of the kind. Move the task (and the data, of course) to a well proven DataBase application. Due to the lack of clear structure in the current files this won’t be easy, but @Villeroy may give more detailed good advice here and there. And the result should be stable and reliable then. After all your concern is not just a game you are playing.

#10

I am very interested in utilizing LibreOffice Base for form and report generation, and I would like to learn more about these features. With my experience working with SQL databases, I am eager to explore the potential of LibreOffice Base. While I am confident in my ability to construct a normalized database schema, I may require some guidance on how to use LibreOffice Base to generate effective forms and reports.

Could you kindly direct me to reliable documentation on this subject? I would greatly appreciate any assistance you can provide. Thank you for your help.

#11

Create your database with any tool you are familiar with. Just use a database engine that is connectable with LibreOffice. All databases having some JDBC or ODBC driver are connectable with LibreOffice. There are native LibreOffice database drivers (SDBC) for MySQL, MariaDB and PostgreSQL.
Finally, it should be possible to pull the raw data from your sheet cells (including annotations) into your database using the Base GUI.

Creating a new database with Base will result in a HSQL database that is embedded in the database document. The embedded database will be “installed” to a temporary directory when accessing the database and it will be re-wrapped into the Base document when closing the document. This is a highliy error prone process. You may lose all data just because you closed your laptop lid too hastily.
The tools for generating this type of database in a single file exist since OpenOffice.org 2.0 (year 2006) and they are awful. The resulting database is good enough for demo and educational purposes. However, it is very easy to convert an embedded HSQLDB into a stand-alone HSQLDB stored outside the database document which I find very robust and reliable.

Base is not a full featured database development suite. It is a small addition to Writer and Calc.

#12

Thank you for your reply. I just wanted to clarify that I am not playing any game, but rather I am an inmate who is trying to assist the prison authorities in maintaining organized and reliable data. I appreciate your suggestion to use a well-proven database application to handle the data, and I will be using the help of @villeroy and other people from this forum to accomplish that in the future.

Regarding the macros you provided, they work great, but I need to keep the DNI column data from each specific row, which represents each inmate. Also, in the new file, I need to move the annotations onto a new column (from the same sheet). And finally, I need to copy the data from the old file and merge it with the new one, concatenating the annotations (that are now in a new column), while keeping in sync the data from each inmate (let’s say DNI column would be the primary key of an “inmate” table in this scenario).

I hope this clarifies my situation and what I need to accomplish. Thank you for your assistance so far, and I look forward to any further advice you may have.

#13

Since I was the one who used the word “game”, I want to clarify that I didn’t intend to be disrespectful. In fact it’s me who is playing a “support-game” because there isn’t any kind of business in what I do. In addition I suggest a solution I would probably not be capable of implementing it myself, and actually I dislike databases. No matter! There are lots of tasks in the real world we need databases for.
Your first concern, however, is the recreation of information from annotations in an old backup, and in converting it into a usable form.
“Villeroy” claimed that even annotations can directly go to a new database, but this may not cover tha actual case of losses and old backup. On the other hand it’s easy for me to help with this part of the task. See new attachment. see code and explanations at the end of this post. The assignment of the results to the correct rows of different sheets is exclusively your responsibility.
The further use of what you get is additionally aggravated by the fact that many of the annotation contents are compounds made from entries belonging to different days (e.g. / dates badly formatted). it may not be easy to make a normalized database from all that. Good luck!

Sorry. Due to limitations of the site, the upload was refused.

The following sheets were imported from the backup interactively.
For such a one-time-action any coding is wasted time.
Then these imported sheets were renamed (interactively again) prefixing "old_" to the respective name.
Now the included code was run to convert the annotations to ordinary content. 
The columns to take the results were chosen next right of the formarly used area,
all with the same offset in columns and no offset in rows.
Whether the changed annotations in this way correlate row by row with the information in the
more recent document is beyond my judgment, and I cannot of course take any responsibility.

The changes mentioned in A4 were reverted. They should instantly be done again
when running the included Sub 'storeSheetAnnotationsToSeparateColumnsRowByRow'.

Sub storeSheetAnnotationsToSeparateColumnsRowByRow(Optional pSheetPrefix As String)
If IsMissing(pSheetPrefix) Then pSheetPrefix = "old_"
cDoc = ThisComponent
srcSheetCandidates = cDoc.Sheets
For Each srcSheetCandidate In srcSheetCandidates
 needCheck = srcSheetCandidate.Name
 If InStr(needCheck, pSheetPrefix)=1 Then
  cSheet = srcSheetCandidate
  cellCur = cSheet.createCursor()
  cellCur.gotoEndOfUsedArea(False)
  freeCol = cellCur.RangeAddress.EndColumn + 1
  cAnnos = cSheet.Annotations
  For Each anno In cAnnos
   annoCA = anno.Position REM Actually not a position but a CellAddress!!
   With annoCA
    trgCell = cSheet.getCellByPosition(freeCol + .Column, .Row)
    trgCell.String = anno.String
   End With
  Next anno
 EndIf
Next srcSheetCandidate
End Sub
#14

Sample free of macros:
https://forum.openoffice.org/en/forum/viewtopic.php?t=54395

#15

Thank you, Lupp, for your clarification. I appreciate your suggestion to use HSQL and Base, but unfortunately, my current mobile connection makes it time-consuming to start up my virtual machine and download HSQL. However, I will definitely explore its capabilities when I have a better connection.

Regarding the issue at hand, I agree that writing one-time code can be a waste of time, but since our office heavily relies on the ods file, we need to have this running. Thank you for your assistance in converting the annotations into a usable form. Even though I have not imported the new sheets yet, I have already created a normalized database from a Ruby on Rails project I am working on (I haven’t attached it due to file extension limitation).

Additionally, I am wondering if HSQLDB or Base have a native way to handle enum types?

#16

You may start with the old HSQL (version 1.8 of 2007) which is shipped with your office suite by creating a new embedded database from scratch. Later you can extract the embedded database, upgrade to a newer HSQL version and/or set up a HSQL server.

Tutorials and many examples based on embedded HSQL

#17

Thank you for your message, @villeroy. I am currently using LibreOffice 7.4.5.1 on Linux Mint 20.3. I have tried opening the embedded database from the backup file, but it seems that it doesn’t pull the comments from the “COMISION” column. I have attached a screenshot for reference. Is there a way to fix this and retrieve the comments in the database?

Screenshot 2023-02-19 at 17.23.59
Screenshot 2023-02-19 at 17.23.592756×330 94.6 KB

Also, regarding the annotations, it works for me to have them on a new column. Do you have any suggestions on how to achieve this in the most efficient way? Thank you again for your assistance.

#18

[Calc, Basic] Introspective cell functions (function CELL_NOTE)

#19

I don’t think you can get it more efficient than by the code i gave in my previouis comment. I ran it in a second for all the annotations contained in your backup file. The most important thing is that the content is correctly assigned to the records in the current sheet.

#20

Thank you both @Lupp and @Villeroy for your reply. I have copied the annotations to a new column in the same sheet with some changes to the macros Lupp provided earlier.

Currently I have a “laboral.odb” file with a single table “27 DE ENERO DE 2023”. However, when I open the “laboral.odb” file with connection string jdbc:hsqldb:file:/home/equipo/Escritorio/laboral.odb on HSQLDB Manager Swing GUI, I don’t see any tables listed.

HSQLDBMS
HSQLDBMS2284×568 105 KB

Furthermore, when I try to create a PROCEDURE through LibreOffice Base, I get an error message as shown in the screenshot below.

LO Base error with PROC
LO Base error with PROC3356×2048 394 KB

I would greatly appreciate it if you could help me figure out what I am doing wrong. Perhaps I should export the data to a .csv file from Calc and then import it into a different database management system?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.