output stream

Exporting bibliography from library catalog to XML

dtjohnso — Tue, 08 Feb 2011 18:58:58 +0000

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.

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:

How to easily add customized links to an intranet page

dtjohnso — Fri, 11 Dec 2009 21:50:56 +0000

For a long time, I always wished I had an easy way to add a custom set of links to my university’s intranet home page so I could easily access the pages for my current classes.

The idea was to have it look something like the image below. Notice the “Class Links” section above “Home.” The original page only had the “Home” section.

Originally, this seemed to be a bit implausible. Now, thanks to Greasemonkey and Instapaper, it’s actually pretty easy. In fact, the solution I’ve built is general enough that it could be easily adapted to a number of similar situations where you want an easy way to personalize a set of links on a non-interactive page.

I thought that some of my classmates might be interested in this, so this post is to explain how to adapt my code for yourself. This will require some very basic JavaScript skills at one point, but hopefully I’ll be able to make that part easy enough.

Step 1: Create a list of course pages at instapaper.com

To begin with, you’ll need to have an account on instapaper.com. This is where you’ll store links to your class home pages. If you don’t have an account, sign up now.

Once you’re logged in to instapaper, you’ll need to make a folder in instapaper to store your class links. I named mine “Classes.” You can see an example of my folder structure on the right.
Next, you’ll need to add the links you want to the Classes folder. Do this by clicking on the folder and using the link to the “+ Add” form at the top of the page, or with the instapaper bookmarklet.
Once you have the links displaying in your “Classes” folder as you want them to appear on the school home page, go to the RSS link at the bottom of the page. You’ll want to copy the RSS feed’s URL somewhere so you can paste it into your Greasemonkey script in the next step.

Step 2: Install Greasemonkey script

Now that your links are stored in instapaper, you’ll need to install my Greasemonkey script to insert them onto the intranet home page. If you don’t have the Greasemonkey extension installed in your Firefox, go to the Greasemonkey extension page and take care of that.

This step is somewhat more technical, but once done on the computers where you want your links to appear you will never have to do this part again.

To begin with, you’ll need to create a new Greasemonkey script in Firefox. Do this by right-clicking the Greasemonkey icon in the Firefox status bar and selecting “New user script…”
Fill in the box that pops up with the values below.

Name: Insert class links from remote location
Namespace: http://www.duncanandmeg.org/
Includes (one per line):
http://home.bju.edu/

https://home.bju.edu/
Get the current copy of my script from here and paste it in over the new file that pops up on your screen. Save your file.
Find the line in your script that looks like the one below and paste the URL for your instapaper folder’s RSS feed over my URL.
```
var feedURL = "http://www.instapaper.com/folder/48337/rss/11185/QBV0RZfH4KBO7GwgrR3D8b7sv90";
```
Save your script and close it.

Finished!

Now go to the home.bju.edu to test your script. It should insert a list of class links into the page based on the contents of your folder in instapaper.

In the future, you will not need to edit the Greasemonkey script again. Any changes you make to the contents of your instapaper folder will be automatically reflected in the links displayed on the intranet home page.

Birthday surprise

dtjohnso — Tue, 03 Mar 2009 19:00:22 +0000

My wife and I wrote this little program for my little brother, who is a freshman computer science major. It was a lot of fun, and brought back memories of my C++ days in school.

Obviously the programming wasn’t challenging, but getting the parameters right was a lot of fun.

#include <iostream>
#include <windows.h>

#define doo 262 //frequency do 262 Hz
#define re 294 //frequency re 294 Hz
#define mi 330 //frequency mi 330 Hz
#define fa 349 //frequency fa 349 Hz
#define sol 392 //frequency sol 392 Hz
#define la 440 //frequency la 440 Hz
#define si 495 //frequency si 495 Hz
#define od 523 //frequency do 523 Hz

using namespace std;

int main()
{  

  Beep(doo,100);
  Beep(doo,100);
  Beep(re,180);
  Beep(doo,190);
  Beep(fa,200);
  Beep(mi,300);
  Beep(doo,100);
  Beep(doo,100);
  Beep(re,180);
  Beep(doo,190);
  Beep(sol,220);
  Beep(fa,300);
  Beep(doo,100);
  Beep(doo,100);
  Beep(od,260);
  Beep(la,260);
  Beep(fa,220);
  Beep(mi,200);
  Beep(re,300);
  Beep(466,100); //b-flat is 2^(1/12)*440 http://en.wikipedia.org/wiki/Note#Note_frequency_.28hertz.29
  Beep(466,100); //b-flat is 2^(1/12)*440 http://en.wikipedia.org/wiki/Note#Note_frequency_.28hertz.29
  Beep(la,260);
  Beep(fa,300);
  Beep(sol,300);
  Beep(fa,300);

  return 1;
}

