Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 28 Next »

These procedures outline the process for two batch merge workflows that combine item level information collected in an Airtable base with existing OCLC records - as part of a large scale remediation project for Congressional Hearings.

Tools Needed

  • Sierra (local catalog)

  • Airtable                                                                                             

  • OCLC Connexion

  • MarcEdit

  • Excel

Quick Navigation

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 Overlay (Merge New Barcodes: Used for records where an existing OCLC record can be merged with the updated and verified Airtable table. Records overlay existing Sierra records.

  • Barcoded, Ready for New Cataloging: Used for records where an existing OCLC record can be merged with the updated and verified Airtable table info. Records are brought into Sierra as new records.

  • See also the separate workflow for setting OCLC holdings, for titles already fully cataloged in Sierra, but with no holdings showing in OCLC: Congressional Hearing Set OCLC Holdings Procedures

Congressional Hearings Merge Processes (with and without overlay)

General Brief to OCLC Merge Workflow

MarcEdit Brief record creation for merging: export from AirTable → Open in Excel spreadsheet and edit → use MarcEdit 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 → save for merging → Recopy the OCLC numbers with * prefix → Go back to OCLC and run the batch process to set holdings in OCLC.

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 086 → DONE!

 

Changes needed for the two Batch Merge Processes

  1. Preprocess:

    1. Jen’s crew has checked for physical items, added barcodes, checked SuDoc numbers, and so on, in the spreadsheet & uploaded to Airtable.

    2. Cheryl will delete MQ barcodes from Sierra before we bring in our Merged Batchload records.

    3. Liz has created 949 fields for each item record in Airtable.

    4. Various preprocessing will be run for items that lack a 001 or 035, or that have wrong OCLC numbers brough over from Sierra to the two Merge views.

    5. 949s for titles with multiple volumes will to be grouped to be added to a separate workflow.

  2. Excel spreadsheet processing will remove excess columns, change 86 to 099, check for the usual errors, etc.

  3. Brief records will be created/edited in MarcEdit:

    1. OCLC # (OCLC Masterpiece change to 035 - match point for OCLC merge)

    2. Title – for error resolution, only - will not be merged into OCLC bibs.

    3. 086 - taken from the first (home) column and change to 099 - our SUDOC number

    4. Record #(Biblio) change to 907 - Sierra bib-number match point (needed only for merge & OVERLAY) - match point for Sierra import

    5. 949 - formed in Airtable - (will contain barcode-from Airtable and SUDOC - from Airtable; plus identical location codes, item type codes, etc.) – EXCEPTION: multivolume titles will be adding 949 during OCLC steps.

    6. 590 field for the Project Name

  4. Batch Changes to OCLC record will be:

    1. Change 049 from UUSA to UUSH.

    2. General and very modest edits for the usual obvious errors.

  5. Merge Brief bib into OCLC bibs:

    1. 035 OCLC Masterpiece

    2. 099 SUDOC

    3. 949

    4. 907 (for Overlay)

    5. 590 field for the Project Name.

Undergoing revision, please wait until finished

Detailed Procedures for New Barcodes, Update Sierra, Set holdings: (MERGE Overlay) workflow 1

For government documents which already have a record loaded in Sierra (usually from Marcive GPO), but needed a new barcode, we will use the barcode to make 949 item record field, put the Sierra bib# in a 907 for overlaying, and the SUDOC in a 099.

AirTable Extraction:

  1. Go to AirTable Hearings 9-10:

    1. Sign up for a certain section of SuDoc Y4 numbers

      1. Batch Loader: you

      2. Batch Load Status: In Progress

      3. Fill in Batch Load Start Date and (if needed during the process) Notes.

    2. Go to view: New barcodes, Update Sierra, Set holdings:

      1. Flag any problems that need to returned for more work, checking etc. (incomplete, problem, etc.)

      2. Download CSV file of items by clicking on the down arrow at the top menu, in one of two ways:

        • First filter by SUDOC stem (easily done by adding a filter of Sign up Name is exactly: [your name]--then delete just that filter when done downloading)

        • OR

        • Download entire set and delete other titles after opening file in Excel (probably too bulky to do).

      3. Distinguish the download by changing the CSV name of Imported table-New barcodes, Update Sierra, Set holdings after downloading, something like this: Newbarcodes-Y4V2-HRG101-103.

Excel Column work:

  1. Open Excel app first

    1. Open; from PC; Desktop; All Files;

    2. Then, navigate to the CSV file (such as Newbarcodes-Y4V2-HRG101-103); then choose Delimited; Next; click in Comma box, Next; choose Text, and Finish.

    3. If you haven’t done so already by filtering to your SUDOC stem in Airtable, delete all but your chosen Y4 group rows from the spreadsheet. or copy/paste your chosen titles to another spreadsheet.

  2. Delete all these columns in the spreadsheet:

    • The 3 sign up columns: Sign up Name, Batch Cataloging Status, Start Date

    • Final Source

    • Stem

    • Condition

    • Batch Cataloging Status

    • Record #(Item) - we don’t use this

    • 86 (since we’ll be using the first SuDoc Call Number column)

    • OCLC Masterpiece will NOT be deleted: instead delete ANY other OCLC related numbers, including Field 001, OCLC Final for Melanie, Field 001 checked, etc

    • Sierra MQ barcode

    • Barcode added in inventory - make sure this is already in the MARC 949 - $ subfield column, then delete Barcode added in inventory

    • Volume (if multipart) - should be in another view

    • Editable SuDoc

    • Bib Record (linked) - we already have Record#(Biblo as 907)

    • Count of items on Bib

    The remaining Columns should be:

    • SuDoc Call Number

    • OCLC Masterpiece - this will become our 035 field in the next step

    • Title - just for error checking, if needed

    • Record#(Biblio) – (for overlaying in Sierra)

    • MARC 949 - $ Subfield - first item record

  3. Keep these specific columns and rename:

    • SuDoc Call Number change to 086a

    • Title to 245 - for error checking only

    • ADD a new Column:

      • Insert a column; in header name it: 590a

      • In first column add the text: Congressional Hearings Batch Project 2022

      • Pull down to fill the rest of the column cells.

    • Record #(Biblio) change to 907a

    • MARC 949 - $ Subfield to 949a

    • OCLC Masterpiece - first do these edits:

      1. Insert 2 new columns to the left.

        1. In the second column add an asterisk * to the header cell, and then pull down to fill in the rest of the column.

        2. In the first column add the function:

          • =CONCATENATE(F1,G1) ← with F is asterisk column * G is OCLC Masterpiece

          • click return key:

          • First (header) cell should display *OCLC Masterpice

          • Pull down to populate the rest of the cells in the column with their concatenate functions.

          • Then Copy the entire *OCLC column, and Paste as Values in the same column.

          • Finally, delete * asterisk column and the unadorned OCLC Masterpiece column.

    • Change the new *OCLC Masterpiece column to 035a

  4. Save the Excel New Barcodes file as Text (Tab Delimited)

    • I usually give it a new name, at this point (optional)

MarcEdit Conversion and Edits

  1. Open MarcEdit; then Tools; Delimited Text Translator

    1. Source File: [file name, such as: NewBarcodes-Y4Hearings-Y4-3]

    2. Output File: [new name such as NewbarcodesY4HearingsOverBrief-Y4-3…]

    3. Click Import File (formerly Next)

  2. Manually enter in each column (fields) and map them to MARC fields

    1. Use the Data Snapshot as a guide:

      • Use Select to pick each Data shapshot field

      • In Map To: type MARC tag+1st subfield;

      • Then Indicators;

      • (No terminal punctuation is needed for this project).

      • Then click Add Argument

    2. Field for checking or error resolution in blue:

      • Field 0 086$a Indicators: \\  no terminal punctuation

      • Field 1 099$a Indicators: \\ no terminal punctuation

      • Field 2 245$a    Indicators: 10 no terminal punctuation             

      • Field 3 590$a Indicators: \\ no terminal punctuation

      • Field 4 907$a Indicators: \\ no terminal punctuation

      • Field 5 949$a Indicators: \\ no terminal punctuation

      • Field 6 035a Indicators: \\ no terminal punctuation

    3. Click Add Argument to enter each mapping. The MARC fields will show up in the Arguments preview:

      • you can right-click in the Add Argument box to Delete or Edit if there are any problems

      • you can also select a line in the box, and use the blue circled arrows to move it up and down

    4. Click Finish.

  3. Edit the Brief Records in MarcEdit:

    1. In MarcEdit; open MarcEditor; Open, then navigate to the new MARC mnemonic (.mrk) file

    2. You can doublecheck the mappings by looking over the first mock record in the file to see that the tags and mapping match.

      1.  

  4. Go to Reports; Field Count to make sure the number of records is correct

    1. (records number should be the numbers of the 001, 035, & 245 field count; HOWEVER, the first mock record will be counted: so subtract 1 for the correct number)

    2. Use Edit; Find; Replace to get rid of CSV double-quotes.

      1. Find: " (one double quote)

      2. Replace: (with nothing)

      3. Replace all to clean up the entire file.

      4. Then fix any 500 quoted notes (which were protected back in the spreadsheet by replacing their quotation marks with carets):

      5. Find: ^ (inverted caret)

      6. Replace: " (double quote)

      7. 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.

      8. Then File; Save (it’s a good idea to do a save after each major edit).

    3. Use Edit; Find on each field to check for errors

      1. Check the subfield d’s in the 100 fields which often have problems:

        1. Edit; Find: =100

        2. Edit; Find; Replace

          • Find what: $d$e ← which won’t occur anywhere else in the records.

          • Replace with: ,$e ← [comma]$e

      2. 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 single [space] after each…

        • =245 10$aThe ← add a single [space] after each…

      3. fix any articles found using Edit; Find; Replace:

        1. Find: =245 10$aThe ← add a single [space]

        2. Replace: =245 14$aThe ← add a single [space]

    4. Add our local constant Data:

      1. Field: 590, field text: \\$aThesis Remediation Project.

      2. Field: 655 field text: \7$aAcademic theses$2lcgft

      3. Field: 710 field text: 2\$aUtah State University.

    5. 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.

    6. Then Save the file of cleaned up brief records.

  5. Batch-search Connexion with OCLC numbers:

    1. In Excel and Notepad, create a text file of OCLC numbers for the batch search in OCLC Connexion.

      1. If not already done, copy/paste OCLC numbers to a new column; replace (OCoLC) with * then name the column: OCLC batchsearch.

      2. Select all the OCLC numbers; right-click; choose format cells; & change Category to text.

      3. 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

      4. Note the number of *OCLC rows (records).

  6. In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).

    1. Do a search/return key to make sure there are no records lingering in there;

    2. Delete any lingering records.

  7. Go to Batch; Enter Bibliographic Search Keys; Import.

    1. Navigate to your text file of *OCLC numbers, make sure the Default Index is: None, and click Open.

    2. Always click Don’t Delete, when asked.

    3. The text file should load into the Bibliographic Batch Search Window; click Save; then close window.

    4. In Local File Manager; Set up Authorizations; add Authorization & Password; Save & Login.            

    5. Go to Batch; Process Batch;

      1. make sure the empty Local Save File is still selected (if not, select it now).

      2. Only Online Searches should be clicked.

      3. Click OK;

      4. The OCLC numbers should scroll down the view window, until all have been searched.

      5. 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.

    6. The Local Save File should have received all the records.

      1. Go to Search; Local Save File; then hit Search, leaving the search box blank.

      2. Select every record in the batch save file.

      3. Export; name the export file, something like this: THESES-OCLCbatchsearchOver-year.

  8. Open MarcEdit; click on MARC Tools icon; leave Select Operation as MarcBreaker;

    1. Open (choose All Files): Theses-OCLCbatchsearchOver-year

    2. Save As: Theses-OCLCbatchsearchOVER-year

    3. Leave Default Character Encoding as MARC8

    4. Click Execute; then click on Edit Records:

      1.  

    5. Edit; Find/Replace: =049 \$aUUSA to =049 \$aUUSP

    6. Delete these fields which we will be replacing with our updated versions:

      1. Tools; Add/Delete Fields, fill in the tag in Field; and leave Field Data blank; then click Delete Field:

        1. Field: 100

        2. Field: 245

        3. Field: 260

        4. Field: 264

        5. Field: 300

        6. Field: 502

        7. Field: 520

      2. For the following Fields, we will be deleting certain duplicate versions, using Add/Delete Fields:

        1. Field: 655 Field Data: \7$aAcademic theses.$2lcgft

        2. Field: 655 Field Data: \4$aAcademic theses.

        3. 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

    7. Check that 504 fields have: Includes bibliographical references (and change any incorrect), but don’t add them where they are missing.

      1. Jump to Record to fix incorrect text.

    8. Save for Merge step.

  9. Merge Brief file and OCLC batch-searched file records together.

    1. Go to Tools, MARC Processing Tools, Merge Records, then navigate to the two files, as in this example:

      1. Source file: Theses-OCLCbatchseach-year.mrk    → (OCLC batch-searched/edited records)

      2. Merge File: Theses-mergeOVERLAY-Brief-year.mrk → (brief records with our data)

      3. Save file: Theses-mergedOVERLAY-year.mrk          → (future file of merged records)

      4. Record identifier:  035$a → (Important Matchpoint)

    2. Click Next to begin selecting the fields to merge.

    3. 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

      • 949  automatic item record creation

    4. Then click Next to do the merge.

  10. Batch copy catalog the merged records in MarcEdit:

    1. Reports; Field Count; to check the merge worked (001, 035, & 245 fields should match number of records);

    2. Edit; Find; Find All various fields, search for egregious errors, etc.

    3. Compile cleaned up merged files into MARC format:     ThesesmergedOVERLAYdone-year.mrc

  11. Load file into Sierra and track:

    1. Use the first load table: LOAD/Overlay bibs & create items (.briefbiboverlay), and remember to click on “use review files”;

      1. Do a Test load first; then if the test goes well; Load.

      2. Go to Create Lists; Copy; find file, looking somewhat like this:

        • LOAD: inserted files for ThesesmergedOVERLAYdone-year (#) ← number of bib records loaded in parentheses

      3. Click on a few records and make sure the records loaded properly.

      4. Leave the Load list in Create Lists for later Global Edit in Sierra.

      5. Return to the OVERLAY view in the Thesis Date Pull Airtable & record the Date Loaded in Sierra.

    Report the Load for the Global Update step to the Batch Thesis Cataloger:

    1. Send an email with the subject line: Global Update for Thesis Batch Load.

    2. Give the name of the load in the body of the email, something like this:

      • I have just loaded ThesismergedOVERLAYdone-1926. It is ready to have the item records' call number tags changed from 090 to 099.

      • The item records will be changed from 090 to 099; after the global edit, and the Create Lists file will be removed.

 

Statistics for Merge Overlay Procedures

Report the total number of MARC records (not items) imported into Sierra during this process in the “MARC Batch-Modified” column in the “Statistics” Airtable base.

Done!

 

Detailed Procedures for MERGE No Overlay workflow 

Spreadsheet Manipulations to keep our local information:

    2. Double quotation marks

      1. To separate and protect actual 500 quoted notes from CSV escape symbols; click on the corner of the spreadsheet to select the entire file;

        1. go to Find and Select; then Replace;

        2. Find what: " (double quote) – if there are no double quotes, go on to next step.

        3. Replace with: ^ (inverted caret); Save.

    3. Column work:

      1. Delete: [certain fields…?]

      2. Keep: OCLC w Prefix

      3. Keep all MARC tag columns (note: Excel will have unhelpfully changed 086 to 86 and 99 to 99; keep them anyway)

        1. or keep 035, 086, and 949

      4. Delete: Batch Type

      5. Delete: Date loaded

    4. These columns should remain:

      1. OCLC w Prefix - or not, as the case may be

      2. 99

      3. 100

      4. 245

      6. 949

      7. first Multiple949

      8. second Multiple949

      9. third Multiple949

    5. Save As the Excel Merge file as Text (Tab Delimited); under a new name (such as: Theses-MergeNoover-year)

 

  1. Convert to a file of MARC records using MarcEdit with our AirTable data:

    1. In MarcEdit; Tools; Delimited Text Translator;

      • Source File: Theses-MergeNoover-year

      • Output File: [new name such as Theses-MergeNooverBrief-year]

    2. Click Import File (formerly Next)

      1. 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)

      2. Fields will load automatically in the Arguments window.

      3. Check the Template has loaded the correct mappings in the Arguments window (scrolling with the mouse or arrow keys)

        1. Tags and first subfield:

          1. 035$a           

          2. 099$a                                

          3. 245$a                       

          4. 590$a

          5. 907$a

          6. 949$a

          7. Click Finish.

            •  

    3. Edit the Brief Records in MarcEdit:

      1. Open MarcEditor,

      2. Click on File; Open; then navigate to the new file.

      3. You can doublecheck the mappings by looking over the first mock record in the file to see that the tags and mapping match:

    4. 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)

      1. (However: that first mock record will also be counted; so subtract 1 when looking for the correct number of records)

      2.  

    5. Edit the Brief record (.mrk) file.

      1. Use Edit; Find; Replace to get rid of CSV double-quotes (used to protect commas in the 100’s, etc.).

      2. Find: " (one double quote)

      3. Replace: (with nothing)

        •  

      4. Replace all to clean up the entire file.

      5. Then replace any 500 quoted notes, if any (which would be protected earlier by replacing their quotation marks with carets):

      6. Find: ^ (inverted caret)

      7. Replace: " (double quote)

      8. Replace all.

      9. 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.

      10. Then File; Save (it’s a good idea to do a save after each major edit).

    6. Check the subfield d’s in the 100 fields which often have problems:

      1. Edit; Find: =100

        •  

           

      2. 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

        •  

    7. If there are any authors with birthdates in the 100s that are missing a d-subfield, do an Edit Field Data to add them:

      1. Tools; Edit Field Data:

      2. Field: 100

      3. Find: 1 (type a single space, followed by a 1)

      4. Replace: $d1 (which will take out the space, add the subfield d & reinsert the 1)

      5. then if there are incorrect commas at the end of a year [dash], do another Edit Field Data:

      6. Field: 100

      7. Find: -,$e (a dash, comma, $e)

      8. Replace: -$e (removes the comma)

    8. 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)

          •  

    9. Then add our local constant data:

      1. Tools; Add/Delete Field; copy/paste the values below in blue; then Add Field:

      2. Add 590, field text: \\$aThesis Remediation Project.

      3. Add 655 field text: \7$aAcademic theses.$2lcgft

      4. Add 710 field text: 2\$aUtah State University.

      5. (Note that each field will also be added to the first mock record).

    10. Finally, use Edit; Find on important fields to do quick check for any more egregious errors (but don’t get caught in the weeds).

    11. Remember to Save the file of cleaned up brief records for the Merging step.

  2. Batch-search Connexion with OCLC numbers:

    1. In Excel and Notepad, create a text file of OCLC numbers for the batch search in OCLC Connexion.

      1. Open Excel and go back to the spreadsheet: Thesis-MERGEnoover-year

      2. Copy/insert OCLC w Prefix into a new column; Replace (OCoLC) with * (asterix).

      3. Select all the OCLC numbers; right-click; choose format cells; & change Category to text.

      4. 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

      5. Note the number of *OCLC rows (records).

    2. In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).

      1. 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;

      2. Delete any lingering records.

    3. Go to Batch; Enter Bibliographic Search Keys; Import.

      1. Navigate to your text file of *OCLC numbers, make sure the Default Index is: None, and click Open.

      2. I always click Don’t Delete, when asked (to allow for error checking and redoing).

      3. The text file should load into the Bibliographic Batch Search Window; click Save; then close window.

    4. In File; Local File Manager; Set up Authorizations; add Authorization & Password; Save & Login.            

    5. Go to Batch; Process Batch; make sure the empty Local Save File is still selected (if not, select it again).

      1. Only Online Searches should be selected; then click OK.

      2. The OCLC numbers should scroll down the view window, until all have been searched.

      3. 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.

    6. The Local Save File should have received all the records.

      1. Go to Cataloging; Search; Local Save File; then hit OK, leaving the search box blank.

      2. 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).

      3. Action; Export; name the export file, something like this: THESES-OCLCbatchsearch-year.

    7. Open MarcEdit; click on MARC Tools icon; leave Select Operation as MarcBreaker;

      1. Open (under All Files): THESES-OCLCbatchsearch-year

      2. Save As: Theses-OCLCbatchsearch-year

      3. Leave Default Character Encoding as MARC8

      4. Click Execute; then click on Edit Records:

        •  

    8. Edit; Find/Replace: =049 \\$aUUSA to =049 \\$aUUSP; Replace All.

    9. Delete these fields which we will be replacing with our updated versions:

      1. Edit Records and Tools; Add/Delete Fields, fill in the tag in Field; and leave Field Data blank; then click Delete Field:

        1. Field: 100

        2. Field: 245

        3. Field: 260

        4. Field: 264

        5. Field: 300

        6. Field: 502

        7. Field: 520

      2. For the following Fields, we will be deleting certain duplicate versions, using Add/Delete Fields:

        1. Field: 655 Field Data: \7$aAcademic theses.$2lcgft

        2. Field: 655 Field Data: \4$aAcademic theses.

        3. 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

    10. Find; Find All =504 to see that all them have the form: Includes bibliographical references;

      • Jump to Record to fix any that are incorrect.

    11. Do an Edit; Find; Find All on: =008 to look for questionable dates

      1. These will have a “q” and two dates in the 008 instead of just one.

        1. Jump to Record:

          •  

        2. The field in question will be highlighted, so do an Edit; Replace

        3. Copy/paste and very carefully change only the effected portions of the 008: qyearyear with syear\\\\

          1. 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)

        4. 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.

        5. Save edited OCLC file for Merging step (and Close).

  3. Merge Brief file and OCLC batch-searched file records together:

    1. Go to Tools, MARC Processing Tools, Merge Records, then navigate to the two files, in this general form:

      1. Source file: Theses-OCLCbatchseach-year.mrk    → (OCLC batch-searched/edited records)

      2. Merge File: Theses-MERGENooverBrief-year.mrk → (brief records with our data)

      3. Save file: Theses-MERGED-noover-year.mrk          → (future file of merged records)

      4. Record identifier:  035$a → (Important Matchpoint)

    2. Click Next:

      1. Use the saved Merge Settings template: click on Settings; Load Settings;

      2. Then navigate to: Thesis-MERGEnoover-MergeSettings2

      3. 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.

    3. 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

    4. Then click Next to do the merge.

  4. Batch copy catalog the merged records in MarcEdit (click on MarcEditor icon; Open and navigate to newly merged file):

    1. Do a Reports; Field Count; to check the merge worked; check that field 001, 035, 245 matches correct number of bibs;

    2. Edit/Find various important fields: 100, 245, 502, etc.,

    3. Check for duplicated fields, such as 300 fields; and delete one version.

    4. Edit/Find all =949 fields and ensure they match the number of barcoded items expected

    5. Scan through a few bibs, to look for egregious errors, etc. -- but don’t get lost in the weeds.

      1. Check 520’s for text that is cut off. Where found, add an ellipsis (…) to the end of the field.

    6. Sign your work!

      1. Add/Delete Fields; Field: 909 Field Data: \\$aMelanie Shaw$b2022-05-04$eMODIFIED$dCMS STATS

    7. Save.

    8. Validate the file by going to Reports; MARCValidator or using Control M

      1. Common errors include:

        1. Dollar signs in the 520 → Fix by replacing dollar signs with a {dollar}

    9. Save file again.

    10. File; Compile into MARC format with a new name: ThesesMERGED-noverDONE-year.mrc

  5. Load file into Sierra and track:

    1. 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.

      1. 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)

      2. if the test goes through with all the records; Load.

      3. 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.

      4. Double-click on the new list and check a few bibs to make sure the records loaded properly.

      5. Leave your Load list in Create Lists for later Global Edit to the item records in Sierra.

      6. Return to the MERGE view in the Thesis Date Pull Airtable & record the Date Loaded in Sierra

      7. Go to the Sign Up Tab, change In Progress to Completed and fill in the Batch Load End Date.

    2. Report the Load for the Global Update step to the Batch Thesis Cataloger:

      1. Send an email with the subject line: Global Update for Thesis Batch Load.

      2. Give the name of the load in the body of the email, something like this:

        • I have just loaded ThesisMERGEDnoover-1926. It is ready to have the item records' call number tags changed from 090 to 099.

        • The item records will be changed from 090 to 099; after the global edit, and the Create Lists file will be removed.

