Taking Description text from database > editing in a text file > placing back into database

Post Reply
Posts: 1
Joined: 14 Feb 22 2:52

Taking Description text from database > editing in a text file > placing back into database

Post by bssmith »

PS, 64-bit Windows.

Of my ~10,000 image catalog, approximately 3,000 items have a sentence in the Description field which I need to find and remove. This is not the only sentence in each Description across those 3,000 items, but it’s the only sentence I need to remove (the rest of the text in each Description should be unchanged).

Each sentence I want to remove starts with “Download a full copy” and ends with “.pdf” but the interior of each sentence is different. Typically, the difference is the filename which precedes the .pdf at the end of the sentence, although there are other differences/exceptions as well. I don’t believe I can run a regex search-and-replace from within Photo Supreme. And I don’t want to go through 3,000 items one at a time to edit the Description field.

I duplicated my catalog database and worked with that as my working copy, not the original database.

Using SQLiteStudio, I discovered that the contents of the Description field are (apparently) stored in the ImageDescription column within the idCatalogItem table. I was able to copy that entire ImageDescription column at once (for all 3,000 records) to my clipboard, and paste those values in my regex-ready text editor. There, I found the target sentences very quickly via a regex search on Download(.*).pdf and I then deleted the offending sentences all at once across all 3,000 records within the text file - so far, so good.

I then copied/pasted the “cleaned” descriptions from the text file right back into the same table/column in the working database (in the same order). After I committed these changes to the working database, I moved/renamed my original database for safety, renamed the working database to match the original database name, moved the renamed working database to the location expected by Photo Supreme, and started Photo Supreme.

But the Descriptions I see within Photo Supreme (now presumably being pulled from the renamed working database) have not changed at all! Those offending sentences, with the text that needs to be removed, are still there.

Now, I am wondering if...

1) I modified the wrong table/column entries within the database - perhaps that Description data is actually stored someplace other than idCatalogItem > ImageDescription?
2) Or perhaps I indeed found the correct location within the database, but I need to somehow tell Photo Supreme to reindex the database to see those new/changed Descriptions?
3) Something else I need to do to refresh this data?

I found some v5 release notes suggesting that a catalog database reindex occurs automatically every four times the database is compacted. I compacted the database four times in a row, but I’m still seeing the “old” Descriptions within the Photo Supreme UI.

I can always go back to my original database (which is unchanged) but I am hoping I am overlooking some final step here to “refresh” the Description text I am seeing within the Photo Supreme UI, so that it reflects the changes I made.

Any thoughts or advice would be appreciated!

-– Ben
Posts: 7281
Joined: 13 Sep 03 7:24

Re: Taking Description text from database > editing in a text file > placing back into database

Post by Hert »

A warm welcome to the forum.

You can query a database if needed, but never(!) make changes to the database outside of the application.

You can use this script to update the Description field with content from a CSV formatted file.
https://repository.idimager.com/openres ... D96EA3BFD6
This is a user-to-user forum. If you need product support then please send a message
Post Reply