Microsoft LifeCam VX-3000: Installation & configuration

dtjohnso — Fri, 26 Dec 2008 23:27:28 +0000

In a recent post, I wrote about our new webcam and mentioned some incompatibilities I discovered. Here I want to document how I installed and configured the software for the camera, mostly so my family can re-install the camera if they ever need to do so. These notes refer to how I did this on a Windows XP system.

Installing the proprietary software

The software included on the CD is pretty easy to install and configure. The disk includes the camera drivers and a copy of the Microsoft LifeCam program which provides basic webcam functions. I used it to test the webcam itself, but nothing else.

The instructions recommend that you install the software on the CD before plugging the camera in. I did this, but I have another theory that I’ll try on my computer at home.¹ Once the LifeCam software and drivers are loaded, the installer prompts you to install the Windows Live Messenger (which includes a component called Windows Live Call). This is the Microsoft software for making video calls. I installed this, but when I installed Skype later I disabled the Live Call functionality (more below).

Configuring Skype

I downloaded and installed Skype using all the default settings. Skype detected the camera automatically, so I really didn’t have to configure anything within Skype for the camera to work.

One-touch button

I found one helpful hack on the Skype user forums that explains how to make the “Live Call” one-touch button on top of the camera work with Skype instead of the default Windows Live Call program. The Skype forums report two ways to do this, one by substituting the Skype executable for the Live Call program, and the other by making edits to the Windows registry.

The only problem with the registry hack method (which has detailed explanation here) is that you may have to edit multiple entries underneath different numbers which refer to which USB port the camera is plugged into. Since there’s a good chance that my family may unplug the camera for some reason and then plug it in somewhere else, I decided to use the substitute executable method. One other commenter mentions a fascinating (and probably better) method using a symlink for Windows Vista, but it won’t work for XP. (If this paragraph doesn’t make sense to you, ignore it!)

Here are the steps I used for the substitute executable method. Essentially, this disables Windows Live Call, so it would be a good idea to remove all shortcuts to Live Call after following this hack:

Open the folder C:\Program Files\Microsoft LifeCam\
Rename IcePick.exe (the “Live Call” executable) to IcePick.exe.bak
Copy skype.exe to this folder and rename it to IcePick.exe

Even when Skype is running in the background, pushing the one-touch button on top of the camera brings it up for making calls.

NOTES:

My theory is that if I plug the camera in without installing the Microsoft software at all, I can probably load the driver by itself into Windows from the CD using the Found New Hardware wizard. If this works, I’ll write another report explaining how.

Microsoft LifeCam VX-3000: Overview

dtjohnso — Fri, 26 Dec 2008 19:29:20 +0000

This Christmas, my mother got the Microsoft LifeCam VX-3000 2 pack so that we can video chat with her when we’re at our home. Although the camera is certainly not a high-end webcam, it is enough for basic video chat needs. I won’t write a comprehensive review here, my purpose is just to clarify a few things that I’ve learned about the camera and then document how I set it up on my mom’s computer so others can properly configure things if anything later goes awry. For a helpful review of the VX-6000 (which appears to be the same camera except for a wide-angle lens and probably a few other perks), see this review from a guy in New Zealand.

The camera itself was fairly easy to install, and the proprietary Microsoft software works pretty well (although it’s a little too resource-intensive for my taste). You don’t have to install Windows Live Messenger to use the camera, but you will need it or Skype in order to make calls with the camera.

Compatibility issues

The camera appears to be incompatible with Windows XP Media Center and Vista 64-bit edition. I am not aware if Microsoft has updated the driver to solve this issue at this point, but this seems to be an issue from what I can tell reading online reviews and user forums.

Compatibility with Skype and Google Video Chat is mixed. The camera works with Skype basically “out of the box,” but at this point it does not work with Google Video Chat at all. Maybe a Google engineer will fix this sometime, but for now the testing window in my Gmail settings shows a black screen and the camera never turns on.

