Find and replace on multiple items

There's a mistake in a journal title, and I need to fix it in a few dozen entries. Is there any way to do a find and replace? Or do I have to search for it and then change it manually in each one?

Time saving advice appreciated!
«1
  • I have the same question!!! Anyone out there to help???
  • Not within Zotero unfortunately. I agree this would be nice.
    I'd suppose one could change the database directly, but I don't understand enough sqlite to even start to suggest that or how you do that.
  • old request see:
    http://forums.zotero.org/discussion/928/find-replace-function/
    but yes, we would like to have it.
  • Once this old ticket for batch editing Zotero data is taken care of it should be much easier to clean up data like this.
  • edited March 25, 2010
    I was able to do some "batch edits" using R's RSQLite library. I'd imported my data from an EndNote RIS export file and had a mess of URLs of the form "file://..." that I wanted to delete. Here is the script (assuming you have setwd() to the dir containing your zotero data). I have no idea about the overall structure of the database files but was able to do this basically by snooping in the database. This was done by an R script that read and dumped (appended) each table's name and contents to a text file that I was then able to search through. Once I found the pattern I was looking for I substituted what I didn't want with a null string.

    You will need to close the browser to unlock the database. MAKE BACKUPS AND USE AT YOUR OWN RISK.

    #This script creates a text dump of the database:
    library("RSQLite")
    m <- dbDriver("SQLite")
    con <- dbConnect(m, "zotero.sqlite")
    tables <- dbListTables(con)
    for(i in tables) {
    cat(paste("\n\n", i), file="data.txt", append=T)
    dat <- dbReadTable(con, i)
    write.csv(dat, file="data.txt", append=T, row.names=F)
    }
    dbDisconnect(con)

    #This script replaced any values in the "value" field "file:.*" with ""
    library("RSQLite")
    m <- dbDriver("SQLite")
    con <- dbConnect(m, "zotero.sqlite")
    datx <- dbReadTable(con, "itemDataValues")
    file.r <- grep("file:", dat$value)
    grep("file:", dat$value, value=T)
    dat[file.r,"value"] <- ""
    dbWriteTable(con, "itemDataValues", dat, row.names=F, overwrite=T)
    dbDisconnect(con)
  • edited August 23, 2010
    Phurvitz, thanks for your R script - that saved me a LOT of time!

    Maybe other can profit from my script as well: I used R to normalize the first names of the authors (I imported my bibliography from Bookends and I had everything in it: "A.B.", "A. B.", "AB"., "Alex B.", etc.).
    When different versions of these intials are present, Zotero always puts the intials in front of the last names in in text citations, what was very annoying. With following script, everything is set to initials, followed by a dot and a space (except the last initial):

    ---------------------------
    # You have to close firefox before manipulating the DB!
    # setwd() to the dir containing your zotero data
    library("RSQLite")
    m <- dbDriver("SQLite")
    con <- dbConnect(m, "zotero.sqlite")

    authors <- dbReadTable(con, "creatorData")

    first <- authors$firstName
    # Replace names with their initials, followed by ". "
    # Find strings with 2 or more word characters, followed by a space or the end of the string, only store the first character
    first.rev <- gsub("(\\w)\\w+(\\s|\\z)", "\\1.", first, perl=TRUE)
    # Make sure that a dot is after each initial
    first.rev <- gsub("(\\w)(\\W|\\z)", "\\1.", first.rev, perl=TRUE)
    # Make sure that a space is afer each dot
    first.rev <- gsub("\\.(\\S)", ". \\1", first.rev, perl=TRUE)

    # test
    cbind(first, first.rev)

    authors$firstName <- first.rev

    dbWriteTable(con, "creatorData", authors, row.names=F, overwrite=T)
    dbDisconnect(con)
    ---------------------------

    Here's a part of the results, first column is the original, second is the normalized version:

    [1107,] "M. R" "M. R."
    [1108,] "Joseph A" "J. A."
    [1109,] "David A" "D. A."
    [1110,] "H. Clark" "H. C."
    [1111,] "Peter M." "P. M."
    [1112,] "Geoffrey F." "G. F."
    [1113,] "Philip W." "P. W."
    [1114,] "Kipling D." "K. D."
    [1115,] "Christopher K. T." "C. K. T."
    [1116,] "Wilma" "W."
    [1117,] "R. W." "R. W."
    [1118,] "" ""
    [1119,] "RF" "R. F."

    Have fun,
    Felix
  • Zotero data are stored in a SQLite database. A easy way may be to use the SQLite Data Browser (http://sqlitebrowser.sourceforge.net/) and to change text with SQL.

    e.g. UPDATE [table] SET [fieldname1] = REPLACE( [fieldname1] , ’searchstring’, ’replacestring’) WHERE [fieldname1] LIKE ’%searchtext%’;

    The zotero database is perfectly normalised, knowledge about relationale Databases and SQL is needed.

    Remark: Because the zotero database gets locked opening Firefox the addin for SQLite can't be used.
  • edited January 7, 2012
    The sqlitebrowser (open source, download at http://sourceforge.net/projects/sqlitebrowser/ or use 'sudo apt-get install -my sqlitebrowser' for Debian based systems like Ubuntu) found and replaced entries when the SQL string UPDATE itemDataValues SET value = 'correct entry' WHERE value = 'incorrect entry' was executed, but the lock that Zotero puts on the zotero.sqlite won't allow me to save my changes, even on a copy of the database file. Anyone know a way to unlock then relock the zotero.sqlite file?
  • Before making changes, close Firefox and Standalone, then make a backup copy of zotero.sqlite, then open the file in sqlitebrowser. There shouldn't be a lock when Firefox is closed.
  • Thank you, that makes perfect sense. I closed Firefox, backed up zotero.sqlite, opened zotero.sqlite in sqlitebrowser, ran the UPDATE query, saved, then ran a SELECT query to see if the UPDATE query worked and see that nothing has changed even though the UPDATE query ran with no errors.

    Then I killed the Firefox process just to be sure and repeated the process, but the SELECT test still returns the original entry.

    At this point I start thinking, "What if the file is read only? I was already the owner with read and write permissions so I changed it to read, write, and execute for the owner and the group (chmod 770 zotero.sqlite) just to be paranoid, then went though the above steps again but the SELECT test still returns the original entry.

    How can an UPDATE query run with no errors, yet not change anything?
  • It works fine for me here, with the same sqlitebrowser client. Be sure you have Firefox completely closed when you make the copy of the file, and be sure that your WHERE statement actually does match the record(s) you're targeting. Not sure what you're trying to modify, but if you're wanting to normalize names, they're not contained in itemDataValues (which holds the strings only for dates and ordinary fields), but in creatorData.

    (Be careful out there, and be sure to keep a spare copy of the database before modification, even if it seems to work. The Zotero DB is a Swiss watch inside, and making direct modifications can be risky.)
  • This time I logged out, logged back in, and went right to sqlitebrowser then ran UPDATE itemDataValues SET value = 'Foundation for Economic Education' WHERE value = 'The Foundation for Economic Education' to get all the publisher data to be uniform (most are Foundation for Economic Education but some are The Foundation for Economic Education) without ever opening Firefox, yet this still didn't work. UPDATE runs with no error, yet SELECT value FROM itemDataValues WHERE value = 'The Foundation for Economic Education' returns The Foundation for Economic Education instead of nothing as it is supposed to after the update.

    I read on a forum somewhere that, "You can toggle extensions.zotero.dbLockExclusive to false" but I have no idea how to do that. Is that a registry edit?
  • Don't touch dbLockExclusive. You'll destroy your database. And it would have no effect unless you were already doing something very wrong.
  • Okay, but that kind of leaves me with no options and a database that I cannot change because of a still mysterious reason.
  • edited January 11, 2012
    Well, it's just an SQLite file—how you manage to edit that is really up to you. But in any case, SQL-based editing is completely unsupported by us and not recommended. (What you were doing also wouldn't have caused the updated items to sync to the server.)

    The better way to do this is via Zotero itself. You can install the Execute JS Firefox extension and interact with Zotero via JavaScript. Back up your database first, obviously, and temporarily disable auto-sync in the Sync pane of the Zotero preferences.

    In Execute JS, switch the target window to an open browser window, paste this in to the "JS-Code to execute" box, and edit the first three lines as necessary:

    var fieldName = "publicationTitle";
    var oldValue = "Foo";
    var newValue = "Foo2";

    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();
    }
    alert(ids.length + " items updated");
    }
    else {
    alert("No items found");
    }

    Then click "Execute".

    The list of field names to use is here.
  • I went ahead and posted your example code to the client API documentation: http://www.zotero.org/support/dev/client_coding/javascript_api#examplebatch_editing
  • Thank you, thank you, and thank you. Your java script worked on the first try, far superior to the pointlessly risky sqlite querying. I hope one day your script becomes the basis of adding a search and replace feature to Zotero.
  • edited January 11, 2012
    I tried this for the language field putting "language" instead of "publicationTitle" in the first line and "English" instead of "Foo" in the second and "EN" instead of "Foo2" on the third. I got an error relation to the item.SetField, but if I let "Foo" and "Foo2", which is not written in any of my records for that field, stay in line 2 and 3 I got a message stating no records found. Does it not work with the language field or have I done something wrong?
  • I got an error relation to the item.SetField
    What error?
  • I am at the wrong computer. I'll report tomorrow morning.
  • edited January 11, 2012
    I receive "No items found" when the script is run as is. The same happens if I only change "publicationTitle" to "language". If I then change line 2 to "var oldValue ="English";" (which I know is found in some records) and line 3 to "var newValue = "EN";" I get the message "Field not specified in Item.setField()". So it seems that it will not do anything with real data. I have not changed anything else in the Execute JS windows. The target window is "Zotero - Mozilla Firefox". I have disabled sync and made a backup copy. I have also previously used Zotero localized using Norwegian locale, but now it is running using English as the locale of the program but Firefox is running using Norwegian locale.
  • edited January 11, 2012
    I've updated the script above and in the documentation to fix the "Field not specified in Item.setField()" error with certain fields.
  • edited January 11, 2012
    Thanks, it works as expected also for the language field now. Would it BTW, be possible to make the addon ExecuteJS available also for Standalone?
  • I modified the script to replace some dates ....

    var fieldName = "date";
    var oldValue = "2003 3-5th July";
    var newValue = "2003 July 3-5";

    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();
    }
    alert(ids.length + " items updated");
    }
    else {
    alert("No items found");
    }

    But it doesn't find any items (when, of course, they are there). Have I specified the field name incorrectly, or is it some other problem?
  • since Zotero parses dates, I'd expect the date field to work differently - someone else will have to provide specifics.
  • Ah ... that might explain why it sometimes works (e.g., a date like 2005-12-03 --> 2005 Dec 3, works as expected).
  • Then, I've begun to wonder about replacing sub-strings (For example, a lot of the URL's have our proxy server name embedded in them, and I'd like to remove them).
  • With this solution, you're working in a full-featured JavaScript environment, so you certainly could pull up all the items with the desired substring and use regular expressions to fix them up. Just look online at regular expressions for JavaScript.
  • edited May 2, 2013
    I am looking to just change part of a URL in the "url" field due to a domain change, eg https://domainname.com/id:A399779/document/versions/latest to be changed to https://NEWdomainname.com/id:A399779/document/versions/latest . Dan Stillmans code above only replaces the full string in a record. I have ~ 1000 records to change. I am not so familiar with Javascript but could anybody point me in the right direction to modify Dan's script to achieve this? Dan are you still about? This would help a lot with what seems to be a long process of updating.
  • edited May 2, 2013
    Further to above, as I have a list of all the URLs needing to change, I could possibly modify Dans script to accommodate an array of all the URLs to be changed with a matching array of new urls.
Sign In or Register to comment.