Folger Tooltips: Exporting a spreadsheet of catalog data

Want to make a spreadsheet of selected fields from your Folger catalog search results? All it requires is a free Folger catalog account (contact catalog@folger.edu if you don’t already have one), a little knowledge of MARC, and a little patience. To begin, log into the Folger catalog and search until the search results show just the records you want to export.

For this example, I decided to export information about all of the Flemish STC imprints in the Folger collection.

I began by selecting  the “STC imprints” collection of catalog records:

Then I check-marked two facets to limit the results:

• Place created: Belgium (there’s no “Flanders” facet because library catalogs use the modern form of city and country names)
• Item access: Vault (to exclude records for modern facsimiles, microfilm, and Early English Books Online scans)

I could have continued to refine the search (e.g. by adding NOT “preliminary record copied from” in the Search box to remove what’s left of the records described in the Collation post 24,000 “preliminary” catalog records are better than nothing!) but decided to stop searching and start demonstrating.

Once you’ve tailored the search to show only the records that you want, scroll to the bottom of the screen and select “Export records” at the lower right, then “Excel (all results).”

This brings up a text box asking you to “Enter the fields you want to export separated by comma” along with sample input.

Here’s the catch. Instead of typing the names of the fields as displayed in the catalog, you need to use the MARC coding that underlies the display. For instance, the “Created/published” field shows what’s encoded in the MARC 260 field.

For a full list of MARC fields displayed in the Folger catalog, see the Folgerpedia article “List of MARC fields displayed in the catalog.”

In this case, I entered 245,250,260a,260b,260c,300a,852h,008 in order to export the following:

• Title statement (245)
• Edition statement (250)
• Imprint separated into its three component subfields (260 subfield a, 260 subfield b, 260 subfield c)
• first part of the Physical description (300 subfield a)
• Call number (852 subfield h)
• Fixed-Length Data Elements (008), a non-displaying field with fixed-width data that makes it very useful in a spreadsheet, as you’ll see

After you hit “Export” the system will generate an .xls file and prompt you to save it. Depending on how many records you’re exporting, how many fields within those records, and how many other things the server is trying to do at the same time, this step could take an instant, or it could take minutes. It’s recommended that users limit exports to no more than about 1,000 records at a time.

By default, the suggested filename is “results.xls” and, as you can see from the screenshot, it’s a good idea to provide a more meaningful name before hitting the “Save” button. In this case, I went with “STC-Vault-Belgium”.

Here’s the other catch: the file says that it’s a .xls file, but in fact it’s a web-flavored .xls file. That means you’ll get a scary looking warning when you try to open it:

Go ahead and say “Yes”. The file is not corrupted or unsafe, it’s just inconvenient. After you’ve opened it, you can “Save As” to make it a proper Excel file.

You’ll get something like this:

Notice that the first column, a series of hyperlinked numbers, isn’t a MARC field you explicitly asked for, but it’s very handy to have. It contains the unique identifier for each record (the MARC 001 field) with a link back to the corresponding Folger catalog record. It’s added by default to every export.

The middle columns are self-explanatory, but the last column I asked for, the MARC 008 field, has data that isn’t normally visible to catalog users. It’s a 40-character string that includes standardized forms of the item’s date, country of publication, and primary language. Here’s an example from this data export:

The sections highlighted in green and pale blue here are known as “Date 1” and “Date 2” while the letter highlighted in yellow that precedes them is the “Date Type.” The Date Type tells you how to interpret the dates that follow. In this case it’s coded “s” for “Single known date/probable date” meaning that only “Date 1” has meaningful data. The back-slashes in “Date 2” are just fill characters.

The section highlighted in magenta encodes the country of creation/publication. See the MARC Code List for Countries for all the possibilities. In this example set, every record has “be\” for “Belgium.”

The section highlighted in royal blue encodes the primary language. See the MARC Code List for Languages for all possibilities. Not surprisingly, all records in this set have “eng” for English because of the search limits I set at the beginning.

In order to make the MARC 008 data useful, use Excel’s “Text to Columns” Wizard to split the information into its component parts, discarding the pieces you don’t need.

Begin by highlighting the column and selecting “Text to Columns” in the “Data Tools” section under “Data”.  Pick the “Fixed width” radio button, then hit “Next”.

Then add and move break lines to isolate the sections that correspond with the color-coded sections described above:

Finally, highlight each of the sections you don’t want and select “Do not import column (skip)” to get rid of them, then hit “Finish”.

You’ll end up with separate columns for each of the elements, making it easy to sort and filter your results:

And that’s the gist of it.

Needless to say, we’re happy to help you tailor searches and exports to your needs, assuming that the data exists. Just send a message to reference@folger.edu and someone will get back to you.