Theses and Dissertation Remediation Project
These procedures outline the process for inventorying, barcoding, and batch cataloging approximately 22,000 Theses, Dissertations, and Plan Bs in the USU University Archives. It includes instructions for doing a shelf inventory, assigning barcodes and call numbers, verifying data, assigning one of three specific batch workflows (Merge (no overlay), Merge (with Overlay), and Original cataloging) and provides step by step instructions for each of the batch workflows.
Tools Needed
Sierra (local catalog)
Airtable
OCLC Connexion
MarcEdit
Excel
Barcodes and Barcode Scanner
Label Printer
RFID Tags
Quick Navigation
- 1 Tools Needed
- 2 Quick Navigation
- 2.1 Data Collection Process
- 2.2 Procedure 1: Shelf Inventory
- 2.2.1 Collection Sign Up and Tracking
- 2.2.1.1 Sign Up For A Collection
- 2.2.1.2 Completed Collections
- 2.2.1.3 Daily Tracking
- 2.2.2 Procedure 1 Process
- 2.2.2.1 Procedure 1: Known Oddities
- 2.2.2.1.1 Plan B Bound-withs
- 2.2.2.1.2 More than one copy or volume of item
- 2.2.2.1.3 Cover has a different title than the title page
- 2.2.2.1.4 More than one entry in Airtable
- 2.2.2.1.5 Thesis is from a different University
- 2.2.2.1.6 Thesis has more than one author
- 2.2.2.1 Procedure 1: Known Oddities
- 2.2.3 Statistics for Procedure 1
- 2.2.1 Collection Sign Up and Tracking
- 2.3 Procedure 1a: Quality Control for Shelf Inventory
- 2.4 Procedure 1b: Assigning Call Numbers
- 2.5 Procedure 1c: Print Labels
- 2.5.1 Print Labels Using PDF
- 2.5.2 Batch Printing Labels
- 2.5.3 Statistics for Procedure 1c
- 2.6 Procedure 1d: Attach RFID tags
- 2.7 Procedure 1e: Review Numbering and Attach Labels (use only when directed)
- 2.8 Procedure 2: Verifying Sierra and OCLC Records
- 2.9 Procedure 3 - Final Quality Control
- 2.10 Procedure 4 - Batch Record Creation and Importing
- 2.10.1 Thesis Batch Merge Processes (with and without overlay)
- 2.10.2 I. DETAILED PROCEDURE FOR MERGE (with no overlay) workflow
- 2.10.3 II. Detailed Procedures for OVERLAY (with Merge) workflow
- 2.10.4 III.Thesis Batch ORIGINAL Processes
- 2.10.4.1 General Workflow:
- 2.10.4.2 MarcEdit additions to include for Original Record Process
- 2.10.4.3 Detailed Procedures for Original workflow:
- 2.10.4.4 Statistics for Procedure 4
Data Collection Process
Export all USU theses records (in any format) from Sierra into an excel sheet [expand on this]
Sort the records into years
Clean and reorder Excel sheet by:
Copy and pasting all records for a year (or decade, if there are sparse records for the year) into a new tab on the spreadsheet, including column labels
Change column order to match Airtable “Next Batch Add” view
Delete the 100 field column
Combine the Birth and Death years into one column, add in the correct punctuation, delete individual columns for years
Isolate the OCLC number
Isolate the titles
Copy the 245 to the very end of the spreadsheet
Split off 245 c by using the text to column, with a delimiter of “/”. Review to make sure it split at the appropriate place. Correct any issues (|c not moved over or “/” was part of the title)
Remove the ending period using the following formula: =IF(RIGHT(A2,1)=".",LEFT(A2,LEN(A2)-1),A2)
Trim the leading/ending spaces with =TRIM([insert cell #])
Scan and review for errors (leave strange text, it will be reviewed and corrected during Procedure 1)
Copy the column and paste back into the original 245 space using paste->value
Split the 502 into Theses Type, Department, Degree Type, and University Name
Theses Type
Use Text to Columns with a delimiter of “(“ to isolate the theses type
Scan and review for errors/issues and consistency with the controlled vocabulary in Airtable
Where found, correct if obvious, or select a default of “Thesis” (this will be reviewed in Procedure 1)
Copy and paste into “Theses Type” column after the Year
Degree Type
Highlight the column with the remaining text (from the previous step)
Use Text to Column with a delimiter of “)” to split out the degree
Review the degrees and correct typos or punctuation errors. Match against the controlled vocabulary in Airtable
Copy and paste into a new “Degree” column after the “Theses Type” column
Department (Note, if the 690 field is consistently applied for the batch, it is a better option for isolating the Department name than the 502.)
Highlight the column with the remaining text (from the previous step)
Delete the double dashes, University name and the phrase “Dept. of “ from the text strings using find and replace the text string
Use Text to Column with a delimiter of “,” to split out the year from the department
Review the department names for spelling errors or extraneous text
Copy and paste into a new “Department column after the the “Degree” column
Add a column after “Department” and input the university name at the time the theses was issued. (See University Name changes for dates.)
Delete all the unneeded work columns at the end of the spreadsheet
Adjust the 300 field
Replace "?eaves" or “ eaves” to "leaves"
Copy field to end of spreadsheet
Use text to columns with a delimiter of “;” to split off the |c. Verify that only the height information was split off, then delete this column (it is automatically applied in Airtable)
Use text to columns with a delimiter of “:” to split off the |b wherever present
Copy the controlled vocabulary for |b from Airtable and copy and paste to each occurrence OR in the next column, use the formula =IF(A2<>"",":$billustrations") and remove all the “FALSE” returns with a find and replace
Split off any extra informaiton (such as maps, CDs) into the Extra Objects column
Copy each into separate column that matches Airtable in order
Ensure each subfield matches the spelling, punctuation, and spaces for the controlled vocabulary in Airtable (if relevant)
Add columns for 500-2, 533, 590-1, 590-2, 690-2, 690-3, and 690-4 immediately after the Illustrations column, copy over relevant info. Note that the following information is NOT copied over into the new fields:
“Library’s copy on microfilm”
USU ETD
Department statements
Note that the columns from 500 on may be out of order if there is an extra note or field. Look at later columns to identify if that information should be moved to the left.
Dedupe records (this can also take place before step 3, if desired).
Sort and resort by different criteria (title, author, etc.) and scan for duplications
Use the conditional formatting to highlight all duplicates in the combined author name
Use the conditional formatting to highlight all the "z" in the material type
If duplicates are found, Preference for a or t records, then b, then z
Once finished, copy into Airtable
Routinely or after each data load into Airtable, run a dedupe process and scan for potential duplicates
Once finished, go to the Sign-Up tab and change the following for the year(s) that were just loaded:
Pre-processing (Barcoding) Status: from “Hold off” to “Unclaimed”
Last Completed Step: from “In Queue” to “Title List Loaded”
Procedure 1: Shelf Inventory
Collection Sign Up and Tracking
Please note that tracking will need to happen in two places because the Theses Project is part of the larger “Barcoding” project and needs to be reported in both the Barcoding Tracking, Issues, and Stats AND the Thesis Data Pull bases. Please fill out both of these bases, as noted below.
Sign Up For A Collection
Go to the Airtable base “Barcoding Tracking, Issues, and Stats”
Click on the “Sign Up” tab
Collections are organized by year. Reserve a collection by
Selecting a name in the “Name” column
Changing the Current Status to “In Progress”
Enter a date in the “Date Assigned” column
Go to the Airtable base “Thesis Data Pull”
Click on the Sign up tab.
Collections are organized by year. Sign up for a collection by
Selecting your name in the Pre-processing assigned to field
Fill in the Pre-processing started
Completed Collections
When you are completely finished with a collection year, fill out the following:
Thesis Data Pull
fill out the Pre-Processing End field on the Sign Up tab
Change the Pre-Processing Status to “Completed”
Barcoding Tracking, Issues, and Stats
Fill out the “Date Finished” field in the Sign Up Tab
Change the status to “Completed”
Sign up for another collection year.
Daily Tracking
Pull a cart’s worth of items from the year
Go to the Airtable base “Barcoding Tracking, Issues, and Stats” - > “All Tracking”
Add a new line with:
Name
Area
Sign Up ID
First and Last call numbers on the cart
As call numbers are not formally assigned yet – please use the format YYYY-LastName
Date Started
Process items following the steps in “Procedure 1: Shelf Inventory” section
Return to the Airtable base “Barcoding Tracking, Issues, and Stats” - > “All Tracking”
Record the following information:
Number of items reviewed
Number of items barcoded
Date Finished
Statistics are used for tracking the progress of the project and also for reporting to the library administration on current and final numbers. Airtable automatically calculates current projects based on your statistics. It is VERY important to record them as listed above. In general, monthly statistics will be pulled from the Barcoding Tracking, Issues, and Stats Airtable base.
Procedure 1 Process
Open the Thesis Data Pull -> Title List tab -> Master Sorted List view
Note: Modifying existing views may cause problems for others who are using the base. If you would like to filter so that you only see the year you are working on, please go to the YYYY-Template view, then select the “duplicate view” from the three dots next to “Share view”. Rename with the year appropriate (or whatever unique name is preferred).
Compare each item on the cart to the list of titles in Airtable
For each item where the theses is found in the collection and also listed in Airtable
Change the status to “Found”
Add the Barcode to the item and record in the “Barcode” column
Verify/update/add the following information using the item in hand. It is very important to match all fields to EXACTLY what is written on the title page and not the front cover of the thesis. There is no need to research or add information that is not available from the item in hand.
Author name and information (split into Last, First, Expanded, Suffix, and Birth/Death fields in Airtable. PLEASE NOTE: Only verify that the information is correct as far as is evident from the title page and/or the author bio/vita, if available. If not readily evident, leave information as is).
For the author Mirla F. W. Morgan, the name would be recorded as:
Author Last
Ex. Morgan
Author First
This is generally first and middle name and/or initials as represented in the object
Ex. Mirla F. W.
Expanded Form of Name
Use only if there was an initial used in the first or middle name and the full version is available. In these cases record the total first and middle names inside of a parentheses. Add a space before the first parathesis.
Ex. (Mirla Francis Whiting)
Suffix
Birth/Death Date
This will not be commonly available- but might appear in the vita
Record as “comma space birth year dash death year”: , 1929-2012
NOTE: the automations in Airtable will combine this information to look like: Morgan, Mirla F.W. (Mirla Francis Whiting), $d 1929-2012 $e author
Title
Years
Theses Type
Department
If the inventory lists a department but the theses lists no department, cut/paste the department into the “notes” field along with an explanation.
Degree Type
University Name
Pagination
Almost all pagination will be leaves. Leaves indicate that the print is only on one-side of the page, whereas pages would mean that both sides of the page are printed on.
Record the pagination as noted in the book. Preliminary pages may be indicated as Roman numeral (v, vii, etc.) Main text of item is likely recorded in traditional Arabic numerals (125 or 67, etc.) An example of an item with 7 pages of preliminary text that are numbered as Roman numerals and 82 numbered pages in Arabic numerals would look like this:
vii, 82 leaves
Unnumbered paged can be recorded in brackets: [8] leaves
Illustrations
This refers to any illustration at all. Simply select it from the drop down menu ( it is the only option). Leave blank if there are not illustrations.
Bibliographic Reference Page
Check that there is a bibliographic reference page (which is likely the case). If so, checkmark the “Bibliographic Reference” field.
For an item that is in the collection, but not listed in Airtable
Use the Search feature in Airtable to see if the item has been listed under a different year. NOTE: You will need to go to the Master Sorted List to conduct this search across all records. Search for both the title and the author names to be sure it is not listed in another year.
If found, verify the information according to step 3, correcting the year (or any other incorrect information)
If not found:
Add a new line to the Airtable base and fill out the fields listed in step 3
Exception: for Status, select “Added”
For an item that is in Airtable, but is not found in the collection
Verify that the item is not within the year range in process according to the process in step 4.a (i.e. if it was misfiled later in the year and hasn’t been pulled onto the cart.)
If found, interfile in the appropriate spot and process according to step 3
If not found in collection, select a status of “Not Found”
At the end of every cart, ensure that the order of the material on the cart matches the order of the “Found” titles in Airtable (excluding any items not found). Using filtering in Airtable to limit the view to the year and “Found” status in order to see this list.
Re-shelve and record statistics in “Barcoding Tracking, Issues, and Stats” Airtable base for each cart:
Reviewed (refers to the number of physical volumes)
Barcoded (refers to the number of barcodes placed)
Procedure 1: Known Oddities
This list will grow with time. Please add to it as needed, to help collect the issues and actions into one space. For all oddities not listed here, please check the “Flag for Becky” column in Airtable and leave any pertinent information in the Notes fields.
Plan B Bound-withs
Explanation: Many of the Plan B’s will likely have more than one title bound into the item. Key indicators include title pages or table of contents that list a “Report No. 1” (or 2, 3 etc.) and when the page is open to the second or subsequent reports, they show another title page. Another key indicator is drastic changes in page numbers. In later decades, Plan B reports are bound in blue. This is not true for earlier material, however.
Action:
Select the status, select “Boundwith”
This list will be compiled and cataloging entries created by hand
More than one copy or volume of item
Explanation: Sometimes theses were published with more than one volume and/or the library may have collected additional copies of theses/dissertation. They will be housed next to one another on the shelf, but will not show up more than once in the Airtable list
Action:
Add the barcode and copy/volume number to the appropriate field (1st Multiple) Barcode, (1st Multiple) Copy/Volume or (2nd Multiple) Barcode, (2nd Multiple) Copy/Volume, etc.) Note: Each additional item will require entry in two fields: Barcode and Copy/Volume. 2nd Note: Copy/Volume is set with a drop down for copies 2-5, volumes 2-5, and second copies of volumes 1-5.
FOR MULTI-VOLUMES ONLY, please check the “This is the first volume in a multi-volume set”. This will be used to generate the first volume number in the call number field
Also change the pagination to “X volumes” where X = the number of volumes. There is no need to record the exact leaves or pagination in this case.
Cover has a different title than the title page
Explanation: Sometimes there are discrepancies between the title page and the cover or spine. These are typically due to typos. Regardless of which version is the correct grammar or spelling, the title page is always the main source of information.
Action:
Verify or record the title from the title page in the “Title” field in Airtable
Record the title from the alternate source (almost always the cover) in the “Alternate Title” field
Select the source for the alternate title in the “Alternate Title Came from” field
More than one entry in Airtable
Explanation: Titles may appear more than once in Airtable because the title list was pulled from all the records currently in Sierra (for physical or microform). Prior to upload, the titles were de-duped. But sometimes, the information did not match up one-for-one, so they will appear twice in the Airtable list.
Action:
Preferred records have a material type of “a” or “t” along with a bib record number and an OCLC number. If not present, look for the next best record along these priorities
Material of “a” or “t”. If none, then look for:
Material type of “b” (preference for records with bib number and OCLC number). If none, then look for:
Material type of “z”. If more than one, select the best of the records in Airtable (more accurate information or simply more information).
Select “Found” on the preferred record and proceed with verifying the item in hand as usual
Select “Duplicate” on the non-preferred record. Please flag both items by checking the “Duplicate – Needs Review” field.
Thesis is from a different University
Explanation: Theses submitted to the archives from other universities may have been interfiled with USU theses.
Action:
Verify that the thesis was not produced at USU (under the current or previous names of the institution) by examining the title page and if needed, additional pages such as the abstract or introduction.
Mark the Status field in Airtable as “Other University”
Note: Selecting this status will automatically create a new entry on the “Non-USU Theses” tab
Put the thesis in the “Other University” shelf next to the barcoding table near the back stairwell in the stacks
If the thesis has already been assigned a number, leave a comment on the record (tagging the Student Supervisor) so that the number can be reassigned
Thesis has more than one author
Explanation: On rare occasions, theses will list more than one author. Sometimes when this happens, there are two theses with the same title and content, but both authors are listed and are in opposite order on each thesis.
Action:
Set the status to “Found”
Check the “Flag for Becky” column
Leave a note in the Notes column that explains why the item is flagged for Becky
When the year is being numbered, be sure to include this item in the numbering, using the first author last name as the filing order.
Statistics for Procedure 1
Please refer to the Collection Sign Up and Tracking portion of these instructions for the full information on how to sign out carts and also track the work done. The final statistics that will be pulled for reporting purposes (not tracking) are as follows:
In Airtable base “Barcoding Tracking, Issues, and Stats” - > “All Tracking”
Number of items reviewed
Number of items barcoded
Procedure 1a: Quality Control for Shelf Inventory
Sign up for a year in the “Student QC” column in the “Sign Up” tab in the Theses Data Pull Airtable base
Where possible, arrange to work with another student tech on a year
Check order of authors on the shelf
Grouped by year, then alphabetized by Last Name, First Name.
Check spelling and punctuation in “Title” field against the physical book
Remove any strange characters and type out with your keyboard.
For example, if your thesis has a title with a “β”, then the transfer process probably put funky symbols in the title field. Delete the unnecessary symbols, and write out “beta”.
Make sure the only things that are capitalized are 1) The first letter of the title, 2) Any proper names, and 3) The first letter of any Latin names (ex: Clostridium botulinum).
Compare the year in the thesis to the year on Airtable
Check Theses Type
Double check Department
We’re going with whatever is in the book – if it says they received a M.S. in Education, use Education as the Department. If they specifically state the Department, use that. If you can’t find the Department, make sure the one listed in Airtable sounds reasonable for the content of the thesis, and then leave as-is.
Check Degree Type
Check University Name
Under “Pagination”, make sure it says “leaves” at the end. Leaves are one-sided, pages are front and back. Most, if not all, of the theses should be leaves.
Double check the multiples are recorded and that the section that says “(1st Multiple) Copies” has a selection underneath it (usually c.2), as well as a barcode in the “(1st Multiple) BARCODE” field.
Check Status
If there are two titles bound in the same book (most commonly found with Plan B Reports) then your Status should be marked “Boundwith” – this is going to be in the main thing you’re looking for. Some indicators that you might have Boundwith, besides the obvious point of having two titles, is pagination that starts over in the middle of the book.
If you have to add a thesis to your year because it’s found its way back to the shelf after the barcoding process:
Search Master Sorted List to avoid duplicate records.
If found, change the status to “Found” and verify the information according to the instructions above
If not found, add according to the instructions above
Once added, comment on the year under the “Sign Up” tab and tag either @Seth Westenburg or @Bryn Larsen. This is an important step for all material prior to 1975 because the entire year will need to be re-numbered.
When the year is finished, return to the Sign Up Tab and check the “Student QC Complete” field for the year
Statistics for Procedure 1a
Because statistics for the initial barcoding have already been recorded in the “Barcoding Tracking, Issues, and Stats” Airtable base. Only record statistics in the main “Statistics” Airtable base.
For each item reviewed, count the total in the “Batch Record Prep” column
If during the QC process, missing information is applied, count each record once in the “Batch Record Prep” except the following
Newly barcoded items = Record in the “Barcoding Tracking, Issues, and Stats” Airtable base in the “Barcoded” column
Procedure 1b: Assigning Call Numbers
Refer to the “Shelf List/Call Number Assignment” view in the “Title List” in Airtable
Limit view to one year at a time
Print off the list (go to the ellipses …. And select the “print view”) or pull up on mobile device
Bring list to Theses shelves
Compare Airtable title order to shelf order and make sure they align
They should be sorted in this order:
Year
Alphabetically by Author Last Name
Alphabetically by Author First Name
Alphabetically by Title
Starting with the first item, assign a number to each item starting with 1. Only assign an integer and no other characters, for example: 1, 2, 3, 4, etc.
With the exception of copies and multi-volumes
Record the number in Airtable (or on sheet and then transfer to Airtable).
Verify all multi-copies are listed correctly
Procedure 1c: Print Labels
This process can be done immediately after assigning call numbers or it can be done at the end of Procedure 4’s batch processing routines. Printing labels can be done individually using Sierra (after the batch processing), individually using a PDF, or in batches. Below is listed the methods for printing individually via a PDF and in batches using mail merge.
Print Labels Using PDF
Print labels for each year. You can create them by hand by inserting the appropriate year and number into this label template: https://usu.box.com/s/wn0dhynskjnjb1rvu3fkgib6auf91tph and then saving as a PDF before sending to the label printer.
Batch Printing Labels
For this part of the process, you’ll be creating the data with which we will print the call # labels.
Create an excel file
Open Excel file and title it “Mail Merge Template”.
Create two columns: one for “Year” and a column for “No”.
Input the year you are processing under “Year”.
From the total shown in the number of rows in Airtable, create as many lines in Excel.
Keep in mind copy 2s and multiple volumes, which will require creating labels by hand (see above).
To quickly and simply create the entries you need:
Input the year into two lines, and then under number input just the “1” and “2”.
Select the years and number cells.
Click and hold on the bottom right square of the selection.
While continuing to hold the mouse button, drag downward. If done correctly you will see a grey box by the mouse cursor showing a number. This number increases as you drag further and represents the current number created.
“Save as” the Excel template with a unique name to the year (no need for strict naming rules, just saving it as something else).
Mail Merge (in Word)
Through Mail Merge, you’ll create a document that will automate the call # label printing process. The main purpose of using “Mail Merge” is for creating a bunch of address labels. Since you aren’t sending out wedding invites, the terminology might not make much sense but it’ll still do what we need.
Open the “Thesis Mail Merge Template” Word document: https://usu.box.com/s/wn0dhynskjnjb1rvu3fkgib6auf91tph
The “USU Spec Coll” section is a header created in Word. You do not (and should not) need to make any edits here.
There should be an empty line between “Theses” and “no.”.
There should be no space after “no.”.
To begin the Mail Merge Process, click on “Mailings” in the top tool bar.
Click on “Select Recipients”.
Click on “Use Existing List”.
Select the Excel file you created and saved and click “Open”.
For the next dialogue box, you should be able to just hit “Ok”, but ensure that the checkbox is selected for “contains headers”. This will omit the top line in the Excel file that denotes the columns for “Year” and “No.”.
At this point, your document will be prepped for pulling information from the Excel file.
While still under the “Mailings” category in Word, find the “Insert Merge Field” button. Click on it, and notice that it gives you options for “Year” and “No”. You’ll use this to pull in information from the Excel file.
Click on the empty line under “Theses”. The line should contain no spaces.
Click on “Insert Merge Field.”
Click on “Year”.
You should now see <<Year>>.
Click directly after the “no.”. Again, there should be no space after “no.”.
Click on “Insert Merge Field”. This time, select “No”. You should see <<No>>.
Through this, Mail Merge will create a page for each line in the Excel file, inserting the year and number listed. It doesn’t necessarily need to be sequential: you could (and likely later will need to) create a custom Excel file that lists any further labels you might need, such as copy 2s.
For copy 2s or volume whatevers: the Word template should have space under the “no.” line where you could enter “c.2”. Make sure there are no spaces after “c.2” and that Word doesn’t create another blank page. (If it does just click at the top of the second page and hit backspace until it goes away.)
For the last steps, click on “Finish & Merge”.
Click “Print Documents”.
In the next dialogue box, “All” should be selected. Click “ok”.
For the printer, select “Adobe PDF” and select “Ok”.
You will now save the document under whatever name works best for you.
The file should open automatically. If not, find it and open it.
Did you notice that “Merge to Adobe PDF” button? Yeah you’d think that would be a shortcut but it creates a PDF file for EACH label, which I discovered after testing. Now if you’ll excuse me I have to clean up a bunch of PDF files from my desktop.
Print the document to the label printer.
Before you print, you may want to make sure there are enough labels in the printer. It will resume printing upon replacing the roll if it runs out, so don’t worry if you forget to check.
Do not save the Word Template! It keeps some residual info that might mess something up later. Haven’t played around with it enough to be sure, but just close the Word document without saving.
Procedure 1d: Attach RFID tags
Procedure 1e: Review Numbering and Attach Labels (use only when directed)
Sign up for a year in the "Renumbering View" in SignUp tab in Airtable by adding your name to the 'Counting Assigned to" column
Open the view "Verifying Call Numbers" in the Title List
Limit view to the year signed up for
Print off the list (go to the ellipses …. And select the “print view”) or pull up on mobile device
Bring list to Theses shelves
Compare Airtable title order to shelf order and make sure they align
They should be sorted in this order:
Year
Alphabetically by Author Last Name
Alphabetically by Author First Name
Alphabetically by Title
Verify all multi-copies are listed correctly
If all looks well, follow Procedure 1c: Print Labels. Note that for now, it is best to use the "Print Labels Using PDF" instructions - which prints labels individually. Batch processing needs to be updated for multi-volume or multiple copies.
If there are problems, notify they should notify the Student Technician Supervisor (Seth) or the Head of Cataloging (Liz)
Once finished, go back to the "Renumbering View" in SignUp tab in Airtable and change the status to Numbered and Labeled
Procedure 2: Verifying Sierra and OCLC Records
Sign up for a year (or years) on the Sign Up tab under the view “Sierra Comparison Signup”. Find any year with a current status of “Labeled”.
Add your name to the Sierra Comparison Sign Up column
Change the Sierra Comparison Status to “In Progress”
Using the “Comparison to Sierra” view in the “Title List” tab
Look up each item BY THE TITLE in your assigned years in Sierra. If present verify the Material Type:
For Material Type of "a” (book) or “t” (manuscript)
Add an item record to Sierra, using info from Airtable:
Barcode
Call Number (change 090 to 099)
Location: ithes
Item Type: 57 (theses or dissertations) or 56 (Plan B report)
Note: if an item record already exists with the call number starting with “INQUIRE AT SPECIAL COLLECTIONS DESK”, delete this item record and create a new one with the information above. DO NOT DELETE any other item records (including items in the stacks, other areas of SCA, or microfilm.)
Verify the Sierra bib number is the same in Airtable, replace with correct number if needed
Look up item in OCLC.
First use the TITLE and/or AUTHOR, then use the OCLC number, if needed (add “#” or “no:” to search OCLC by number
Check each print or manuscript record for the best match (title, author, and year).
Verify it is a print record (FORM : [blank] or r)
In the “Batch Type” column in Airtable, select “Item Added – Overlay batch change”
For Material Type of “b” (microform) or “z” (online, electronic)
Search Sierra by title to confirm that there are no other appropriate records with “a” or “t” material types.
If found, replace the Sierra bib number in Airtable with bib number for the record to be overlayed.
Update the Sierra Material Type.
Follow the steps for 2.b listed above (For Material Type of "a” (book) or “t” (manuscript))
If no other other records are found, proceed to searching OCLC
First use the TITLE and/or AUTHOR, then use the OCLC number, if needed (add “#” or “no:” to search OCLC by number
Check each print or manuscript record for the best match (title, author, and year).
Verify it is a print record (FORM : [blank] or r)
If the OCLC record is print AND the Sierra record is a “b” material type
Select “Merge” as the “Batch type” in Airtable
Verify that the OCLC record # matches the # in Airtable, replace with correct number if needed
If the OCLC record is print AND the Sierra record is a “z” material type
Select “Merge OCLC, No Sierra” as the “Batch type” in Airtable
Verify that the OCLC record # matches the # in Airtable, replace with correct number if needed
If there is no OCLC record for a print item, do the following to the Airtable record:
Select “Original” as the Batch type
Delete the OCLC # from its column
For items “Added” to Airtable
Search for item in Sierra by searching for title,
If found, check the “Flag for Becky” column and leave a note in the “Notes” column
If not found, proceed to next step
Search for item in OCLC
If a print record is found (has OCLC Fixed Fields Form: [blank] or r), add the following to Airtable
Add the OCLC #
Select “Merge” as the “Batch type” in Airtable
If no print record is OCLC and no record in Sierra, record the following in Airtable:
Select “Original” as the Batch type
When finished, return to the “Signup” tab and update the following
Last Completed Step - change to “Sierra Comparison’d”
Sierra Comparison Status - change to Completed
Procedure 2: Known Oddities
This list will grow with time. Please add to it as needed, to help collect the issues and actions into one space. For all oddities not listed here, please check the “Flag for Becky” column in Airtable and leave any pertinent information in the Notes fields.
Year is different between Airtable and OCLC or Sierra
Explanation: If there is a discrepancy in the theses/dissertation year listed in Airtable and the year listed in Sierra or OCLC, then the master records will need to be adjusted by a cataloger.
Action:
Check the “Flag for Becky” column in Airtable and leave a note in the “Notes” column explaining the issue (for example: “The year is listed differently in Sierra from Airtable”
OCLC returns no records when it is obvious one should exist
Explanation: Likely caused by beginner articles (a, the, an) or the use of Boolean terms in the title (AND, OR, NOT)
Action:
Strip out articles and Boolean terms or simplify to just the keywords in the title
Procedure 3 - Final Quality Control
This process will review the information recorded in each year and the batch type assigned to the item prior to Batch Record Creation process.
Sign up for a year on the “Sign Up” tab in Airtable, including recording the name of the quality controller, the status (changed to “In Progress”), and the date the review started.
In the “Title List” tab, use the “Batch Check” view and filter for the year being checked.
Look for pastel colored lines to indicate where information is missing.
If found, use the commenting feature in each record to request a check and verification of the missing information
Review the Batch Type and ensure the following parameters
Merge - verify that there is both an OCLC # and a bib #, material should be set to be “b”
If the material type is “a” or “t” – double check Sierra for a print version bib that might have been missed in Procedure 2
If the material type is empty or OCLC or Sierra is empty – double check Sierra to check for a print version bib
If you do find a print version bib for either (not microform or electronic) – make a new Item record using Procedure 2, step 2-b
Delete any “INQUIRE AT SPECIAL COLLECTIONS DESK” item records
Change the Batch Type to “Item Added” and add the Sierra/OCLC numbers, plus “a” or “t”
Item Added - verify that there is both an OCLC # and a bib #, material type should be an “a” or “t”
Original - verify that there is no OCLC #, bib #, or material type listed. (Note: the only exception is for a “z” material type, in which case there may also be an OCLC # or bib #, but these can be ignored & often are for electronic versions.)
Merge OCLC, no Sierra - verify that there is an OCLC # and that there is no Sierra record (Note: These are often incorrectly labelled as “Merge” and can be spotted when “Merge” records have a “z” Material type record with both an OCLC# and a Sierra bib #. )
Catalog Individually - not a batch catalog prospect
Review author’s name fields, in particular:
Fix errors caused by diacritics
Add “$q” before all expanded forms and enclose the terms in parentheses:
Expanded Form of Name: $q(Sidney Walker)
Add “, “ [comma and a space] before all suffixes [See OCLC’s Bibformats page about personal names for more info]
Make sure the suffix is not repeated in the expanded form:
Expanded: Sidney Walker, jr. <--take out duplicate suffix
Suffix: Jr.
rare exception:
Author first: Laurence J.
Expanded: Laurence Junior
Add periods to initials, if missing
Review the Title field, in particular:
Fix errors caused by diacritics
Remove ending periods
Remove any subfield c information
Ensure a subfield b is placed before any remainder of title (usually after a colon)
Fix obvious spelling errors or typos. Be sure not to change scientific names or personal names.
Assign the Theses Type (Standardized) based on the Theses Type field
Thesis = Theses
Plan B Report = Plan B Report
Dissertation = Dissertation
Any other type in the Theses Type field = Book Noncirculating
Verify that the Department name does not have any spelling or punctuation errors
Verify that the Degree type does not have a spacing, punctuation, or capitalization errors (Note: This may be done batch changes periodically.)
Verify that the University Name is correct and correctly assigned for the time frame. (See University Name changes for dates.) If the latter appears incorrect, use the comment feature to request a check on the item. The information found on the thesis will be the final information recorded in the Airtable record.
Review the pagination and make sure they are listed as “leaves”, check for egregious errors
Verify that there is a barcode
Check the multiple copies/volumes for the number of additional items that might belong to the title (Note: These are also flagged in purple.) Ensure that a barcode and copy/volume number is provided for each additional item.
Verify that a number has been assigned to the item
For the entire year, check the Numbering to ensure that there are sequential (no skipped numbers) and match the “Numbered” column in the Sign Up table.
If there is a mismatch, check the “All Items Sorted by Date and Author” view in the Title. Missing numbers may be filtered out of your view because they have been flagged for review in a previous process
Replace all double quotation marks (“) with a single quotation (') [note this is to make the find and replace in the batch processing simpler]
For all “Item Added - Overlay batch changes”, check the item in Sierra to make sure the added item number matches the thesis call number in Airtable.
For items that meet all of the above criteria, check the “Ready for Batch Cataloging” column
Please skip any items listed as “Catalog Individually” in the Batch Type
Record the status of the collection (year) on the Sign Up page and date when the review is completed.
Complete = all items have been approved and no items are waiting to be reviewed by the cataloger
Reviewed, some items remain = the year has been thoroughly reviewed and all applicable items approved, but there are remaining items in the year that need to be addressed. Record which items they are and needs to happen with them.
Hold off = there is a problem with all records in this year. These items will need to be revisited at a later date.
Procedure 4 - Batch Record Creation and Importing
This process will outline how to take the data from Airtable and merge with or create original records for OCLC. There are three processes used depending on the needs of the record:
Merge: Used for records where an existing OCLC record can be merged with the updated and verified Airtable table. Records are brought into Sierra as new records.
Overlay: Used for records where an existing OCLC record can be merged with the updated and verified Airtable table. Records are overlayed on top of existing Sierra records.
Original: Used to create new records where none exist in OCLC or in Sierra.
Please note that a single year may include all three workflows. Once all items in a year are accounted for, change the “Last Completed Step” column in the Thesis Remediation Airtable to the value ‘Complete’. Otherwise, change the value in this column to the last completed batch process.
Thesis Batch Merge Processes (with and without overlay)
General Merge Workflow Overview
MarcEdit Brief record creation for merging: export from AirTable → Open in Excel spreadsheet and edit → useMarcEdit Tab Delimited Convertor to make brief MARC records (.mrk) → check/edit MarcEdit Brief Records → save for merging.
OCLC records batch-search and edit: change (OCoLC) prefix to * → copy entire OCLC# column → save in notepad → load notepad txt file in OCLC batch processor → point to empty OCLC local save file → run OCLC Batch Process search → export all searched records → convert OCLC file to MarcEdit mnemonic (.mrk) → delete certain fields (such as the 502's) → save for merging.
Merge brief & OCLC batchsearched records together: batch edit → compile into MARC (.mrc) format → load into Sierra → make Create List of LOAD → global edit Item call numbers to 099 → DONE!
Changes needed for the two Batch Merge Processes
Brief record will have:
OCLC # - match point
Title – match point
099
100 – cleaned up author
245 – cleaned up title
264 – correct year
300 – concatenated from the pagination column and the illustration column
500 – DEPARTMENT
500 – Transcribed university name (where different from the official name).
502 – statement: DEGREE $cGRANTING INSTITUTION NAME AT TIME OF AWARD $dYEAR
520 – Summary scraped from digital theses.
690s - Local Subject String
907 Sierra bib-number matchpoint (needed only for merge & OVERLAY)
949 info (barcode and call # and location code and item type code* )
Batch Changes to OCLC record will be:
Delete existing 100, 245, 264, 300, and 502 fields (merge afterwards)
Add 33x fields if missing
Change 504 to “Includes bibliographical references.”
Add in 655_7 Academic theses |2 lcgft
Add 590 “Thesis Remediation Project”
Change 049 from UUSA to UUSP
Add a 710 for Utah State University
Add a 909 for CMS stats
I. DETAILED PROCEDURE FOR MERGE (with no overlay) workflow
MarcEdit Templates to download: under revision
Thesis-MERGEnoover-TEMPLATE3 is used during the conversion of the spreadsheet to MARC.
ThesisMERGEnoover-MergeSettings2 is used during brief & OCLC bib record merging.
Spreadsheet Manipulations:
Go to AirTable Thesis Data Pull
Click on the Sign Up tab, Batch Loading view; and sign up for a theses year
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to Title List and the view: Merge Batch Work
Check mark records in the column Batch Cataloging in Process
Check all essential fields are filled:
Flag any problems that need to returned for more work, checking etc. (incomplete, problem, etc.)
Download CSV file of items by clicking on the down arrow at the top menu, in one of two ways:
First filter by your chosen year: Thesis Contains [your chosen year], before downloading;
Or, if titles are already filtered by your year, go ahead and download them
Add the year of the download to the CSV name while downloading to the Desktop, like so: Title list-Melanie - Merge Batch Work - 1931
Open Excel; Open other workbooks; on Computer; Desktop; choose All Files; navigate to the CSV file (under title: Title list-Melanie/Joe - Merge Batch Work; choose Delimited; Next; click in Comma box, Next, and Finish.
If you haven’t done so already by filtering to your year in Airtable, delete all but your chosen year from the spreadsheet.
Double quotation marks
To separate and protect actual 500 quoted notes from CSV escape symbols; click on the corner of the spreadsheet to select the entire file;
go to Find and Select; then Replace;
Find what: " (double quote) – if there are no double quotes, go on to next step.
Replace with: ^ (inverted caret); Save.
Column work:
Delete: Thesis column with text in the format of year-number-Author in the cells)
Delete: Batch Cataloging in Process
Delete: OCLC #
Keep: OCLC w Prefix
Keep all MARC tag columns (note: Excel will have unhelpfully changed 099 to 99; keep it anyway).
Delete: Batch Type
Delete: Date loaded
These columns must remain (even if empty); because we will be using a MarcEdit template: again, do not delete any of these:
OCLC w Prefix - in this format, (OCoLC)####
99
100
245
264
300
500
500-2
500-3
502
520
590-1
590-2
690-1
690-1a
690-2
690-3
690-4
949
first Multiple949
second Multiple949
third Multiple949
Save As the Excel Merge file as Text (Tab Delimited); under a new name (such as: Theses-MergeNoover-year)
Convert to a file of MARC records using MarcEdit with our AirTable data:
In MarcEdit; Tools; Delimited Text Translator;
Source File: Theses-MergeNoover-year
Output File: [new name such as Theses-MergeNooverBrief-year]
Click Import File (formerly Next)
Then: click on Load Template (may need to scroll down to the bottom of the window) navigate to Thesis-MERGEnooverMARC-TEMPLATE3 (downloadable template is available at the beginning of this procedure , if needed)
Fields will load automatically in the Arguments window.
Check the Template has loaded the correct mappings in the Arguments window (scrolling with the mouse or arrow keys)
Tags and first subfield:
035$a
099$a
100$a
245$a
264$c
300$a
500$a
500$a
500$a
502$b
520$a
590$a
590$a
690$a
690$a
690$a
690$a
690$a
949$a
949$a
949$a
949$a
Click Finish.
Edit the Brief Records in MarcEdit:
Open MarcEditor,
Click on File; Open; then navigate to the new file.
You can doublecheck the mappings by looking over the first mock record in the file to see that the tags and mapping match:
Use Reports; Field Count; and check that 100, 245, 300, etc. match number of records (ignore 500’s, 590’s, etc., where more than one field will exist per record)
(However: that first mock record will also be counted; so subtract 1 when looking for the correct number of records)
Edit the Brief record (.mrk) file.
Use Edit; Find; Replace to get rid of CSV double-quotes (used to protect commas in the 100’s, etc.).
Find: " (one double quote)
Replace: (with nothing)
Replace all to clean up the entire file.
Then replace any 500 quoted notes, if any (which would be protected earlier by replacing their quotation marks with carets):
Find: ^ (inverted caret)
Replace: " (double quote)
Replace all.
Like so:
=500 \$aInstitution name listed as ^Utah State Agricultural College^ on the title page.
=500 \$aInstitution name listed as "Utah State Agricultural College" on the title page.
Then File; Save (it’s a good idea to do a save after each major edit).
Check the subfield d’s in the 100 fields which often have problems:
Edit; Find: =100
To fix them, do an Edit; Find; Replace
Find what: $d$e ← which won’t occur anywhere else in the records.
Replace with: ,$e ← comma,$e
If there are any authors with birthdates in the 100s that are missing a d-subfield, do an Edit Field Data to add them:
Tools; Edit Field Data:
Field: 100
Find: 1 (type a single space, followed by a 1)
Replace: $d1 (which will take out the space, add the subfield d & reinsert the 1)
then if there are incorrect commas at the end of a year [dash], do another Edit Field Data:
Field: 100
Find: -,$e (a dash, comma, $e)
Replace: -$e (removes the comma)
Example of (most probably) exhaustive name with subfields: Smith, Sam C.$q(Sam Christian),$cJr.,$d1985-2045,$eauthor.
Do a Edit; Find; Find All on =245 for each article (as below) to check for titles with articles that are missing a skip indicator. MarcEdit usually catches all the A, An, and The articles when converting to MARC, but it misses one or two on occasion.
=245 10$aA ← add a single [space] after each to avoid title words starting with 'A'
=245 10$aAn ← add a single [space] after each…
=245 10$aThe ← add a single [space] after each…
Replace any found with the correct skip number (remembering to add the following space):
Find: =245 10$aA ← add a single [space]
Replace: =245 12$aA ← add a single [space]
Replace All: (and make sure the number of modifications matches the number of titles found with the issue)
Then add our local constant data:
Tools; Add/Delete Field; copy/paste the values below in blue; then Add Field:
Add 590, field text: \\$aThesis Remediation Project.
Add 655 field text: \7$aAcademic theses.$2lcgft
Add 710 field text: 2\$aUtah State University.
(Note that each field will also be added to the first mock record).
***Check for any double spaces that may have been mistakenly entered in previous steps, e.g. “50 leaves _ _ :$illustrations"***
If these truly don’t belong in the record, use Find & Replace to reduce to one space.
Finally, use Edit; Find on important fields to do quick check for any more egregious errors (but don’t get caught in the weeds).
Remember to Save the file of cleaned up brief records for the Merging step.
Batch-search Connexion with OCLC numbers:
In Excel and Notepad, create a text file of OCLC numbers for the batch search in OCLC Connexion.
Open Excel and go back to the spreadsheet: Thesis-MERGEnoover-year
Copy/insert OCLC w Prefix into a new column; Replace (OCoLC) with * (asterix).
Select all the OCLC numbers; right-click; choose format cells; & change Category to text.
Copy and paste the cells in the OCLCbatchsearch column to Notepad; save as text; with an easy to find title, in this format: OCLCnos-year
Note the number of *OCLC rows (records).
In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).
Go to Cataloging; Search; Local Save File; then hit OK, leaving the search box blank, to make sure there are no records lingering in there;
Delete any lingering records.
Go to Batch; Enter Bibliographic Search Keys; Import.
Navigate to your text file of *OCLC numbers, make sure the Default Index is: None, and click Open.
I always click Don’t Delete, when asked (to allow for error checking and redoing).
The text file should load into the Bibliographic Batch Search Window; click Save; then close window.
In File; Local File Manager; Set up Authorizations; add Authorization & Password; Save & Login.
Go to Batch; Process Batch; make sure the empty Local Save File is still selected (if not, select it again).
Only Online Searches should be selected; then click OK.
The OCLC numbers should scroll down the view window, until all have been searched.
Finally, a Batch Search Report will appear; go down to the last save number in the report; it should equal the number of *OCLC row numbers that you wrote down earlier.
The Local Save File should have received all the records.
Go to Cataloging; Search; Local Save File; then hit OK, leaving the search box blank.
Select every record in the batch save file (select first record; hit Ctrl and Shift together, at the same time clicking on the last record).
Action; Export; name the export file, something like this: THESES-OCLCbatchsearch-year.
Open MarcEdit; click on MARC Tools icon; leave Select Operation as MarcBreaker;
Open (under All Files): THESES-OCLCbatchsearch-year
Save As: Theses-OCLCbatchsearch-year
Leave Default Character Encoding as MARC8
Click Execute; then click on Edit Records:
Edit; Find/Replace: =049 \\$aUUSA to =049 \\$aUUSP; Replace All.
Delete these fields which we will be replacing with our updated versions:
Edit Records and Tools; Add/Delete Fields, fill in the tag in Field; and leave Field Data blank; then click Delete Field:
Field: 100
Field: 245
Field: 260
Field: 264
Field: 300
Field: 502
Field: 520
For the following Fields, we will be deleting certain duplicate versions, using Add/Delete Fields:
Field: 655 Field Data: \7$aAcademic theses.$2lcgft
Field: 655 Field Data: \4$aAcademic theses.
check for other versions of 655 Academic theses headings to delete, but don’t remove FAST headings:
=655 \7$aAcademic theses.$2fast$0(OCoLC)fst01726453 ← keep fast 655 fields
Delete:
=655 \2$aAcademic Dissertation$0(DNLM)D019478
=655 \7$aTh{grave}eses et {acute}ecrits acad{acute}emiques.$2rvmgf$0(CaQQLa)RVMGF-000001173
=655 \7$adissertations.$2aat$0(CStmoGRI)aatgf300028029
=655 \7$adoctoral dissertations.$2aat$0(CStmoGRI)aatgf300312076
=655 \7$amasters theses.$2aat$0(CStmoGRI)aatgf300077723
=655 \7$atheses.$2aat$0(CStmoGRI)aatgf300028028
Find; Find All =504 to see that all them have the form: Includes bibliographical references;
Find: =504 .* [Must select “Use Regular Expressions” option]
Replace: =504 \\$aIncludes bibliographical references.
Search for any questionable 008 fields that don’t have an s date type.
Find all: =008 [0-9]{6}[^s] [Must select “Use Regular Expressions” option]
These will have a “q” and two dates in the 008 instead of just one.
Jump to Record:
The field in question will be highlighted, so do an Edit; Replace
Copy/paste and very carefully change only the effected portions of the 008: qyearyear with syear\\\\
An example:
Find: q19291957 (the original cataloger was unsure of the year, and put in a time span)
Replace: s1923\\\\ (but we know the year was 1923)
Run Reports; MARCValidator to make sure no blanks “\” or digits haven’t been lost in the 008 fields.
Note that MARCValidator may mark the lack of 245 fields as an error, but those will be replaced in the next (Merging) step.
Save edited OCLC file for Merging step (and Close).
Merge Brief file and OCLC batch-searched file records together:
Go to Tools, MARC Processing Tools, Merge Records, then navigate to the two files, in this general form:
Source file: Theses-OCLCbatchseach-year.mrk → (OCLC batch-searched/edited records)
Merge File: Theses-MERGENooverBrief-year.mrk → (brief records with our data)
Save file: Theses-MERGED-noover-year.mrk → (future file of merged records)
Record identifier: 035$a → (Important Matchpoint)
Click Next:
Use the saved Merge Settings template: click on Settings; Load Settings;
Then navigate to: Thesis-MERGEnoover-MergeSettings2
If the Settings file isn’t working; highlight the fields below, and click on the arrow to select them for merging into the OCLC file of records.
Do quick check of the loaded Settings:
049 UUSP
099 local call number
100 Author (our corrected version)
245 Title (ours)
264 RDA production date (ours)
300 Physical description (ours)
500 Department & transcribed note
502 RDA standardized thesis statement
520 Scraped summary field
590 Project name
655 Academic theses genre term
690 local subject string
710 university
949 automatic item record creation
Then click Next to do the merge.
Batch copy catalog the merged records in MarcEdit (click on MarcEditor icon; Open and navigate to newly merged file):
Do a Reports; Field Count; to check the merge worked; check that field 001, 035, 245 matches correct number of bibs;
Edit/Find various important fields: 100, 245, 502, etc.,
Check for duplicated fields, such as 300 fields; and delete one version.
Edit/Find all =949 fields and ensure they match the number of barcoded items expected
Scan through a few bibs, to look for egregious errors, etc. -- but don’t get lost in the weeds.
Check 520’s for text that is cut off. Where found, add an ellipsis (…) to the end of the field.
Check 65Xs for foreign-language entries.
Easiest if you alphabetize the Find results
Sign your work!
Add/Delete Fields; Field: 909 Field Data: \\$aMelanie Shaw$b2022-05-04$eMODIFIED$dCMS STATS
Save.
Validate the file by going to Reports; MARCValidator or using Control M
Common errors include:
Dollar signs in the 520 → Fix by replacing dollar signs with a {dollar}
When working with economics or business theses, the validator may return an error that the 520 field has an invalid subfield. It is likely that the summary/abstract contains a monetary value, and MarcEdit is interpreting the ‘$’ used to denote USD as a subfield delimiter. If this occurs, please refer to the relevant batch-edit step at the end of this workflow.**
Save file again.
File; Compile into MARC format with a new name: ThesesMERGED-noverDONE-year.mrc
Load file into Sierra and track:
Use the first load table: LOAD/Overlay bibs & create items (.briefbiboverlay), and remember to click on the square with Use Review Files” before starting the test & load.
do a Test load first; check to make sure the number of bibliographic records and the number of item records is correct (Note: if theses have more than one copy or volume, these numbers will differ)
if the test goes through with all the records; Load.
Go to Create Lists, then choose an empty line with enough room for your load; click Copy icon; and find the file, which will look somewhat like this:
LOAD: inserted files for ThesesMERGED-nooverDONE-year (6)
It will ask Do you want to remove the file being copied from? Yes, you do.
Double-click on the new list and check a few bibs to make sure the records loaded properly.
Leave your Load list in Create Lists for later Global Edit to the item records in Sierra.
Leave the Load list in Create Lists for later Global Edit in Sierra.
Follow the process for Global Edits to change the 090 to 099 in the item record : https://usulibrary.atlassian.net/l/cp/z0cUb0Ur (note: this is a restricted workflow)
**Fixing $ error:
Use the newly created bibliographic-type review list to perform a batch update.
Create a second bibliographic-type list by searching the first list with the added condition:
Bibliographic MARC Tag 520 matches "|[0-9]"
In the Global Update function, search for the second review list
Perform a “Variable-length field edit” on the 520 fields, changing all “|” to “$”
Finally, because this will convert the initial subfield A delimiter to a “$” as well, perform a second ““Variable-length field edit” to change all “$a” to “|a”
Check a couple of records as normal to verify no errors were created in the process
Return to the MERGE view in the Thesis Remediation Airtable & record the Date Loaded in Sierra.
In the Sign-Up Tab, change the following:
“Merge Batch Loaded Status” to Completed
“Merge Batch Load Started” and “Merge Batch Load Ended” dates are filled in with the correct information
Add any notes that will be needed for future in the Batch Loaded Notes field (for example, if there were issues encountered or records that need to be reviewed by someone)
“Last Completed Step” column to “Merge Batch Loaded” UNLESS the “Remaining Items to be loaded into Sierra” value is 0 (zero) - in which case, change to “Complete”
Global Update for Thesis Batch Load link: https://usulibrary.atlassian.net/wiki/spaces/ULC/pages/1497038917
Done!
II. Detailed Procedures for OVERLAY (with Merge) workflow
Undergoing revision, please wait until finished
Overlay_DTT_Coversion_Templant.mrd for conversion to MARC:
Used for overlaying a Sierra record for thesis/dissertation which was actually cataloged for a print format thesis.
Spreadsheet manipulations:
Go to AirTable Thesis Data Pull
Click on the Sign Up tab, Batch Loading view; and sign up for a theses year
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to Title List and the view: Overlay Batch Work
Check all essential fields are filled:
Flag any problems (incomplete, problem, etc.)
Check for missing barcodes in 949s (do not select)
Mark all non-problem items within a year (if possible) and download CSV file of items.
Checkmark the column Batch Cataloging in Process for the chosen year.
Open Excel; choose All Files; navigate to CSV file; choose Delimited; tab; & Save as Excel File with new name, (something explanatory like: Theses-mergeOVERLAY-year).
Delete all but the chosen year, if not done already.
Column work:
Delete: Thesis column with text in the format of year-number-Author in the cells)
Delete: Batch Cataloging in Process
Delete: OCLC #
Keep: OCLC w Prefix
Keep all MARC tag columns (note: Excel will have unhelpfully changed 099 to 99, keep it anyway).
Delete: Batch Type
Delete: Date loaded
All these columns should remain (including blank columns):
OCLC w Prefix - in this format, (OCoLC)#### [will serve as 035]
099
100
245
264
300
500
500-2
500-3
502
520
590-1
590-2
690-1
690-1a
690-2
690-3
690-4
907 (for overlaying)
Save the Excel OVERLAY file as Text (Tab Delimited); new name: Theses-mergeOVERLAY-year
Double quotation marks protection:
To separate and protect actual quoted notes from CSV escape symbols:
Click on the corner of the spreadsheet to select the entire file;
Go to Find and Select; then Replace;
Find what: " (double quote)
Replace with: ^ (inverted caret).
Save file.
Convert to a file of MARC records using MarcEdit with our AirTable data:
In MarcEdit; Tools; Delimited Text Translator;
Source File: Theses-mergeOVERLAY-year
Output File: [new name such as Theses-mergeOVERLAY-Brief-year
Click Import File (formerly Next);
Then: click on Load Template; navigate to Overlay_DTT_Coversion_Templant.mrd (downloadable template is available at the beginning of this procedure )
Fields will load automatically in the Arguments window.
Check the fields against the list in step c, to make sure if they loaded correctly (only the MARC field/1stsubfield will show.
Or, you can manually enter in each column (MarcEdit calls them fields) and map them to MARC:
Select or type: MARC tag; 1st subfield; and Indicators; (no terminal punctuation needs to be added).
Fields for checking or error resolution in blue:
035$a Indicators: \\ no terminal punctuation
099$a Indicators: \\ no terminal punctuation
100$a Indicators: 1\ no terminal punctuation
245$a Indicators: 10 no terminal punctuation
264$c Indicators: \0 no terminal punctuation
300$a Indicators: \\ no terminal punctuation
500$a Indicators: \\ no terminal punctuation (all of these)
502$b Indicators: \\ no terminal punctuation
520$a Indicators: \\ no terminal punctuation
590$a Indicators: \\ no terminal punct. (all of these)
690$a Indicators: \\ no terminal punctuation (all of these)
907$a Indicators: \\ no terminal punctuation (overlay b-number)
Click Add Argument to enter each mapping. The MARC fields will show up in the Arguments preview;
right-click to Delete or Edit.
Click Finish.
Edit the Brief Records in MarcEdit:
In MarcEdit; open MarcEditor; Open, then navigate to the new MARC mnemonic (.mrk) file
You can doublecheck the mappings by looking over the first mock record in the file to see that the tags and mapping match.
Go to Reports; Field Count to make sure the number of records is correct
(records number should be the numbers of the 100, 035, & 245 field count; HOWEVER, the first mock record will be counted: so subtract 1 for the correct number)
Use Edit; Find; Replace to get rid of CSV double-quotes.
Find: " (one double quote)
Replace: (with nothing)
Replace all to clean up the entire file.
Then fix any 500 quoted notes (which were protected back in the spreadsheet by replacing their quotation marks with carets):
Find: ^ (inverted caret)
Replace: " (double quote)
Like so:
=500 \$aInstitution name listed as ^Utah State Agricultural College^ on the title page.
=500 \$aInstitution name listed as "Utah State Agricultural College" on the title page.
Then File; Save (it’s a good idea to do a save after each major edit).
Use Edit; Find on each field to check for errors
Check the subfield d’s in the 100 fields which often have problems:
Edit; Find: =100
Edit; Find; Replace
Find what: $d$e ← which won’t occur anywhere else in the records.
Replace with: ,$e ← [comma]$e
Edit; Find; Find All on =245 for each article (as below) to check for titles with articles that are missing a skip indicator. This is usually caught by MarcEdit, but sometimes…
=245 10$aA ← add a single [space] after each to avoid title words starting with 'A'
=245 10$aAn ← add a space
=245 10$aThe ← add a space
fix any articles found using Edit; Find; Replace:
Find: =245 10$aThe ← add a space
Replace: =245 14$aThe ← add a space
Add our local constant Data:
Field: 590, field text: \\$aThesis Remediation Project.
Field: 655 field text: \7$aAcademic theses$2lcgft
Field: 710 field text: 2\$aUtah State University.
Scroll down to look for egregious errors in our locally generated (variable and local constant data) fields, especially for our descriptive/searchable tags, but don’t get lost in the weeds.
Then Save the file of cleaned up brief records.
Batch-search Connexion with OCLC numbers:
In Excel and Notepad, create a text file of OCLC numbers for the batch search in OCLC Connexion.
If not already done, copy/paste OCLC numbers to a new column; replace (OCoLC) with * then name the column: OCLC batchsearch.
Select all the OCLC numbers; right-click; choose format cells; & change Category to text.
Copy and paste the cells in the OCLCbatchseach column to Notepad; save as text; with an easy to find title, in this format: OCLCnosOV-year
Note the number of *OCLC rows (records).
In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).
Do a search/return key to make sure there are no records lingering in there;
Delete any lingering records.
Go to Batch; Enter Bibliographic Search Keys; Import.
Navigate to your text file of *OCLC numbers, make sure the Default Index is: None, and click Open.
Always click Don’t Delete, when asked.
The text file should load into the Bibliographic Batch Search Window; click Save; then close window.
In Local File Manager; Set up Authorizations; add Authorization & Password; Save & Login.
Go to Batch; Process Batch;
make sure the empty Local Save File is still selected (if not, select it now).
Only Online Searches should be clicked.
Click OK;
The OCLC numbers should scroll down the view window, until all have been searched.
Finally, a Batch Search Report will appear; go down to the last save number in the report; it should equal the number of *OCLC row numbers that you wrote down earlier.
The Local Save File should have received all the records.
Go to Search; Local Save File; then hit Search, leaving the search box blank.
Select every record in the batch save file.
Export; name the export file, something like this: THESES-OCLCbatchsearchOver-year.
Open MarcEdit; click on MARC Tools icon; leave Select Operation as MarcBreaker;
Open (choose All Files): Theses-OCLCbatchsearchOver-year
Save As: Theses-OCLCbatchsearchOVER-year
Leave Default Character Encoding as MARC8
Click Execute; then click on Edit Records:
Edit; Find/Replace: =049 \\$aUUSA to =049 \\$aUUSP
Delete these fields which we will be replacing with our updated versions:
Tools; Add/Delete Fields, fill in the tag in Field; and leave Field Data blank; then click Delete Field:
Field: 100
Field: 245
Field: 260
Field: 264
Field: 300
Field: 502
Field: 520
For the following Fields, we will be deleting certain duplicate versions, using Add/Delete Fields:
Field: 655 Field Data: \7$aAcademic theses.$2lcgft
Field: 655 Field Data: \4$aAcademic theses.
Field: 655 Field Data: \4$aAcademic dissertations.
check for other versions of 655 Academic theses headings to delete, but don’t remove FAST headings:
=655 \7$aAcademic theses.$2fast$0(OCoLC)fst01726453
Check that 504 fields have: Includes bibliographical references (and change any incorrect), but don’t add them where they are missing.
Find: =504 .* [Must select “Use Regular Expressions” option]
Replace: =504 \\$aIncludes bibliographical references.
Save for Merge step.
Merge Brief file and OCLC batch-searched file records together.
Go to Tools, MARC Processing Tools, Merge Records, then navigate to the two files, as in this example:
Source file: Theses-OCLCbatchseach-year.mrk → (OCLC batch-searched/edited records)
Merge File: Theses-mergeOVERLAY-Brief-year.mrk → (brief records with our data)
Save file: Theses-mergedOVERLAY-year.mrk → (future file of merged records)
Record identifier: 035$a → (Important Matchpoint)
Click Next to begin selecting the fields to merge.
Select/type each field to be merged into the OCLC batch-searched file; click the Green arrow to select each:
049 UUSP
099 local call number
100 Author (our corrected version)
245 Title (ours)
264 RDA production date (ours)
300 Physical description (ours)
500 Department & transcribed note
502 RDA standardized thesis statement
520 Scrapped summary
590 Project name
655 Academic theses genre term
690 local subject strings
710 university
907 Sierra bib-number for overlay
Then click Next to do the merge.
Batch copy catalog the merged records in MarcEdit:
Reports; Field Count; to check the merge worked (001, 035, & 245 fields should match number of records);
Edit; Find; Find All various fields, search for egregious errors, etc.
Validate
When working with economics or business theses, the validator may return an error that the 520 field has an invalid subfield. It is likely that the summary/abstract contains a monetary value, and MarcEdit is interpreting the ‘$’ used to denote USD as a subfield delimiter. If this occurs, please refer to the relevant batch-edit step at the end of this workflow.
Compile cleaned up merged files into MARC format: ThesesmergedOVERLAYdone-year.mrc
Load file into Sierra and track:
Use the first load table: LOAD/Overlay bibs & create items (.briefbiboverlay), and remember to click on “use review files”;
Do a Test load first; then if the test goes well; Load.
Go to Create Lists; Copy; find file, looking somewhat like this:
LOAD: inserted files for ThesesmergedOVERLAYdone-year (#) ← number of bib records loaded in parentheses
Click on a few records and make sure the records loaded properly.
Leave the Load list in Create Lists for later Global Edit in Sierra.
Follow the process for Global Edits to change the 090 to 099 in the item record : https://usulibrary.atlassian.net/l/cp/z0cUb0Ur (note: this is a restricted workflow)
**Fixing $ error:
Use the newly created bibliographic-type review list to perform a batch update.
b. Create a second bibliographic-type list by searching the first list with the added condition:
Bibliographic MARC Tag 520 matches "|[0-9]"
c. In the Global Update function, search for the second review list
d. Perform a “Variable-length field edit” on the 520 fields, changing all “|” to “$”
e. Finally, because this will convert the initial subfield A delimiter to a “$” as well, perform a second ““Variable-length field edit” to change all “$a” to “|a”
f. Check a couple of records as normal to verify no errors were created in the process
Return to the OVERLAY view in the Thesis Remediation Airtable & record the Date Loaded in Sierra.
In the Sign-Up Tab, change the following:
“Overlay Batch Loaded Status” to Completed
“Overlay Batch Load Started” and “Overlay Batch Load Ended” dates are filled in with the correct information
Add any notes that will be needed for future in the Batch Loaded Notes field (for example, if there were issues encountered or records that need to be reviewed by someone)
“Last Completed Step” column to “Overlay Batch Loaded” UNLESS the “Remaining Items to be loaded into Sierra” value is 0 (zero) - in which case, change to “Complete”
Done!
III.Thesis Batch ORIGINAL Processes
Template for converting from Tab-Delimited Text (.txt) document to Mnemonic Mark (.mrk) document:
Note: This template (and all others) will only work if the columns in the .txt document are ordered exactly as included in the template.
General Workflow:
MarcEdit original record creation: export AirTable records → Open in Excel spreadsheet and edit → use MarcEdit Tab Delimited Convertor to create MARC records (.mrk) → check/edit MarcEdit Brief Records → edit fixed fields to match variable fields as needed → compile into MARC (.mrc) format for importing into OCLC.
Import/Produce newly created MARC records in OCLC: without logging into OCLC → Run OCLC validation and fix errors as necessary → login to OCLC when done and update holdings → records will receive OCLC numbers → export records to desktop → import batch file into Sierra → Global edit Item Record call numbers to 099 → DONE.
MarcEdit additions to include for Original Record Process
049 – UUSP
33x fields
504 “Includes bibliographical references.”
590 “Thesis Remediation Project”
655_7 Academic theses. |2 lcgft
Detailed Procedures for Original workflow:
UNDERGOING REVISION: please wait before using
Spreadsheet manipulations:
Go to AirTable Thesis Data Pull
Click on the Sign Up tab, Batch Loading view; and sign up for a thesis year.
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to Title List and the view: Original Batch Work
Choose just one year at a time to work on (if you don’t, individualized editing will be necessary later on).
Check all essential fields are filled:
Flag any problems (incomplete, problem, etc.)
Check for missing barcodes in 949s (do not select)
Download CSV file of items within a year, or download entire view and delete rows of other years later.
Open Excel; Open other workbooks; on Computer; Desktop; choose All Files; navigate to the CSV file (under title: Title list-Melanie - Original Batch Work, & Save as an Excel File with new name, (something explanatory like: Theses-Original-year).
Delete all but your chosen year in the spreadsheet, if not done before.
Remove any columns no longer needed for the MARC record:
Thesis (year-#-author),
Batch Cataloging in Process,
OCLC # (which will be empty),
OCLC w/ Prefix,
Batch Type,
Date Loaded in Sierra,
Barcode
Status
And keep these columns (all MARC fields):
099
100
245
264
300
500’s
502
504
520
690’s
all 949’s (including empties)
Save the Excel Original file as Text (Tab Delimited);
new name: Theses-Original-year.
Double quotation marks protection:
To separate and protect actual quoted notes from CSV escape symbols:
Click on the corner of the spreadsheet to select the entire file;
Go to Find and Select; then Replace;
Find what: " (double quote)
Replace with: ^ (inverted caret).
Save file.
Convert to a file of MARC records using MarcEdit with our AirTable data:
Open MarcEdit: Tools; Delimited Text Translator;
Source File: Theses-Original-year
Output File: [new name such as Theses-Original-MARC-year.mrk
Fixed Fields (Leader)
In the Options portion of the Delimited Text Translator, click on the blue Edit LDR/008:
LDR Edits
“nam” to “ntm”
“Ia 45e0” to “7i 45e0”
For the Leader, make sure the dropdown says: Book.
While still in the LDR, change nam to ntm and Ia to 7i
00000nam 2200000Ia 45e0
00000ntm 2200000Ki 45e0
t = manuscript monograph
K = (obsolete OCLC minimal record (should use either 3 or 7, the MARC21 equivalent)
a = AACR2 to i=ISBD (used in combination with 040 rda code)
For the 008, copy & paste the blue 008 values into the 008 edit box (replacing year with the 4 digit year):
s9999\\\\xx\\\\\\000\0\und\d
syear\\\\xx\a\\\\\bm\\\000\0\eng\d
Be extremely careful in the 008: it is really easy to accidently delete or add a digit, and then the entire file will have faulty 008 fields.
Click OK.
Click Next to begin the task of selecting each column/field and mapping it to MARC.
Select or type: MARC tag; 1st subfield; and Indicators; no end punctuation needed in this project.
099$a Indicators: \\ no terminal punctuation
100$a Indicators: 1\ no terminal punctuation
245$a Indicators: 10 no terminal punctuation
264$c Indicators: \0 no terminal punctuation
300$a Indicators: \\ no terminal punctuation
500$a Indicators: \\ no terminal punctuation
500$a Indicators: \\ no terminal punctuation
502$b Indicators: \\ no terminal punctuation
504$a Indicators: \\ no terminal punctuation
690$a Indicators: \\ no terminal punctuation
690$a Indicators: \\ no terminal punctuation
949$a Indicators: \\ no terminal punctuation no terminal punctuation
Note: There should only be one 949 for each copy of the item, e.g., “c.2,” “c.3,” etc.
Click Add Argument to enter each mapping.
The MARC fields will show up in the Arguments preview; right-click to Delete or Edit.
Note that Calculate common nonfiling data is checked. This will automatically adjust the 245 indicators for A, An, or The in the first word of a title of a record.
Finish.
In MarcEdit; open MarcEditor; then find the new MARC mnemonic (.mrk) file
Note: To solve issues related to the records' Leaders, compile the .mrk file into an .mrc file. Then, break the new .mrc file and perform the required edits on the resulting .mrk file.
Use Reports; Field Count to make sure the number of records is correct (the first mock record will be counted; subtract 1 for the correct number)
Use Reports; MARCvalidator to check records have been entered correctly during the conversion process:
If errors are found; fix them, if it isn’t major, otherwise, go back and run the conversion again.
Use Edit; Find; Find: " (double quotes) for odd double quotes around entire text in fields where it is not called for.
Then use Edit; Find; Replace:
Find: "
Replace: (with nothing)
Replace all
Change ^ (caret) back into " (double quotes):
Find: ^ (caret)
Replace " (double quotes)
Replace all.
Use Edit; Find; Find All on each field to check for errors; then add local constant data:
Add 040, field text: \\$aUUS$beng$erda$cUUS
Add 049, field text: \\$aUUSP
Add 590 field text: \\$aThesis Remediation Project.
Add 655 field text: \7$aAcademic theses$2lcgft
Add 710 field text: 2\$aUtah State University.
Scroll down the MARC records and check for GAPS after adding all those fields (which will affect all records):
Once the the adds have gone through without major hiccups, run Reports; MARCValidator, to make sure all is well.
Next, add the 33x RDA format fields, but instead of adding them individually…
Add 336
Add 337
Add 338
Use the RDA Helper in Tools with everything except Add 336, 337, 338, deselected:
It’s a great time saver.
Change ^ (caret) back into “ (double quotes):
Find: ^ (caret)
Replace " (double quotes)
Replace all.
Edit fixed field 008 in first record, if 008 fields come through incorrectly:
This will only edit one record at a time and for that record all the other codes will need to be typed in.
Place cursor at the beginning of the first record, the click on Edit; Insert/Edit 008; and choose Book:
Edit all the values for these areas (leave the backslashes or zeroes in place for all others):
Type: t
Ills: a ← unless there is no illustrations
Cont: bm
Srce: d
Lang: eng
Ctry: xx\
Dates: year of production
Click OK
In order to fix all the other record's 008 fields; use Find/Replace with the old 008 fields copy/pasted from an unedited record in the Find box and the newly edited 008 on the first record in the Replace box.
Find: =008[old 008 data from unedited record]
Replace =008[newly edited data from edited record]
Look at the recently edited 008 fields by Edit; Find, and scan down the find screen to look for oddities, such as missing indicators or jagged lines showing too short 008s with missing codes.
Also, recheck edited fields by validating: Edit; MARCValidator and fix 008 errors.
Finally, delete the first mock record: Edit; Delete Records; By Range; and type in 1; then click OK:
And the mock record will disappear.
Scan through record for egregious errors in any other Fields, especially for descriptive/searchable tags; but try not get lost in the weeds.
Validate
When working with economics or business theses, the validator may return an error that the 520 field has an invalid subfield. It is likely that the summary/abstract contains a monetary value, and MarcEdit is interpreting the ‘$’ used to denote USD as a subfield delimiter. If this occurs, please refer to the relevant batch-edit step at the end of this workflow.
Save the file of cleaned up records.
Compile into MARC: ThesesOriginalDONE-year.mrc
Import new original theses records into OCLC:
In OCLC Connexion:
First time: Set up the File; Local File Manager to create a new Local Save File path. If already created, skip this step.\
Configure the path; Close.
Each time: If the File Path has already been set up:
Any time you are ready to import new records into OCLC, make sure there are no earlier records left in the save file: especially important for this workflow
F3 and return without inputting a search; then delete any bibs found.
In Connexion, but without logging in:
File; Import Records; navigate to record file in MARC21/MARC8.
Click on Import to Local Save File Bibliographic: should show the new File Path for the save file.
Click OK.
Connexion will immediately import the file into the Save File, but since you are not logged in** the records will not yet receive OCLC record numbers. A Report with the Import results will pop up: it can be printed out (which includes as a PDF), if a record of the results will be needed.
**Note: If this is your first time cataloging offline, you will likely need to set up your offline options:
1) USU Libraries' OCLC symbol: UUS
2) Holding symbol: UUSP
3) MARC institution code: ULA
Bring up the Connexion Save file with F3 and Return key without entering any search terms:
All of the records should be there in the Save file list and can be marked and/or previewed.
Delete the first “dummy” record that was created by Marcedit (the one containing empty fields); unless it has already been removed.
Select all records (Ctrl+A for Windows; Cmd+A for Macs)
Then Edit; Validate entire file:
Fix any errors, or if too many errors, delete entire saved records in OCLC save file, and go back to MarcEdit to fix the problems there; then, redo import with clean records.
After all records validate without errors in Connexion, take a quick check for punctuation errors of one or two bibs, and if all checks out, then:
Log into Connexion and mark entire set of records,
Then: Action; Holdings; Produce/Update Holdings
Each record should be assigned a new OCLC #
Mark and Export entire file to desktop.
Name the file of new records something memorable and easy to pick out of a list like so: ThesesOriginalDONE-year
Load file into Sierra:
Use the first load table; and remember to click on “use review files”; and do a test load first; then if the test goes well; Load.
Go to Create Lists; Copy; find file, looking somewhat like this:
LOAD: inserted files for ThesesOriginal-MARC-year (6)
Click on a few records and make sure the records loaded properly.
Leave the Load list in Create Lists for later Global Edit in Sierra.
Follow the process for Global Edits to change the 090 to 099 in the item record : (note: this is a restricted workflow)
Fixing $ error:
Use the newly created bibliographic-type review list to perform a batch update.
Create a second bibliographic-type list by searching the first list with the added condition:
Bibliographic MARC Tag 520 matches "|[0-9]"
iii. In the Global Update function, search for the second review listiv. Perform a “Variable-length field edit” on the 520 fields, changing all “|” to “$”
v. Finally, because this will convert the initial subfield A delimiter to a “$” as well, perform a second ““Variable-length field edit” to change all “$a” to “|a”
iv. Check a couple of records as normal to verify no errors were created in the process
Return to the ORIGINAL view in the Thesis Remediation Airtable & record the Date Loaded in Sierra.
In the Sign-Up Tab, change the following:
“Original Batch Loaded Status” to Completed
“Original Batch Load Started” and “Original Batch Load Ended” dates are filled in with the correct information
Add any notes that will be needed for future in the Batch Loaded Notes field (for example, if there were issues encountered or records that need to be reviewed by someone)
“Last Completed Step” column to “Original Batch Loaded” UNLESS the “Remaining Items to be loaded into Sierra” value is 0 (zero) - in which case, change to “Complete”
Done!
Statistics for Procedure 4