As part of a LibGuide project that I was working on recently, I wanted to export the bibliographic data for rare Bibles in our Mack Library collection so that I could generate a list for public viewing. The rare Bibles in our collection are housed in two rooms, the Jerusalem Chamber and Special Collections, and because few people ever see the books in Special Collections I thought it would be nice to create a browsable list. Our current library catalog does not make it possible to browse the items in a particular room (or “location code” in library science terms).
The process I came up with involves three major components:
- Finding and exporting the bibliographic records in Millennium using Create Lists
- Filtering, finalizing, and structuring the data in Excel 2007
- Converting the structured data into clean HTML with XSLT
I’ve posted my files in a github repo for others to adapt.
Step 1: Export records from Millennium
To create my list, I did an item record search based on location code, but stored the bib records in my list.
I then exported all the data to text. Because some of the variable-length fields included commas, I used tabs (“Control character” 9) as my field delimiter.
Step 2: Manipulation in Excel 2007
For my situation, data processing in Excel involved a number of small steps. Steps 2.2 and 2.3 may not be necessary for every use case.
2.1 Import data
A number of the titles in our collection are not in English, and so I had to specify that the imported data was Unicode (UTF-8). I found out that Excel 2003 does not support importing UTF-8, but Excel 2007 does.
On step 3 of the import wizard, I set the data format for all columns as “Text” rather than “General” to avoid any unwanted conversion of my bibliographic data.
2.2 Filter records (if necessary)
Because our Special Collections room includes many items besides the Bibles that I was interested in, I needed to filter my data. I did a quick filter for all items with Uniform Titles beginning with “Bible.” and used Select Visible Cells to copy and paste the data I wanted to a new worksheet to prepare for export.
2.3 Produce truncated bibID
The current version of the Innovative Web catalog at my institution does not respond correctly to a full 9 character bibID. Instead, I have to truncate the checkdigit at the end to get a valid link. Now, since that may not make much sense, let me just illustrate:
- http://library.bju.edu/record=b1184983 VALID
- http://library.bju.edu/record=b11849836 – INVALID (at least, for now)
The first example does not include the checkdigit “6″ at the end. Since the checkdigit is included in the data exported by Millennium, I created a new column at the end of my data and populated it with this Excel formula:
=LEFT(A2,8)where “A2″ is the reference to the cell containing the untruncated bibID.
2.4 Match columns to XML Schema
In order to prepare my data for export, I need to match each column to an attribute in a custom XML Schema so I can export structured data that I can meaningfully interpret into HTML.
To do this, I’ll need to create my schema file, load into Excel, and match each element to the appropriate data columns.
My XSD is as follows, and can be easily adapted to another situation.
<?xml version="1.0" encoding="utf-8"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="Bibliography">
<complexType>
<sequence>
<element name="Item" maxOccurs="unbounded">
<complexType>
<sequence>
<element name="ItemTitle" type="string" />
<element name="ItemUniformTitle" type="string" />
<element name="ItemAuthor" type="string" />
<element name="ItemCallNum" type="string" />
<element name="ItemEdition" type="string" />
<element name="ItemPublication" type="string" />
<element name="ItemDescription" type="string" />
<element name="ItemSeries" type="string" />
<element name="ItemNote" type="string" />
<element name="ItemSubject" type="string" />
<element name="ItemAddAuthor" type="string" />
<element name="ItemAddTitle" type="string" />
<element name="ItemISBN" type="string" />
<element name="ItemTOC" type="string" />
<element name="ItemBibID" type="string" />
<element name="ItemBibIDTruncated" type="string" />
</sequence>
</complexType>
</element>
</sequence>
</complexType>
</element>
</schema>Once I have a valid schema, I load it into Excel through XML Source window on the Developer ribbon. To add a new Schema, click the “XML Maps” button and then “Add.” Then I drag each attribute in the XML tree visible in the XML Source pane and drop it on the appropriate column in my data until the result looks like this:
2.5 Export to XML
To generate XML from my formatted file, I click the “Export” button in the XML section of the Developer ribbon and save to file. Excel will follow my custom schema to produce structured data that I can use to generate HTML output.
Step 3: Generate HTML with XSLT
Now that my data is in XML, generating HTML is a simple matter of applying an XSL stylesheet. My stylesheet is as follows:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="no" encoding="UTF-8"/>
<xsl:template match="/Bibliography">
<xsl:apply-templates select="Item">
<xsl:sort data-type="text" select="ItemUniformTitle" order="ascending" />
</xsl:apply-templates>
</xsl:template>
<xsl:template match="Item">
<p>
<xsl:variable name="bibID" select="ItemBibIDTruncated"/>
<xsl:choose>
<xsl:when test="ItemUniformTitle != ''">
<xsl:value-of select="ItemUniformTitle" />
<xsl:text> </xsl:text>
<xsl:value-of select="ItemTitle"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="ItemTitle"/>
</xsl:otherwise>
</xsl:choose>
<xsl:text> </xsl:text>
<xsl:value-of select="ItemPublication" />
<xsl:text> [</xsl:text>
<xsl:value-of select="ItemCallNum" />
]<xsl:text/>
<xsl:if test="ItemNote != ''">
<br/>
<xsl:value-of select="ItemNote"/>
</xsl:if>
</p>
</xsl:template>
</xsl:stylesheet>
Recent Comments