Although the camera works fine with Skype, getting the “Live Call” button on top to work with Skype requires a fairly simple hack. Since my family will make most of their video calls using Skype and not Windows Live Messenger, I’ll explain that in a future post I’ve explained that in another post.

Burning CDs and DVDs in Windows XP… without unusual punishment

dtjohnso — Sat, 26 Jul 2008 19:10:40 +0000

Someday, burning optical media like CDs and DVDs may be a fully supported operating system feature. Until then, Windows users have to use burning software packages that unfortunately tend to bundle complexity and cost with a lot of frustration. Windows XP does support minimal burn operations when blank CDs and DVDs are opened as “writable” windows in My Computer, but this is not enough for creating anything beyond basic data disks.

I have used commercial burn software packages (like Nero and Roxio) before, but I chose not to use these packages again for a few recent projects. Here’s why:

For a home user who rarely (read, almost never) needs to burn media anyway, it is difficult to justify the cost of either package.
Both Nero and Roxio bundle a lot of useless junkware into their packages (perhaps partially to justify their pricetag) and all I need is basic burn functionality.
Removing remnants of an expired “trial” version of either is not a task I relish. I’ve found (and Wikipedia concurs¹) that even licensed copies of these programs are hard to remove.

With this in mind, I set out to find open source or freeware burn software without needless frills. I was looking for actively developed projects which might continue to be useful in the future. Two programs which do just that are InfraRecorder and ImgBurn.

InfraRecorder is an open-source project, and it supports creating various data and audio CDs and DVDs (at this writing, it is in version 0.45). It does not yet support the creation of video DVDs, but it has a fairly simple interface, and enough basic documentation to help most users. I used it to burn a a large collection of MP3 files to a DVD without any problems.

ImgBurn is freeware, and supports data, audio, and video for CDs, DVDs, HD-DVDs, and Blu-ray (at this writing, it is in version 2.4.2.0). The interface is very clean and operation is straightforward. I probably like this program better than InfraRecorder because its basic approach appeals to me.

Rather than modeling all its operations like other burning software which combine high-level tasks like “Copy disk” or “Burn data disk” with lower-level processes such as creating a disk image, ImgBurn does everything from a low-level perspective. If you want to copy a disk, you first run the process to create an image file of the original disk, and then you insert a blank disk and burn the image file into the new disk. Although this approach may seem more technical to a new user than a higher-level approach, this approach is so simple (and the tutorials so straightforward) that when I first used it I feared that I was missing some detail simply because it seemed too easy to be true.

Amazingly, with all this simplicity, it really does support a high degree of customization for advanced users. The complexity is accessible, but you don’t have to deal with it unless you seek it out.

I’m glad I finally put some effort into finding burn software for future projects. Both of these are good programs that don’t annoy me in any of the ways the commercial packages did, and hopefully they’ll still be useful as technology and media standards progress. At least for now, these two will probably remain my burn packages of choice.

NOTES:

Wikipedia’s article on Roxio’s “Easy Media Creator” states “Once installed, Easy Media Creator is difficult to uninstall. See the Roxio Community Forum, containing over 5000 posts concerning this topic. Roxio’s official six-step uninstall process, which includes registry edits, is available at ….”

Clean pastes with Excel VBA

dtjohnso — Fri, 14 Dec 2007 16:50:31 +0000

I’ve talked about some of the Excel VBA I’ve done before, and here’s a new thing I learned today.

Problem

One part of my application selects a range from one worksheet in the spreadsheet and pastes it onto another. Originally, the code I used looked like this:

destinationWorksheet.Range("A2").Select
ActiveSheet.Paste

There is one problem I was unaware of. Every time the .Paste operation executed, it added a new "Named Range" to the ActiveSheet. I only realized this when snooping around in the Properties for the Excel file, where I found (to much dismay) 840 different "Named Ranges" referring to data long since thrown away! An older version of my application, which used this unspecified Paste operation much more frequently, had even more of these attached to numbers of other worksheets.

Solution

The first thing I looked into was the properties of the .Paste method itself. Turns out there are two of them, "Destination" and "Link," and they are more or less mutually exclusive. The Excel VBA Help provides good explanation of that which I won't reproduce here. The reason my old call for an unparameterized .Paste worked was that it simply used the current selection as the "Destination" property. This is explained from this portion of the Help document:

Destination Optional Variant. ... If this argument is omitted, the current selection is used.

