Tutorial: Exporting Zotero to Excel

Zotero's a great tool for managing your citation library, but sometimes you need that data elsewhere, and though Zotero has some good export features, you may sometimes want something like a simple spreadsheet of citations to work with.Zotero runs on a SQLite database, which makes report generation and, thereby, conversion to a different format difficult for the lay user.  To export Zotero, we will open the SQLite database, submit a query, and then copy the results to Excel, Calc, or another spreadsheet program.Let's get started!

1. First, you will need to download and install the Firefox SQLite Manager.  Do a quick internet search to find it or go to "Tools > Add-ons" and search there.
2. Restart Firefox when prompted.
3. Now, click on the "Tools" menu and select "SQLite Manager."
4. In the manager, you will need to open your Zotero database, which is located in your Firefox profile folder.  Rather than querying the database directly, find the zotero.sqlite file on your machine (do a web search if you're unsure where your Firefox profile folder is stored on your particular operating system), and make a copy of it to another location (like your desktop).  Though we will not be making any changes to the database, it's better to be safe than to mess up your database.  In SQLite Manger, click the folder icon, find your copied database file, and click "Open."
5. This may take a moment.  If a popup comes up saying that it's taking a while, just say continue (your database may be large).
6. Now that your database is open, click on the "Execute SQL" tab in the right pane.
7. In the "Enter SQL" box, replace the default "SELECT * FROM tablename" with the following query:

   
/* Begin query. */
/* Basic SELECT statement. */
SELECT
/*Identify which fields to SELECT for your output. The left identifier represents the database abbreviated name (set below in the FROM and JOIN statements) and the database field name we want, while the right identifier (after the AS statement) represents the column header as it will appear in your output. */
i.itemID AS ITEMID,
title.value AS TITLE,
t.typeName AS TYPE,
d.value AS DATE,
issn.value AS ISSN,
isbn.value AS ISBN,
doi.value AS DOI,
url.value AS URL,
pub.value AS PUB,
issue.value AS ISSUE,
volume.value AS VOLUME,
series.value AS SERIES,
pages.value AS PAGES,
proceedingsTitle.value AS PROCEEDINGS,
bookTitle.value AS BOOK,
abstract.value AS ABSTRACT,
c1.firstName AS AUTHOR_1_FIRST,
c1.lastName AS AUTHOR_1_LAST,
c1.shortName AS AUTHOR_1_SHORT,
ct1.creatorType AS AUTHOR_1_TYPE,
c2.firstName AS AUTHOR_2_FIRST,
c2.lastName AS AUTHOR_2_LAST,
c2.shortName AS AUTHOR_2_SHORT,
ct2.creatorType AS AUTHOR_2_TYPE,
c3.firstName AS AUTHOR_3_FIRST,
c3.lastName AS AUTHOR_3_LAST,
c3.shortName AS AUTHOR_3_SHORT,
ct3.creatorType AS AUTHOR_3_TYPE,
c4.firstName AS AUTHOR_4_FIRST,
c4.lastName AS AUTHOR_4_LAST,
c4.shortName AS AUTHOR_4_SHORT,
ct4.creatorType AS AUTHOR_4_TYPE,
c5.firstName AS AUTHOR_5_FIRST,
c5.lastName AS AUTHOR_5_LAST,
c5.shortName AS AUTHOR_5_SHORT,
ct5.creatorType AS AUTHOR_5_TYPE,
t1.name AS TAG_1,
t2.name AS TAG_2,
t3.name AS TAG_3,
t4.name AS TAG_4,
i.dateAdded AS DATE_ADDED,
i.dateModified AS DATE_MODIFIED,
i.key AS ZOTERO_KEY,
extra.value AS EXTRA
/* The general FROM statement merely queries the `items` table. This table does not have much information in it, so all the information we want will be added with JOIN commands below. */
FROM
items i
/* Begin JOIN commands. First JOIN command.
Join command. Depending upon the information (whether there is one value or many and whether the value is essential to returning a row), we may use either INNER JOIN or LEFT JOIN. */
INNER JOIN
/* The database table name (on the left) and the abbreviated table name (on the right), which we reference in our SELECT query above. */
itemDataValues title
/* Identifies the conditions that direct what data is attached to the `items` table based upon the JOIN command. */
ON
title.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'title' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
/* Additional JOIN statements. Each of these statements is necessary to attach a new table of information to our initial `items` table. */
LEFT JOIN
itemTypes t
ON
t.itemTypeID = i.itemTypeID
LEFT JOIN
itemDataValues issn
ON
issn.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'ISSN' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues doi
ON
doi.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'DOI' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues isbn
ON
isbn.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'ISBN' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues url
ON
url.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'url' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues pub
ON
pub.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'publicationTitle' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues d
ON
d.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'date' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues abstract
ON
abstract.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'abstractNote' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues pages
ON
pages.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'pages' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues issue
ON
issue.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'issue' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues series
ON
series.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'series' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues volume
ON
volume.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'volume' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues proceedingsTitle
ON
proceedingsTitle.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'proceedingsTitle' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
itemDataValues bookTitle
ON
bookTitle.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'bookTitle' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
LEFT JOIN
creatorData c1
ON
c1.creatorDataID = (SELECT creatorDataID FROM creators WHERE creatorID = (SELECT creatorID FROM itemCreators WHERE itemID = i.itemID LIMIT 0,1) LIMIT 1)
LEFT JOIN
creatorTypes ct1
ON
ct1.creatorTypeID = (SELECT creatorTypeID FROM itemCreators WHERE itemID = i.itemID LIMIT 0,1)
LEFT JOIN
creatorData c2
ON
c2.creatorDataID = (SELECT creatorDataID FROM creators WHERE creatorID = (SELECT creatorID FROM itemCreators WHERE itemID = i.itemID LIMIT 1,1) LIMIT 1)
LEFT JOIN
creatorTypes ct2
ON
ct2.creatorTypeID = (SELECT creatorTypeID FROM itemCreators WHERE itemID = i.itemID LIMIT 1,1)
LEFT JOIN
creatorData c3
ON
c3.creatorDataID = (SELECT creatorDataID FROM creators WHERE creatorID = (SELECT creatorID FROM itemCreators WHERE itemID = i.itemID LIMIT 2,1) LIMIT 1)
LEFT JOIN
creatorTypes ct3
ON
ct3.creatorTypeID = (SELECT creatorTypeID FROM itemCreators WHERE itemID = i.itemID LIMIT 2,1)
LEFT JOIN
creatorData c4
ON
c4.creatorDataID = (SELECT creatorDataID FROM creators WHERE creatorID = (SELECT creatorID FROM itemCreators WHERE itemID = i.itemID LIMIT 3,1) LIMIT 1)
LEFT JOIN
creatorTypes ct4
ON
ct4.creatorTypeID = (SELECT creatorTypeID FROM itemCreators WHERE itemID = i.itemID LIMIT 3,1)
LEFT JOIN
creatorData c5
ON
c5.creatorDataID = (SELECT creatorDataID FROM creators WHERE creatorID = (SELECT creatorID FROM itemCreators WHERE itemID = i.itemID LIMIT 4,1) LIMIT 1)
LEFT JOIN
creatorTypes ct5
ON
ct5.creatorTypeID = (SELECT creatorTypeID FROM itemCreators WHERE itemID = i.itemID LIMIT 4,1)
LEFT JOIN
tags t1
ON
t1.tagID = (SELECT tagID FROM itemTags WHERE itemID = i.itemID LIMIT 1)
LEFT JOIN
tags t2
ON
t2.tagID = (SELECT tagID FROM itemTags WHERE itemID = i.itemID LIMIT 1,1)
LEFT JOIN
tags t3
ON
t3.tagID = (SELECT tagID FROM itemTags WHERE itemID = i.itemID LIMIT 2,1)
LEFT JOIN
tags t4
ON
t4.tagID = (SELECT tagID FROM itemTags WHERE itemID = i.itemID LIMIT 3,1)
LEFT JOIN
deletedItems
ON i.itemID = deletedItems.itemID
LEFT JOIN
itemDataValues extra
ON
extra.valueID = (SELECT itemData.valueID FROM itemData WHERE itemData.fieldID = (SELECT fieldID FROM fields WHERE fields.fieldName = 'extra' LIMIT 1) AND itemData.itemID=i.itemID LIMIT 1)
/* End JOIN statements. */
/* Optional JOIN Statements. */
/* This is an optional JOIN statement to select for a specific collection. To only show a specific collection in your output, remove the double-hyphen from the beginning of the statement and replace the collectionName value (e.g. "Instructional Design") with the name of your desired collection (encased by quotes). */
--INNER JOIN collectionItems ON collectionItems.itemID = i.itemID AND collectionItems.collectionID = (SELECT collectionID FROM collections WHERE collectionName = "Instructional Design" LIMIT 0,1)
/* End optional JOIN statements. */
/* Final conditional WHERE statement allows us to ignore entries that have been deleted in Zotero. */
WHERE deletedItems.itemID IS NULL
/* If you would like to add any additional selection criteria, this is the place to do it. Here are some examples that you may wish to use. Merely remove the double-hyphen from the beginning of a condition to make it active in the query. */
/* Begin optional conditions. */
/* Only show journal articles. */
--AND t.typeName = "journalArticle"
/* Only show items where the first author's last name is Smith. */
--AND c1.lastName = "Smith"
/* Only show items from a particular publication. */
--AND pub.value = "Teachers College Record"
/* Only show items added to Zotero after January 1, 2010. */
--AND i.dateAdded > "2010-01-01"
/* End optional conditions. */
/* End query. */


8. Click "Run SQL." (Note: We are not making any changes to the database, just running a Select query. If you would like to make any conditional queries on the database, you can use the notes in the query to help you edit it directly, or you can just wait until you get the data to a spreadsheet program. Feel free to hit "Run SQL" as you make edits to see how the output changes.)
9. In the bottom pane, you will see your output.  Left-click on the first entry, scroll all the way down, and then shift+left-click on the last entry to select them all.
10. Right-click on the selected entries and select "Copy Row(s) as CSV" (or use the MS Excel compatible option if appropriate).
11. Finally, open your favorite spreadsheet program, paste your results, and you're done.  (Note: If your spreadsheet does not break up the values properly, you will need to paste the text into Notepad or a similar text editor and save it as a file with the .csv extension.  Then, go back to the spreadsheet and import the .csv file.)

Let me know if this was helpful for you!