To extract journals' names from the library

xtanya

Hello!
Is there any way to see the whole list of journals (item type: journal article) that I have in my Zotero library? I mean not the whole bibliography but only journals names.

Thanks in advance!

bwiernik

1) Select all of the items in your library, right-click, and choose Export Items. Choose CSV and save the file.
2) Open the eported file in a spreadsheet program, such as Excel, Google Sheets, or LibreOffice Calc.
3) Filter the "Item Type" column to show only journalArticle items.
4) Select the "Publication Title" title column and copy its contents to a new sheet.
5) Use the Remove Duplicates function to retain just the unique titles.

tim820

I needed some code to get an alphabetical list of all journals in my library, to run WITHIN Zotero under Tools\Developer\Run Javascript. It draws from some other code snippets floating around here, with some additions/modifications. It includes a count of the number of papers in each journal. Here it is in case it's of use to others.

var rows = await Zotero.DB.queryAsync("SELECT value AS title, COUNT(*) AS num FROM itemData JOIN itemDataValues USING (valueID) WHERE fieldID=12 GROUP BY title ORDER BY UPPER(title) ASC");
var lines = [];
for (let row of rows) {
lines.push(row.title + '\t' + row.num);
}
return lines.join('\n');

AbeJellinek

The publicationTitle field won't have ID 12 in all libraries. Try this:

var rows = await Zotero.DB.queryAsync(`SELECT value AS title, COUNT(*) AS num FROM itemData JOIN itemDataValues USING (valueID) WHERE fieldID=${Zotero.ItemFields.getID('publicationTitle')} GROUP BY title ORDER BY UPPER(title) ASC`);
var lines = [];
for (let row of rows) {
lines.push(row.title + '\t' + row.num);
}
return lines.join('\n');

[edited to fix wrong field name]

tim820

@AbeJellinek thanks for catching that. Is that way that fieldID works documented anywhere ?

sdvr

I'm just making a link from here to this thread, where I asked a similar question. The code provided by @AbeJellinek did exactly what I needed, which was very helpful - thank you.

I'd like to modify it to work on the currently selected collection, but haven't been able to quite work this out (see the other thread, which does a similar thing but for an author list rather than publications).

https://forums.zotero.org/discussion/77813/0/#Form_Comment

AbeJellinek

@sdvr, try this:

var rows = await Zotero.DB.queryAsync(`
SELECT value AS title, COUNT(*) AS num
FROM itemData
JOIN collectionItems USING (itemID)
JOIN itemDataValues USING (valueID)
WHERE fieldID=${Zotero.ItemFields.getID('publicationTitle')}
AND collectionID=${ZoteroPane.getSelectedCollection(true)}
AND itemID NOT IN (SELECT itemID FROM deletedItems)
GROUP BY title ORDER BY UPPER(title) ASC
`.trim());
var lines = [];
for (let row of rows) {
lines.push(row.title + '\t' + row.num);
}
return lines.join('\n');

Will throw an error if you aren't in a collection, though.