The saga of migrating our cataloging data, continued from our last post.
At first, it seemed simple to move our cataloging data from Excel to PastPerfect. PastPerfect allows for the bulk import of records using a number of file formats, including Excel. But when we tried importing records from our spreadsheet, fields that exceeded 254 characters were truncated. Several fields, such as Scope and Content, routinely exceeded this length. If we imported directly from Excel, we’d have to later manually copy and paste every field longer than 254 characters into PastPerfect.
Fortunately, we developed a better solution than copy/paste. This solution is a multi-step migration and data clean-up process using several available programs. If you love data and experience similar challenges, read on!
PastPerfect also accepts .DBF, or “dBase,” as a file format for import. .DBF files make use of a seperate .DBT file to store memo fields (fields that exceed 254 characters). So we tried a number of methods to move our cataloging data into a .DBF format. What we settled on was a cheap and relatively simple method, albeit with a couple of steps.
Once we have a set of records ready for import, we spend some time performing quality control, data normalization, and data transformation using Google Refine. I’ll write about why and how we use Refine in another post. Once we’re done in Refine, we move the records back into an excel format, and then import this data into a Microsoft Access database. Then we export from Access into .DBF. This export creates the dual .DBF/.DBT file structure that we need to import into PastPerfect. Once we have the .DBF and .DBT files prepared, we can import the records into PastPerfect.
While this all sounds a little awkward, it’s turned out to be a consistent and relatively quick process that can be performed on a regular basis once you get the hang of it. It makes creative use of software that we already own. And it allows us to continue our cataloging in Excel, while avoiding any cumbersome manual entry when we move records to PastPerfect. Best of all, no copy and paste.