I had assumed that this parameter was irrelevant. The increase in these weird "Named Ranges" made me suspicious. I modified my .Paste call this way to see if it would make a difference:

ActiveSheet.Paste Destination:=ActiveCell

Adding a parameter that specifies the default parameter anyway isn't a pretty fix, would it work? After executing a few calls to the .Paste statement in question, I was shocked! It did make a difference! No more "Named Ranges" were being appended to the ActiveSheet.

Cleanup

Wonderful, now I had 840 "Named Ranges" stuck in my file for no reason, referring to data long since wiped off the sheet. Some quick Help file searching taught me some things.

Each Worksheet object in a Workbook contains a collection of Name objects called Names. The Name objects in this collection are the "Named Ranges" in question. With some quick 'n dirty coding, I wiped them all out. Solution is below:

Private Sub NameDR4ng3z()
    ThisWorkbook.Worksheets("destinationWorksheetName").Activate

    For Each myName In ActiveSheet.Names 'list '
        Debug.Print myName.Name
    Next myName

    For Each myName In ActiveSheet.Names 'delete '
        myName.Delete
    Next myName

    Debug.Print "Names deleted? We'll see"
    For Each myName In ActiveSheet.Names 'double-check '
        Debug.Print myName.Name
    Next myName
End Sub

This worked. I'm happy.

Lazy list management

dtjohnso — Mon, 12 Nov 2007 21:28:09 +0000

I use a couple awk scripts to manage a list of students who come to church with me every week. When a student tells me he will be coming on Sunday, I put an asterisk (*) in front of his name in a static text file with everyone’s names. If they tell me they won’t be coming, I put a minus sign (-) in front of their name so I remember that they said so. If for some reason I’m not sure, I put a question mark (?).

My main script spits out a list of all the students who are coming (i.e., have an asterisk). There’s no genius to it, I’m just logging it here for reference.

I also have another script that wipes all the attendance marks out of the main list file.

Files

All files are available here:

List management files

Source is posted below without comment since it’s basically self-explanatory.

StudentList.bash

awk 'substr($1,0,1) == "*" { print substr($0,2,length($0)) } ' students.txt > studentsComing.txt
cat studentsComing.txt

WipeStudentList.bash

awk -f Wipe.awk temp
cat temp >students.txt
rm temp
./studentlist.bash

Wipe.awk

{if ( substr($1,0,1) == "*" || substr($1,0,1) == "-" || substr($1,0,1) == "?" )
  { print substr($0,2,length($0))  }
 else
  { print $0 }
}

Producing valid RSS from my XSL Transform

dtjohnso — Thu, 11 Oct 2007 21:15:37 +0000

In a previous post, I showed the XSL transform I designed to transform a simple XML file I use into “(almost) valid RSS.” I called it (almost) valid because of one problem, which I address here.

The problem reviewed

My original XSL assigned an arbitrary ID to each entry in the news file based on its order in the list. What this meant was that the first item received the integer 1 for an ID, the second 2, the third 3, etc. Here it is:

<!--old method, produces invalid link & guid-->
<link><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
  <xsl:value-of select="position()" /></link>
<guid><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
  <xsl:value-of select="position()" /></guid>

This was fine for my original purposes, but since this hardly represented a unique & permanent identifier for each post, it caused some problems when echoed into the RSS and elements. The significance of the element in particular is eliminated with such an approach. Every time I added an item to the list (see the previous post for an explanation of my schema), it would be assigned the ID of 1, and the older posts would be assigned the ID numbers 2, 3, 4, 5 etc.

This meant that most RSS aggregators would not detect that a new post had appeared and would not update accordingly.

Choosing a unique ID schema

Since I could no longer use my simple numbering scheme based on position in the original XML file, I had to come up with some other identifier. I learned about the Tag URI algorithm, but decided that it was more complex than I really needed for this application, and the scheme of my original XML file didn’t lend itself to producing these anyway.

Since I don’t update the entries in this file frequently, I invented a simple method that produces relatively unique identifiers based on each entry’s data. All my new ID’s consist of two elements based on the data in my XML file:

The text from the entry’s title preceding the first space (” “) character
(If the entry consists of only one word, this element will not appear. This is a weakness that would be more important if updates were frequent)
The date of the entry encoded in the format mm-dd-yy

Once I settled on my scheme, producing it in XSL turned out to be fairly simple.

XSL transform

