Upgrade StorageFind 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!
Time saving advice appreciated!
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.
http://forums.zotero.org/discussion/928/find-replace-function/
but yes, we would like to have it.
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)
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
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.
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?
(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.)
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?
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.
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?