Find and replace on multiple items

crinne

Edit the data in the SQLite database ist self, you may choose a GUI to do this, for example Mozilla Add-On SQLite Manager.
The id of the field url is '1', s. table fields.
Querry the table itemDataValues for the old domaine name:
SELECT value FROM itemDataValues WHERE value LIKE '%<oldname>%';
To be more precice you may select on the id of field url too.
SELECT value FROM itemDataValues WHERE (value LIKE '%<oldname>%' AND itemDataValues.valueID IN (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID='1'));
Now you got the list of all old domainnames. To replace the old name:
UPDATE itemDataValues SET value = REPLACE([value],'<oldname>','<newname>') WHERE (value LIKE '<oldname>%' AND itemDataValues.valueID IN (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID='1'));
One possible problem: there's a index on the field value. If the new value exists, the REPLACE command will not be executet. In this case you have to replace the corresponding valueID in the table itemData.

Make a backup of your zotero database in advance!
Good luck

dstillman

I just want to clarify here that, if you're using Zotero's syncing functionality (and really even if you're not), you don't want to be making direct SQL changes as in crinne's example. You can accomplish the same thing via JavaScript, using Zotero's search and item saving functions, and the latter will properly update items so that they sync and do other things to ensure the integrity of the database.

Justin Damic1

Yes thanks Dan for clarifying that, I read your previous comments hence I pursued the JS method, and I have had success with the following code which is just an adaptation of yours with an array and For loop. Here it is should other find same issue.

var oldValueArray=new Array("A","B","C".....etc)
var newValueArray=new Array("A1","B1","C1".....etc)
var numberInArray = whatever number of items in arrays minus 1

for (var i=0; i<numberInArray; i++)
{
var fieldName = "url";
var oldValue = oldValueArray[i];
var newValue = newValueArray[i];

var fieldID = Zotero.ItemFields.getID(fieldName);
var s = new Zotero.Search;
s.addCondition(fieldName, 'is', oldValue);
var ids = s.search();
if (ids) {
for each(var id in ids) {
var item = Zotero.Items.get(id);
var mappedFieldID = Zotero.ItemFields.getFieldIDFromTypeAndBase(item.itemTypeID, fieldName);
item.setField(mappedFieldID ? mappedFieldID : fieldID, newValue);
item.save();
}

}
else {
alert(oldValueArray[i] +"Not found");
}
}

tanaree

I apologize if thread necromancy is frowned upon, but this thread seemed most relevant to the question I wanted to ask.

I recently imported an RIS file with italic information in some article titles in the format of {Icontents}, almost exclusively species names. If I had known this was there beforehand, I could have run a regular expression based substitution on the RIS to replace it with HTML tags before import, but now that it's in (and is several thousand items long, with several hundred with the italic tag), I would prefer a way to fix it directly in the database. The example code provided seems to be searching for an exact match.

In this case, however, I think I can get away with just searching for the initial "{I" -- does the following modification to Dan Stillman's code look sane? It's my first foray into Javascript, so I'm not completely sure of my syntax. What it should do is it apples a getField to the current item to retrieve the title as a string, runs a global regexp replacement on it changing {I } to HTML italics and saves it to newValue, then finally writes that modified string back in.

var fieldName = "title";
var oldValue = "{I";

var fieldID = Zotero.ItemFields.getID(fieldName);
var s = new Zotero.Search;
s.addCondition(fieldName, 'contains', oldValue);
var ids = s.search();
if (ids) {
	for each(var id in ids) {
		var item = Zotero.Items.get(id);
		var mappedFieldID = Zotero.ItemFields.getFieldIDFromTypeAndBase(item.itemTypeID, fieldName);
		var tempTitle = item.getField('title');
		var regExFilt = /\{I([A-Za-z .]+)\}/g;
		var newValue = tempTitle.replace(regExFilt, "<i>$1</i>");
		item.setField(mappedFieldID ? mappedFieldID : fieldID, newValue);
		item.save();
	}
	alert(ids.length + " items updated");
}
else {
	alert("No items found");
}

adamsmith

looks good to me. Obviously make a back-up of your Zotero database:
https://www.zotero.org/support/zotero_data#backing_up_your_zotero_library
before you try.

tanaree

Everything went off perfectly, and the tags are now corrected. Thanks!

John_muccigrosso

Does execute.js still work?

On my Mac I get a "Missing ( after for" error. If I take out the "each" I then get "ReferenceError: Zotero is not defined".

adamsmith

execute.js still works. What exactly are you using as code (and note that you need to select on open browser window for the code to run. That'd be the "Zotero is not defined" error.)

edit: FWIW, "for each" is deprecated, but it should still work and does for me.

John_muccigrosso

Hmmm, I unchecked the "Content Window" box (which I think I had checked when trying to troubleshoot), and it's working for some values, but not others. Instead I'm getting this error:

Cannot edit item in read-only Zotero library

adamsmith

Sounds like it's for some reason searching a group you belong to? That'd be the only reason I can think of for this.

John_muccigrosso

I was thinking the same thing and it's certainly possible.

Is it possible to prevent that?

John_muccigrosso

Pretty sure this is what's happening. Is there some way to have the js search only the local library?

dstillman

var s = new Zotero.Search;
// Exclude group libraries
var groups = Zotero.Groups.getAll();
for (let group of groups) {
s.addCondition('libraryID', 'isNot', group.libraryID);
}
Or a bit easier in the latest 4.0 Beta (4.0.27+):

var s = new Zotero.Search;
s.libraryID = null;

John_muccigrosso

Ah, that did it. Thanks!

And for posterity, here's my script, which changes one field based on the value of another. I'm using it to insert journal abbreviations by journal name. Setting the two field names to the same thing should work just like the other version.


var checkFieldName = "[fieldname1]";
var targetFieldName = "[fieldname2]";
var searchValue = "Foo";
var replaceValue = "Bar";

var fieldID = Zotero.ItemFields.getID(checkFieldName);

var s = new Zotero.Search;
// Exclude group libraries
var groups = Zotero.Groups.getAll();
for (let group of groups) {
s.addCondition('libraryID', 'isNot', group.libraryID);
}


s.addCondition(checkFieldName, 'is', searchValue);
var ids = s.search();
if (ids) {
for each(var id in ids) {
var item = Zotero.Items.get(id);
var mappedFieldID = Zotero.ItemFields.getFieldIDFromTypeAndBase(item.itemTypeID, targetFieldName);
item.setField(mappedFieldID ? mappedFieldID : targetFieldName, replaceValue);
item.save();
}
alert(ids.length + " items updated");
}
else {
alert("No items found");
}

MalcolmHLevitt

This is exactly what I want to do, i.e. insert an abbreviation for a journal in the Journal Abbr. field, based on what is in the Publication field.

However I have no experience with Javascript or the programs you mention. Can someone give me a step-by-step idiots guide for implementing the code above? I am using Zotero on a Mac with OS X Yosemite.

Which programs do I need to install to run that code, and how do I do it?
thanks in advance M

Gracile

Instructions here: https://www.zotero.org/support/dev/client_coding/javascript_api#batch_editing
(backup your library before)

Just change the first four lines ([fieldname1] [fieldname2] Foo Bar). The field names are here.

MalcolmHLevitt

thanks - I tried to use this, but it did not work and I do not know if I am doing something wrong. I installed Execute JS in Firefox and copied the script into the window and run execute. But nothing happened. What should I substitute for

var checkFieldName = "[fieldname1]";
var targetFieldName = "[fieldname2]";

?

I guess I either need a clear and unambiguous step-by-step set of instructions, or I'll just leave it for now.

MalcolmHLevitt

apologies - apologies - it worked after some changes !!

For future reference and clarity, I used the following first four lines of your code.

var checkFieldName = "publicationTitle";
var targetFieldName = "journalAbbreviation";
var searchValue = "The Journal of Chemical Physics";
var replaceValue = "J. Chem. Phys.";

Before I hadnt realised that the square brackets had to be removed.

Thanks again

adamsmith

you do realize that Zotero can automate journal abbreviations in Word/LibreOffice, right? This is for something else?

MalcolmHLevitt

thanks, yes, I did discover that. But I want to use Zotero to format references I can paste into multiple types of graphics, not just Word (which I try to avoid as much as possible). So I'd much prefer to have these in the library from the beginning.

I only just switched to Zotero from EndNote and although I am very impressed by many things I see now that there are some things I don't like. In particular I very much miss having a window where I can see the formatted bibliography entry in a style of my choice. Just looking at what you're about to paste across seems +very+ messy and clumsy in Zotero. Anyone know how to get Zotero to open a simple window giving you a choice of bibliography styles and a display??

Matt Jans

I've never done script programming like this in Zotero, but it looks like it might help with my problem. I decided that some of the organizing I've been doing as collections, I want to do as tags instead. Since there's no point-and-click to edit tags on selected items, this looks promising. But what I want to do is just add a tag to each item in the collection, where the tag will be the same name as the collection. Then I can move those items up a level and delete the original sub-colleciton. The part I'm not clear on is how I would only do this for items in a specific collection or sub-collection. I don't want to add a tag to all items. Any ideas would be helpful.

Thanks!

-Matt

dragonfly

I've played around with CustomButtons add-on in Firefox.

https://addons.mozilla.org/en-GB/firefox/addon/custom-buttons/

And also All-In-One Sidebar add-on which allows custom buttons to be positioned in vertical toolbar.

Then you can add calls to Zotero javascript API into a custom button.

You do need to get to grips with the javascript API however ..

https://www.zotero.org/support/dev/client_coding/javascript_api

and CustomButtons coding ..

http://custombuttons.sourceforge.net/forum/viewforum.php?f=2

For example I have a custom button which clears defined tags on selected items.

But .. its not clear what will be the future of such add-ons when Mozilla changes architecture and drops XUL support.

Gracile

@Matt Jans: You don't need any script to do that.

1. Create a new tag "Collection_name" by adding it to one item.

2. Make sure the Tag Selector is open in the left pane.

3. Select all the items which are in the (sub-)collection : Command-A on Mac OS X or Control-A on Windows.

4. Once they're all selected, drag them (from the middle pane) onto the tag "Collection_name" in the Tag Selector. (The filter box at the bottom of the tag selector can be used to search tags, which is useful when you have a lot of tags. Only the tags containing the search string are shown, e.g. "Collection_name".)

kmpenner

Building on some posts in this thread, I've written a script that works to search and replace on most fields, including creators (by parsing the creators object into JSON). It operates on the currently selected items, not the whole database.
You can paste the script into Tools->Developer->Run JavaScript and modify the first few lines of code to suit your needs.
The example standardizes my name to Penner, Ken M.
Improvements will be made to the version on GitHub at https://github.com/kmpenner/Zotero-replace

// For creators, the JSON looks like [{"fieldMode":0,"firstName":"Ken","lastName":"Penner","creatorTypeID":1}]
// so the regExFil should include the colon and double quotes like /"firstName":"Ken(neth)?( M\.)?","lastName":"Penner"/gm
// and the replaceText should be in single quotes and double quotes like '"firstName":"Ken M.","lastName":"Penner"'

var fieldName = "creator";
var regExFilt = /"firstName":"Ken(neth)?( M\.)?","lastName":"Penner"/gm;
var replaceText = '"firstName":"Ken M.","lastName":"Penner"';

var oldValue = "";
var changes = 0;
var fieldID = Zotero.ItemFields.getID(fieldName);
var items = Zotero.getActiveZoteroPane().getSelectedItems();

await Zotero.DB.executeTransaction(async function () {
for (let item of items) {
switch (fieldName) {
case "creator": oldValue = JSON.stringify(item.getCreators()); break;
default: oldValue = item.getField(fieldName);
}
newValue = oldValue.replace(regExFilt, replaceText);
if (newValue != oldValue) {
changes++;
switch (fieldName) {
case "creator":
item.setCreators(JSON.parse(newValue)); break;
default:
let mappedFieldID = Zotero.ItemFields.getFieldIDFromTypeAndBase(item.itemTypeID, fieldName);
item.setField(mappedFieldID ? mappedFieldID : fieldID, newValue);
}
await item.save();
}
}
});
return changes + " item(s) modified";

DonnaCoxBaker

Thank you, KMPENNER. I am not a programmer, so I'll be stumbling through this--but I'm glad there looks to be a way to do some search-and-replace operations. It seems like it should be the most basic thing to go on the improvement list in future for Zotero. I need to search and replace "*AQ:" with "Q:" in a batch of freestanding notes. Does your code work on the notes? What would be the field ID, and do I need to handle the punctuation in a special way? Thanks for any guidance you can give.

adamsmith

This is very much on the list of planned features -- my understanding is relatively close to the top -- but doing so in a way that's both flexible and usable enough for everyone and not likely to lead to total disasters when used incorrectly is not trivial.

schoeneh

I wrote some simple JavaScript-Code that prompts the user: 1) the field to be searched, 2) the string to replace, 3) the new string - then it shows the number of (case-sensitive) results and a preview of one old+new field-contents. After confirming, it performs the batch-editing and returns the number of changed items:
https://github.com/Schoeneh/zotero_scripts/blob/main/search&replace/README-search_replace.md