I’m including only the portion of the XSL tranform that deals with the entry and here. The rest of it is in the previous post.

<item>
  <title><xsl:value-of select="headline" /></title>

  <!--unique id created and stored in XSL variable-->
  <xsl:variable name="bookmark">

    <!--find word before first space-->
    <xsl:value-of select="substring-before (headline, ' ')" />
    <!--produce date in format mm-dd-yy-->
    <xsl:value-of select="translate (date, '/', '-')" />

  </xsl:variable>

  <!--variable placed in link and guid elements-->
  <link><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
    <xsl:value-of select="$bookmark" /></link>
  <guid><xsl:text>http://www.duncanandmeg.org/news.php#</xsl:text>
    <xsl:value-of select="$bookmark" /></guid>

  <!-- continue with transform, see original post -->

</item>

With a reasonably unique guid, most RSS readers properly update now when I post new items.

Fixing memory leaks in Excel VBA… (we hope)

dtjohnso — Fri, 14 Sep 2007 02:05:52 +0000

One project I constantly return to is an application I built in Excel VBA that does a lot of different things. One specific problem remains unsolved with this application, so I’ve decided to throw it out here and see if any gurus have an answer.

Basically, as a result of some Excel memory leaks beyond my control (I can’t install recommended hotfixes on the computers where this application is used, and need to be able to handle the issue with/without hotfixes anyway), when I shut down Excel it typically remains stuck in memory (and visible under “Processes” in “Task Manager”) even though it looks like it is gone to the user.

Paranoia

I realize that many would argue that since this bug is Microsoft’s problem and not mine, that I shouldn’t trouble with addressing it. However, since this application I’ve built is something that gets used frequently (but not constantly) throughout the day, I can imagine this becoming a problem (or at least wasting resources) at some point.

Another rejoinder to this charge is quite simple: I’ve actually ignored this issue for about a year now already, and am only revisiting it because I’ve learned some things about VBA that enable me to possibly defeat it. If I didn’t have any hope of fixing this, I wouldn’t try.

Problem (in more depth)

Here is what I know about why one of my memory leak problems exists (this article won’t deal with all my memory leak issues in the application, since I still haven’t made any headway on the others). I may not have quite the right diagnosis here, but tests to this point suggest I might be on the right track.

The main worksheet in my application includes numerous embedded control buttons that marshall up various cool features I’ve built for this VBA app. Unfortunately, it appears that embedding any sort of control (as far as I can tell) on a worksheet can cause Excel to commit memory leaks.

Before going further, a few notes about the KB article I just mentioned:

This article refers to an issue with Excel 97.
I’m not sure that worksheet buttons are considered ActiveX or OCX controls

The problem here is simple: I did some research on this a year ago when I first discovered the issue, and didn’t keep track of the KB article I found that suggested the problem with embedded buttons. I remember reading something like that then, but can’t find it now. The only reason I think this might actually be the source of the problem is because my hacked solution seems to prevent the memory leak in question.

Ugly solution

I learned that it is possible in Excel VBA to write an Auto_Close sub to override default behavior when closing a spreadsheet. ~~(If I ever track down the documentation that tipped me off to this, I’ll link to it here.)~~

This suggested that I might be able to do something on the file’s close event that might at least catch and destroy the source of the memory leak before the Excel app slipped out of my control until an inevitable Ctrl+Alt+Del. Here’s the ugly, scary code:

Sub Auto_Close()
    'Deletes the spreadsheet with the embedded buttons '
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Sheet1").Delete

    'Prevents prompt to save changes, so that workbook closes without saving '
    'This prevents the sheet from being really deleted '
    ThisWorkbook.Saved = True
End Sub

Concerns

After this function fires, Excel closes as normal, without saving the file. Since this fires whenever the user closes the file, it seems safe enough to do this, but you can’t help being a little bit frightened with an approach like this.

Tests and reasoning

I think this works. Before, when I would exit Excel, an instance of EXCEL.EXE would remain in my “Process” list in Task Manager until I manually terminated it, logged out, or rebooted. Now when I exit Excel, it disappears from the list as normal (unless I’ve used some other parts of the application which induce the other memory leak problems I’m not discussing here). It appears that the embedded button memory leak problem is bypassed (I really hesitate to say “solved”).

Suggestions?

If you have handled this problem before, have more information, or know how a better (and saner) way to fix this, please post a comment!