Bulk editing html in notes.db

[Comfy.n]

Is there a way to edit the notes.db file on a simple text editor, without harming the database structure? (html/css)

For example, I want to replace the string xx-large with x-large. Actually I did try this on Notepad++, but Calibre didn't like it. Then I restored my notes.db backup.

(I hope there is some way other than having to run sql commands!)

[kovidgoyal]

Just use copy/paste in the HTML view of the edit notes dialog. If you want to do it in bulk, then there is no easy way. You would bneed to write some code to export and re-import the notes.

[Karellen]

It should be pretty easy to edit notes.db using DB Browser...

https://sqlitebrowser.org/

[Comfy.n]

Hmm, that works for each note, individually. I want to change like 1500 notes all at once, just like the search and replace feature that works with metadata.db

[Comfy.n]

Originally Posted by Karellen

It should be pretty easy to edit notes.db using DB Browser...

https://sqlitebrowser.org/

hmm, I have that program. But I don't see something like a Search and replace function in it? (i mean, without having to know how to issue sql commands...)

[Comfy.n]

Originally Posted by kovidgoyal

Just use copy/paste in the HTML view of the edit notes dialog. If you want to do it in bulk, then there is no easy way. You would bneed to write some code to export and re-import the notes.

oh i see, thanks

[kovidgoyal]

Originally Posted by Comfy.n

Hmm, that works for each note, individually. I want to change like 1500 notes all at once, just like the search and replace feature that works with metadata.db

That will require code. You can write a small python script to export the notes using the calibre api, process them and re-import them. Run it with calibre-debug. But there is no pre-cooked solution to do this.

[Karellen]

Originally Posted by Comfy.n

hmm, I have that program. But I don't see something like a Search and replace function in it? (i mean, without having to know how to issue sql commands...)

Yes, you need to modify using SQL.

What edit do you need to make?

[Comfy.n]

Originally Posted by kovidgoyal

That will require code. You can write a small python script to export the notes using the calibre api, process them and re-import them. Run it with calibre-debug. But there is no pre-cooked solution to do this.

Oh, I wish I could.

I was testing a composite column containing the author notes, that displays both in book details and as a tooltip in booklist view, just out of curiosity. Then I thought I might just keep it, but I need to style the html provided in GR Author Notes. I guess I could edit the html inside the plugin, but this would work only for the new notes generated with it.

It looks interesting in Book Details, but I'd prefer a smaller author picture and a smaller h1 tag (it has also a font-size styling - xx-large).

[Comfy.n]

Originally Posted by Karellen

Yes, you need to modify using SQL.

What edit do you need to make?

for starts: xx-large -> x-large

[Comfy.n]

I notice the files below under .calnotes - backup:



Those are very easy to edit in bulk, by just loading them on a text editor and doing the Search and Replace on all open documents... If I could rebuild the database with these modified files...

[kovidgoyal]

Those are backups, I dont recall in what circumstances exactly they are used, but you can try corrupting the notes.db file or deleting it and see if they get restored. Make proper backups before doing that of course.

[Comfy.n]

Will try that tomorrow, thanks. Definitely not a good thing to do when I'm kind of somnolent.

[BetterRed]

Originally Posted by Comfy.n

hmm, I have that program. But I don't see something like a Search and replace function in it? (i mean, without having to know how to issue sql commands...)

You could do this:

• export the notes table in notes.db to a CSV
• edit the CSV with a text editor or spreadsheet program
• import the edited CSV back into the notes db notes table

Do an export and import without any edits first to make sure you have the basics working. The export and import options are in the File menu of DB Browser for SQLite.

I've done that with tables from metadata.db to good effect… a long time ago.

And of course work on a copy of .calnotes.

Added: just noticed the notes table in notes.db is virtualised for FTS, not sure of the implications of that.

BR

[kovidgoyal]

I highly doubt using a third party sqlite tool is going to work. This is text is FTS indexed and has various things going on that are not encapsulated in notes.db, for instance it links to resources not stored in the db file. Editing it directly will likely break something.