Statistics for Merge (with no overlay) Procedures

Report the total number of MARC records (not items) imported into Sierra during this process in the “MARC Batch-Modified” column in the “Statistics” Airtable base. If any additional item records were created (beyond the initial records), record that number in the “Sierra-Items Added”.

Global Update for Thesis Batch Load link: Batch Work: Sierra Global Update: Thesis Batch work example (CMS staff only)

 Done!

 

Batch ORIGINAL Processes

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 – UUSH

  • 086

  • 33x fields

 

Detailed Procedures for Original workflow: 

UNDERGOING REVISION: please wait before using

  1. Spreadsheet manipulations:

    1. Go to AirTable

    2. Click on the Sign Up tab, Batch Loading view; and sign up for a thesis year.

      1. Batch Loader: you

      2. Batch Load Status: In Progress

      3. Fill in Batch Load Start Date and (if needed during the process) Notes.

    3. Go to Title List and the view: Original Batch Work

    4. Choose just one year at a time to work on (if you don’t, individualized editing will be necessary later on).

    5. Check all essential fields are filled:

      1. Flag any problems (incomplete, problem, etc.)

      2. Check for missing barcodes in 949s (do not select)

      3. Download CSV file of items within a year, or download entire view and delete rows of other years later.

    6. 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).

    7. Delete all but your chosen year in the spreadsheet, if not done before.

    8. Remove any columns no longer needed for the MARC record:

      1. Thesis (year-#-author), Batch Cataloging in Process, OCLC # (which will be empty), Batch Type, Date Loaded in Sierra, and Status

    9. And keep these columns (all MARC fields):

      • 099

      • 100

      • 245

      • 264

      • 300

      • 500’s

      • 502

      • 690’s

      • all 949’s (including empties)

    10. Save the Excel Original file as Text (Tab Delimited);

      1. new name: Theses-Original-year.

    11. Double quotation marks protection:

      1. To separate and protect actual quoted notes from CSV escape symbols:

        1. Click on the corner of the spreadsheet to select the entire file;

        2. Go to Find and Select; then Replace;

        3. Find what: " (double quote)

        4. Replace with: ^ (inverted caret).

        5. Save file.

  2. Convert to a file of MARC records using MarcEdit with our AirTable data:

    1. Open MarcEdit: Tools; Delimited Text Translator;

      • Source File: Theses-Original-year

      • Output File: [new name such as Theses-Original-MARC-year.mrk

      •  

  3. Fixed Fields (Leader)

    1. In the Options portion of the Delimited Text Translator, click on the blue Edit LDR/008:

      1. For the Leader, make sure the dropdown says: Book.

      3. While still in the LDR, change nam to ntm and Ia to Ki:

        • 00000nam 2200000Ia 45e0

        • 00000ntm 2200000Ki 45e0

          • t = manuscript monograph

          • K = OCLC minimal record (maybe should change to 7, the MARC21 equivalent as OCLC will be changing to that soon)

          • a = AACR2 to i=ISBD (used in combination with 040 rda code)

      4. 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.

    2. Click Next to begin the task of selecting each column/field and mapping it to MARC.

  4. Select or type: MARC tag; 1st subfield; and Indicators; no end punctuation needed in this project. 

    1. 099$a            Indicators: \\                      no terminal punctuation

    2. 100$a            Indicators: 1\                    no terminal punctuation              

    3. 245$a            Indicators: 10                     no terminal punctuation

    4. 264$c             Indicators: \0                     no terminal punctuation              

    5. 300$a            Indicators: \\                      no terminal punctuation

    6. 500$a            Indicators: \\                      no terminal punctuation

    7. 500$a            Indicators: \\                      no terminal punctuation

    8. 502$b            Indicators: \\                      no terminal punctuation

    9. 690$a            Indicators: \\                      no terminal punctuation

    10. 690$a            Indicators: \\                      no terminal punctuation

    11. 949$a            Indicators: \\                      no terminal punctuation no terminal punctuation

  5. Click Add Argument to enter each mapping.

    1. 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.

    2. Finish.

  6. In MarcEdit; open MarcEditor; then find the new MARC mnemonic (.mrk) file

  7. 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)

  8. Use Reports; MARCvalidator to check records have been entered correctly during the conversion process:

    1. If errors are found; fix them, if it isn’t major, otherwise, go back and run the conversion again.

  9. 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):

      1. Find: ^ (caret)

      2. Replace " (double quotes)

      3. Replace all.

  10. Use Edit; Find; Find All on each field to check for errors; then add local constant data:

    1. Add 040, field text: \\$aUUS$beng$erda$cUUS

    2. Add 049, field text: \\$aUUSP

    3. Add 590 field text: \\$aThesis Remediation Project.

    4. Add 655 field text: \7$aAcademic theses$2lcgft

    5. Add 710 field text: 2\$aUtah State University.

    6. Scroll down the MARC records and check for GAPS after adding all those fields (which will affect all records):

    8. Once the the adds have gone through without major hiccups, run Reports; MARCValidator, to make sure all is well.

    9. Next, add the 33x RDA format fields, but instead of adding them individually…

      • Add 336

      • Add 337

      • Add 338

    10. Use the RDA Helper in Tools with everything except Add 336, 337, 338 deselected:

      1. It’s a great time saver.

    11. Change ^ (caret) back into “ (double quotes):

      1. Find: ^ (caret)

      2. Replace " (double quotes)

      3. Replace all.

    12. Edit fixed field 008 in first record, if 008 fields come through incorrectly:

      1. This will only edit one record at a time and for that record all the other codes will need to be typed in.

      2. Place cursor at the beginning of the first record, the click on Edit; Insert/Edit 008; and choose Book:

    13. Edit all the values for these areas (leave the backslashes or zeroes in place for all others):

      1. Type: t

      2. Ills: a

      3. Cont: bm

      4. Srce: d

      5. Lang: eng

      6. Ctry: xx

      7. Dates: year of production

      8. Click OK

      9. 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]

      10. 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.

      11. Also, recheck edited fields by validating: Edit; MARCValidator and fix 008 errors.

    14. Finally, delete the first mock record: Edit; Delete Records; By Range; and type in 1; then click OK:

      • And the mock record will disappear.

  11. Scan through record for egregious errors in any other Fields, especially for descriptive/searchable tags; but try not get lost in the weeds.

  12. Save the file of cleaned up records.

  13. Compile into MARC: ThesesOriginalDONE-year.mrc

  14. Import new original theses records into OCLC:

    1. In OCLC Connexion:

      1. First time: Set up the File; Local File Manager to create a new Local Save File path. If already created, skip this step.\

        1. Configure the path; Close.

    2. Each time: If the File Path has already been set up:

      1. Any time you are ready to import new records into OCLC make sure there are no earlier records left in the save file:

      2. F3 and return without inputting a search; then delete any bibs found.

    3. In Connexion, but without logging in:

      1. File; Import Records; navigate to record file in MARC21/MARC8.

      2. Click on Import to Local Save File Bibliographic: should show the new File Path for the save file.

      3. Click OK.

      4. 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 (including as a PDF), if a record of the results will be needed.

    4. Bring up the Connexion Save file with F3 and Return key without entering any search terms:

      1. All of the records should be there in the Save file list and can be marked and/or previewed.

      2. Delete the first “dummy” record that was created by Marcedit (the one containing empty fields); ignore if dummy record is not there.

    5. Mark entire set of records in save file with Shift-Control.

      1. Then Edit; Validate entire file:

      2. 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.

      3. 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:

    6. Log into Connexion and mark entire set of records,

      1. Then: Action; Holdings; Produce/Update Holdings

      2. Each record should be assigned a new OCLC #

    7. Mark and Export entire file to desktop.

    8. Name the file of new records something memorable and easy to pick out of a list: ThesesOriginalDONE-year

  15. Load file into Sierra:

  16. Use the first load table; and remember to click on “use review files”; do a test load first; then if the test goes well; Load.

    1. Go to Create Lists; Copy; find file, looking somewhat like this:

      1. LOAD: inserted files for ThesesOriginal-MARC-year (6)

    2. Click on a few records and make sure the records loaded properly.

    3. Leave the Load list in Create Lists for later Global Edit in Sierra.

    4. Return to the ORIGINAL view in the Thesis Date Pull Airtable & record the Date Loaded in Sierra.

  17. Report the Load for Global Update step in Sierra, or, if just a handful of records, do the edits manually in the Createlist file:

    1. Item Record: Call Number code from 090 to 099.

 Done!

 

Statistics for Procedure 4

Because there is no need to pull items physically for Procedure 4 and the number of items barcoded and reviewed has already been recorded, there is no need to record statistics in the “Barcoding Tracking, Issues, and Stats” Airtable base.   Only record statistics in the main “Statistics” Airtable base.

  1. For every record in either of the Merge processes (with or without overlay), record the number in the “MARC Batch - Modified” column

  2. For every record in the Original process, record the number in the “MARC Batch-Created records” column

  • No labels