Excel Alma Catalog Lookup Tool

Owned by Thomas Ventimiglia
Last updated: Jul 15, 2024
4 min read

This is an Excel plugin that allows one to search Alma using data in a spreadsheet and retrieve bibliographic and holdings data for the records it finds.  It can be used for simple yes/no queries (e.g. "Which ISBNs in this list can be found in the catalog?") or to generate more detailed reports (e.g. "Retrieve the titles, location codes, and MMS IDs for all the call numbers in this list").  While there is some overlap between the functionality of this tool and Alma Analytics, some users may find this tool easier to use for simple queries.  The entire process runs within Excel, and the catalog data can be added to an existing spreadsheet.  The full documentation can be found at the following github site:

https://github.com/pulibrary/ExcelAlmaLookup#readme

Below is a brief "Quick Start" guide.  First, download the installer from the following link:

https://github.com/pulibrary/ExcelAlmaLookup/releases/latest/download/CatalogLookupInstaller.exe

Any user can install the tool; it is not necessary to have administrative access to the computer. However, be sure to quit Excel before launching the installer.  (On some computers, you may receive a warning that "Windows protected your PC".  If so, you can simply click "More Info" and then "Run Anyway" to complete the installation.)  After running the installer, open a spreadsheet containing the data you want to search for.  You will see a new tab at the top of the Excel window called "Library Tools".  If you click that tab, you will see a button labeled "Look up in Local Catalog"

Highlight the column or cells containing the data you want to search for (for example, a column of ISBNs), then click the button.  A dialog will appear.  The first time the tool is run, the user must enter the Alma SRU Base URL at the top of the dialog:

For Princeton, use the URL https://princeton.alma.exlibrisgroup.com/view/sru/01PRI_INST

(Alternatively, one can search the sandbox with the URL: https://princeton-psb.alma.exlibrisgroup.com/view/sru/01PRI_INST)

Click "Add URL to list" to save this URL for future use.  One can also add and save the URLs for other institutions, and select the desired URL from the drop-down list.  This is a non-intrusive way to search the holdings of other institutions that use Alma.  For most institutions, the SRU Base URL will have a similar format to ours, but with a different prefix before the ".alma" and a different institution code in place of "01PRI_INST".  The following site lists the connection information for many different institutions: https://irspy.indexdata.com/

After entering the SRU Base URL, you can configure other settings for your query in the dialog:


  • "Range of cells to look up" should already contain a reference to the cells containing your search data, but you can use the button to the right of this field to adjust the range if needed.
  • Set "Field to Search" to indicate the kind of data in the selected cells (in this case, ISBNs).  If the data type does not appear in this menu, you can choose "Keyword", or click the "Additional fields..." button to see a complete list of available search fields.
  • "Leftmost result column" indicates the column where the search results will be output.   If more than one result type is selected, the others will be put in consecutive columns to the right of the first.
  • "Result types" indicates the type of data elements to retrieve from the catalog.  "True/False" will simply put TRUE in the results column if the search key in that row was found, and FALSE if not.  The drop-down menu gives other options for specific fields to retrieve, or you can specify one using the MARC field and subfield codes.  The explanation in the dialog describes how to specify such fields.
  • Click “OK” to begin the lookup process. You will see the tool populating the result column(s) with the retrieved values. Hidden rows will be skipped. If a record contains multiple instances of the desired result field/subfield, then all instances will be placed in the result cell, separated by “broken vertical bar” characters (¦). If multiple bibliographic records are retrieved by a single search value, the desired field from each record will be placed in the result cell, separated by solid vertical bars (|).

Beta version (1.3.0) for non-Alma sources

Though not officially released, there is a beta version of the tool that can search certain non-Alma catalogs relevant to PUL, specifically ReCAP (SCSB), IPLC ReShare (BorrowDirect), Library of Congress, and WorldCat.  If you are interested in trying out this version, please let Tom Ventimiglia know. The installer for the beta version can be downloaded from:

https://github.com/pulibrary/ExcelAlmaLookup/releases/download/v1.3.0-beta/CatalogLookupInstaller.exe

The specific features supported by the beta version are subject to change prior to the official release, but the following notes describe in general how it works:

  • A new button "Non-Alma Sources", now appears under the "Base URL" field. Clicking this button will show a list of supported catalogs. You can select one of these to add it to the list of available sources. It will then appear in the drop-down list alongside any Alma URLs already there. (Rather than a URL, these sources will appear in the list as the text "source:" followed by an abbreviation for that source). Simply select the source from the drop-down to search it.
  • The first time you try to search WorldCat, you will be prompted for a username and password. You can use the same login that you use for OCLC Connexion (and save the login so you don't have to enter it each time).
  • The supported search types may be more limited compared to Alma catalogs.  When searching ReCAP, one cannot retrieve fields using MARC codes.  The supported result types are shown in the drop-down menu.  Note that keyword searching cannot be used to search for OCLC numbers in WC; the "OCLC No." search type must be used.
  • For some sources, a "holdings" result type is available, which will generate a list of holding institutions.   For ReCAP, this produces a list of institution names separated by vertical bars.  For BorrowDirect, the institution codes along with the holdings status (e.g. "UNLOANABLE") are extracted from fields 999$s and $p of the retrieved records.  For WorldCat, the institution codes in field 945$c are retrieved.  A broken-bar-delimited list is generated for each retrieved record, and the lists themselves are separated by vertical bars.  Other holdings-related information (such as $a country, $b province, and $d institution name) can be retrieved by specifying the corresponding subfield of 945.