Congressional Hearing Merge Batch Procedures

Owned by Liz Woolcott
Last updated: Feb 06, 2023 by Jenny Jensen
23 min read

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:

Congressional Hearings Merge Processes (New Barcodes, Update Sierra, Set Holdings)

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 at some point before or after we bring in our Merged Batchload records.

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

    4. Various preprocessing tasks 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, add collection name field, and OCLC batchsearch column for later.

  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. 590 field for the Project Name

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

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

  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 bibs with our local data into OCLC bibs:

    1. 035 OCLC Masterpiece for (Update New Barcodes, Update Sierra, Update Holdings) workflow

      • or 035 OCLC Final for Melanie (New Barcodes, Ready for New Cataloging) workflow

    2. 099 SUDOC

    3. 590 field for the Project Name

    4. 949

    5. 907 (only for New Barcodes, Update Sierra, Update Holdings).

 

New Barcodes, Update Sierra, Set holdings

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

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/SUDOC# to make 949 item record field, put the Sierra bib# in a 907 for overlaying, and the official 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. Download CSV file of items by clicking on the 3 dots at the top right, in one of two ways:

        • Click on the right of the view title and Duplicate the New Barcodes view; then filter your new personal view (easily done by adding a filter of Sign up Name is exactly: [your name]--then download by clicking the three dots; Download CSV,

      2. Change your downloaded file by changing the CSV name of Imported table-New barcodes, Update Sierra, Set holdings, to something like this: Newbarcodes-Y4V2-HRG101-103, or something shorter, whatever is easier to remember between work sessions.

Excel Column work:

  1. Open Excel first:

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

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

  2. Next, delete all these columns in the spreadsheet:

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

    • Final Source

    • Stem

    • Condition

    • Cataloging Status

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

    • Delete OCLC related numbers, but leave OCLC Masterpiece) delete Field 001, OCLC Final for Melanie, Field 001 checked,

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

    • Sierra Barcode (mq type)

    • Barcode added in inventory - make sure the barcode made it into the MARC 949 - $ subfield column, then delete Barcode added in inventory

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

    • Editable SuDoc - not needed

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

    • Count of items on Bib - not needed

The remaining Columns should be:

  • SuDoc Call Number - first/home column.

  • Title - just for error checking, if needed

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

  • MARC 949 - $ Subfield - the 949 column with the $ (double-dagger) delimiter.

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

  •  

  • Rename (and edit) the remaining columns:

    1. SuDoc Call Number change to 099$a

    2. Title to 245$a - this will be for error tracing;/checking, if necessary, it will not be used in the record

    3. Record #(Biblio) change to 907$a

    4. Then, Add a 590 column for the project:

      1. right click/Insert a new column

      2. Give it a name: 590$a

      3. Copy/Paste this text into the second cell: Y4 Retrocataloging Project 2022.

      4. Pull down the cell to fill the rest

    5. Make sure you copied the period at the end of the Collection title (if you didn’t, Excel adds up the years):

    6. MARC 949 - $ Subfield rename to 949$a

    7. For OCLC Masterpiece - Create 2 new OCLC columns:

    8. The 035 column:

      1. Use 2 columns to the left

      2. In the second column type the (OCoLC) prefix into the cell, and then pull down the corner to fill in the rest of the column.

      3. then in the first OCLC column add the function:

        1. =CONCATENATE(G1,F1) ←G equals (OCoLC) and F = OCLC Masterpiece

        2. click the Enter key

        3. First (header) cell should display (OCoLC)OCLC Masterpiece

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

        5. Then Copy the entire (OCoLC)OCLC Masterpiece column, and Paste as Values in the same column.

        6. Finally, delete the (OCoLC) prefix column and the old OCLC Masterpiece column.

        7. Copy the column, then, Insert as new column at the beginning of the spreadsheet

        8. Rename this beginning column to: 035$a

    9. For the second OCLC column:

      • Go back to the (OCoLC)OCLC Masterpiece column, then use Find & Select; Replace to edit to OCLC batch-searching format:

        • Find: (OCoLC) ← no spaces

        • Replace: * ← asterisk

        • Replace All

        • Rename column: OCLC batchsearch and leave it at the end of the spreadsheet:

  • Save the Excel New Barcodes spreadsheet as Text (Tab Delimited)

    • I usually give it a new name, here just in case I’m interrupted (but that is optional):

      • NewBarcodes-Y4T68T68107-51-56clean

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…brief]

    3. Click Import File (formerly Next)

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

    1. Using Data Snapshot as a guide

      • Click on Select to pick each Data shapshot field, then type in the Field as displayed.

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

      • Then Indicators are usually the default: \\

        • → except for 245$a which is: 10

      • No terminal punctuation is needed for this project, so leave blank.

      • Then click Add Argument

    2. The field to MARC arguments:

      • Field 0 035$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

      • Do not use the OCLC batchsearch column

    3. The MARC fields will show up in the Arguments preview box

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

      1. (Edit will bring up the data entered in Select, Map To, Indicator boxes to be changed as needed)

      2. you can also select one argument (or more) and use the blue circled arrows to move it up or down

    5. If all looks good, click Finish.

  3. Edit the Brief Records in MarcEdit

    • In MarcEdit; open MarcEditor; then File; Open, then navigate to the new MARC mnemonic (.mrc) file

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

  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 actual number of records)

    2. Use Edit; Find; Replace to get rid of Excel’s double-quotes.

      • Find: " (one double quote)

      • Replace: (with nothing)

      • Replace all to clean up the entire file.

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

  5. [If we were actually using the 245 fields as anything except error resolution, we’d need to check for incorrect indicators, but since we don’t use them, we’ll leave them alone.]

  6. Do some quick Find’s to check for egregious errors in our local data.

    1. Edit; Find; Find: =035; Find All:

    2. Find: =099

      • Find: =907:

      • Find: =907

        • Edit; Replace

        • Find: =907 \$ab

        • Replace: =907 \$a.b

        • Replace All

      • Find: =949

      • If you find =949 \\$a949 Y 4.T 68/2:107-65$i39060020971312$k-$lagov$t6 ---which has an 949 in the subfield a - whoops

        • You will need to do another Find; Replace

          • Find: =949 \\$a949 Y

          • Replace: =949 \\$aY

          • Replace All

      • Then scroll down the records to look for any egregious errors in our locally generated fields (ignore LDR, 008, & 245), making sure they all line up correctly, but don’t get lost in the weeds.

    3. Then Save the file of edited brief records: I usually assign a new name to show it has been edited, in case of interruptions.

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. In the edited spreadsheet, 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, like: OCLCnosNewBarY4T68107

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

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

    1. Do a Local Save file search (F3)/enter key to make sure there are no records lingering in there;

    2. If something is found, 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 (because then it has to be recreated, if there is a problem).

    3. The text file should load into the Bibliographic Batch Search Window; click Save; but don’t close the window.

    4. Still in the same Batch window, click Local File Manager; Authorization; add Authorization & Password; click OK to login, make sure you’re still pointed at the right local save file, Close the window(s).        

    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--make sure nothing else is selected.

      3. Click OK;

      4. The OCLC numbers should scroll down the search 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 titles in the Excel spreadsheet.

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

      1. Okay, looks like the old method of F3/enter key, then Selecting the entire save file and Exporting, doesn’t work anymore, so if nothing happens: use this method:

        1. Go to Batch; Process Batch; Click Export; then hit OK;

        2. Name the export file, something like this: OCLCnewbarY4T682107to49

        3. the batch window scroll down, then bring up a report of all exported titles, plus their save #s.

          • Done.

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

  1. Open (choose All Files): OCLCnewbarY4T682107to49.dat

    1. Save As: OCLCnewbarY4T682107to49 type: (.mrk)

    2. Leave Default Character Encoding as MARC8

    4. Click Execute; then click on Edit Records:

    5. Edit; Replace

      • Find =049 \$aUUSA

      • Replace: =049 \$aUUSH

      • Replace All

      • should match number of titles

    6. Save (.mrk) for the MarcEdit Merge step.

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: OCLCnewbarY4T682107to49.mrk  → (OCLC batch-searched/edited records)

    2. Merge File: NewBarcodes-Y4T68-2-107to49brief2.mrk → (brief records with our data)

    3. Save file: NewBarcodes-Y4T68-2-107to49merged.mrk          → (future file of merged records)

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

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

    6. Select/type each field to be merged into the OCLC batch-searched file; click the Green arrow to select each:

      • 099 Our SuDoc number

      • 590 Project name

      • 907  Sierra bib-number for overlay

      • 949  auto item record creation

    7. Then click Next to do the merge.

  2. Check the merged records in MarcEdit and do minor edits:

    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. Tools; Add/Delete Fields

      • Field: 856 Field Data: [leave blank]

      • Delete All

      • Most other fields we don’t like will be deleted during export by the rules in the particular Load table we will be using

    4. Tools; Add/Delete Fields

      • Field: 909 Field Data: \\$aFirstname Lastname$b[YYYY-MM-DD]$eMODIFIED$dCMS or GOV INFO STATS$zY4 Hearings Project

      • Add Field

  3. Compile cleaned up merged files into MARC format (.mrc) and change the name: OCLCnewbarY42T68107to49-finished.mrc

Load file into Sierra and track:

Data Exchange

  • Get PC: Navigate to file

    • lfts

    • Prep - make sure number in Prep matches number of records

  • Use first Overlay load table: LOAD/Overlay bibs & create items (.briefbiboverlay)

    • Checkmark: Use Review Files

    • Test, then if all looks good, Load

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

        • Load: Overlaid records for OCLCnewbarY42T68107to49.mrc (b) (10-12-2022)

      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

Global Update options

Two possibilities for this difficult step:

  • We could report our Load for the Global Update step to the Sierra Systems librarian:

    • Send an email with the subject line: Global Update for Y4 retrocataloging project

    • Give the name of the load in the body of the email, looking something like this:

      • I have just loaded Load: Overlaid records for OCLCnewbarY42T68107to49.mrc (b) (10-12-2022). It is ready to have the item records' call number tags changed from 090 to 086

      • The item records will be changed from 090 to 086; after the global edit

      • you should also change the Bib Mat Type from a to n

    • Once they are finished, they will either delete the Create Lists file or ask you to delete it

  • Or, if they wish CMS to do the Global edit, we would use these instructions:

    • Remember your file name in Create Lists LOAD: inserted files for OCLCY411111etc. of loaded bibliographic records.

    • Select a Clear UNUSED Create Lists line - do not use someone else's or your own bib load file

    • Then, make a Create Lists Review file of Item records for the same bib load

      • Search Records

      • Change Store Record Type to Item: i

      • Select Review in dropdown

      • Select the Review file Name (that’s your bibliographic load)

      • At the top, type in a name for the new item list, such as: OCLCnewbarY4etc_30items

    • Click Search to Create the review file.

    • The number of item records will be higher than expected (because of the MQ items)

    •  

  • Item Global Edit steps - use extreme caution

    • In FUNCTION: chose Global Update

    • Select record type = Item

      • Remember to deselect Bibliographic

    • Find your Review file name in the Review file dropdown - be careful not to select someone else’s file

      • It is extremely easy to select the wrong file - be careful

    • Click Search:

      • which will bring in the file:

      •  

        • Select the item records (click first entry; Shift/Ctrl, click last true-barcode entry)

        • Click on Command input; Add

          • Change Variable Length Field should be selected

    • UNCHECK “Use displayed field”

      •  Enter values:

      •  Find: Field group tag c

      • MARC tag 090

      • Replace: Field group tag leave as <no change>

      • MARC tag 086

      • Click OK

    • Click on Preview (this is an extremely important check point)

      • Check all the way down the list

      • Is it making the correct change? Is it corrupting something?

      • Go all the way down to the bottom!

      • Number should match the true barcode items

      • If all looks well, then click Process:

        • Are you sure?? – if so click Yes – be very sure

        • Check the Total # of Changes

        • Alrighty! Go back to Create Lists and see what it did by checking a few records in the item records list.

        • If you messed up, go back and fix it.

        • Otherwise, congratulations! You have finished all the finicky steps correctly.

  • At some later time, we will ask for all the redundant MQ barcode item records to be removed.

  • Finally, use Global update to fix the Sierra local Material Type in bibliographic records

    • You will be using the original LOAD for the bibliographic records to do so.

Set Holdings in OCLC for the load:

  1. Batch; Enter Bibliographic Search Keys

    1. click on Local File Manager

    2. click on Authorization; then sign in with number and password

    3. Close Local File Manager window; click Save, then Close.

  2. Now go to Batch; Holdings by OCLC Number; and skipping the header, Copy & Paste all *OCLC numbers (asterisk plus OCLC number) into the text box to the left

  3. Select Update Holdings only

  4. Click OK

  5. A Batch Holdings Report will open with a list of the records.

  6. Scroll down to the end of the report to check for errors:

    • Usually these are records which already have our holdings set and need no further action.

    • If any show “Transaction failed” copy the OCLC Control # and search it individually to see what can be done. Usually its holdings can be manually updated.

    • If there are more than one error on the report (very rare), copy all their OCLC #s to a text file, and go through each one to manually update holdings in OCLC.

 Track progress and record stats

  • In the Hearings Airtable base: Change Batch Cataloging Status cells to both Finished/Imported in Sierra. This will move them to the FINISHED BATCH PROCESSING view, so, may be hard to fill down.

Statistics

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

Done! Done. done.

 

New Barcodes, Ready for New Cataloging

2-Detailed Procedures for Barcoded, Ready for New Cataloging: (Merge, NO overlay) workflow 

UNDERGOING REVISION: use with caution

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: Barcoded, Ready for New Cataloging:

      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:

        • Duplicate the view; then add another of Sign up Name is exactly: [your name]--then click on the dots; Download CSV

        • 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-Barcoded, Ready for New Cataloging after downloading, something like this: NewCatalogingY4V2HRG101-103 or whatever is useful for you to use.

Excel Column work:

  1. Open Excel app first

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

    2. Then, navigate to the CSV file (such as NewcatalogingY4V2HRG101-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

    • 110 and Date

    • Stem

    • Condition

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

    • Record #(Biblio) - we’re not overlaying

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

    • OCLC Final for Melanie DON’T delete: instead delete ANY other OCLCs, if any

    • Condition

    • Cataloging Status

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

    • 008 Date two, 300, 336, 338, Sierra Barcode, 533, 008 Date one - which will mostly be empty

    • Bib Record (linked)

    • Count of items on Bib and columns after it

    The remaining Columns should be:

    • SuDoc Call Number

    • OCLC final for Melanie - this will become our 035 field in the next step

    • Title - just for error checking, if needed

    • MARC 949 - $ Subfield - to create the item record

  3. Rename (and edit, as needed) the remaining columns:

    • SuDoc Call Number change to 099$a

    • Title to 245$a - this will be for error checking only

    • Then, Add a 590 column for the project:

      • right click/Insert a new column

      • Give it a name: 590$a

      • Copy/Paste this text into the second cell: Y4 Retrocataloging Project 2022.

      • Pull down the cell to fill the rest

      • Make sure you have a period at the end of the Collection title, or Excel will add up the years:

  • For OCLC Final for Melanie - Create two new OCLC columns for two different uses:

    • The 035 column:

      • Insert 2 new columns to the left

      • In the second column add the (OCoLC) prefix to the header cell, and then pull down to fill in the rest of the column.

      • In the first column add the function:

        • =CONCATENATE(E1,F1) ←E equals (OCoLC) and F = OCLC Final

        • click the Enter key

        • First (header) cell should display (OCoLC)OCLC Masterpiece

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

        • Then Copy the entire (OCoLC)OCLC Masterpiece column, and Paste as Values in the same column.

        • Finally, delete the (OCoLC) prefix column and the old OCLC Masterpiece column.

        • Copy the column, then, Insert as new column at the beginning of the spreadsheet

        • Rename: 035$a

    • For the next OCLC batchsearch column:

      • Go back to the (OCoLC)OCLC Masterpiece column, then use Find & Select; Replace to edit to OCLC batch-searching format:

        • Find: (OCoLC) ← no spaces

        • Replace: * ← asterisk

        • Replace All

        • Rename column: OCLC batchsearch

        • then erase the left over columns: (OCoLC) and OCLC final for Melanie

  • MARC 949 - $ Subfield rename to 949$a

  1. Save your Excel NewCatalogingY4… file as Text (Tab Delimited)

    • I usually give it a new name, at this point (but that is 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 NewbarcodesY4HearingsNewBrief-Y4-3…brief]

    3. Click Import File (formerly Next)

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

    1. Using Data Snapshot as a guide

      • Use Select to pick each Data shapshot field

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

      • Then Indicators are usually the default: \\

        • – except for 245$a which is: 10

      • No terminal punctuation is needed for this project, so leave blank.

      • Then click Add Argument

    2. The field to MARC arguments:

      • Field 0 035$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 949$a Indicators: \\ no terminal punctuation

      • Do not use the OCLC batchsearch column

    3. you can also select one argument (or more) and use the blue circled arrows to move it up or down

    4. If all looks good, click Finish.

  3. Edit the Brief Records in MarcEdit

    1. In MarcEdit; open MarcEditor; then File; Open, then navigate to the new MARC mnemonic (.mrc) 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.

    3. Use Edit; Find; Replace to get rid of Excel’s double-quotes.

      1. Find: " (one double quote)

      2. Replace: (with nothing)

      3. Replace all to clean up the entire file.

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

    5. Do some quick Find’s to check for egregious errors in our local data and fix them if you find any:

      1. Edit; Find; Find All

        • =035

        • =099

        • =590

        • =949

          • Whoops. Looks like we still have the double 949 error.

          • Do an Edit; Find/Replace (and copy the first 949 up to the Y4; then take out the extra 949):

            • =949 \\$a949 Y 4

            • =949 \\$aY 4

    6. Then scroll down the records to look for egregious errors in our locally generated fields (ignore LDR, 008, & 245), making sure they all line up correctly, but don’t get lost in the weeds.

  4. Then Save the file of edited brief records.

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. In the edited spreadsheet, 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, like: OCLCnosNewCatY4T68107

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

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

    1. Do a Local Save file search (F3)/enter key to make sure there are no records lingering in there;

    2. If something is found, 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 (because then it has to be recreated, if there is a problem).

    3. The text file should load into the Bibliographic Batch Search Window; click Save; but don’t close the window.

    4. Still in the same Batch window, click Local File Manager; Authorization; add Authorization & Password; click OK to login, make sure you’re still pointed at the right local save file, Close the window(s).        

    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--make sure nothing else is selected.

      3. Click OK;

      4. The OCLC numbers should scroll down the search 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 titles in the Excel spreadsheet.

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

      1. Okay, looks like the old method of F3/enter key, then Selecting the entire save file and Exporting, doesn’t work anymore, so if nothing happens: use this method:

        1. Go to Batch; Process Batch; Click Export; then hit OK;

        2. Name the export file, something like this: OCLCnewcatY4T682107to49

        3. the batch window scroll down, then bring up a report of all exported titles, plus their save #s.

          • Done.

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

  1. Open (choose All Files): OCLCnewcatY4T682107to49.dat

    1. Save As: OCLCnewcatY4T682107to49 type: (.mrk)

    2. Leave Default Character Encoding as MARC8

    3. Click Execute; then click on Edit Records:

    4. Edit; Replace

      • Find =049 \$aUUSA

      • Replace: =049 \$aUUSH

      • Replace All

      • (results should match number of titles)

    5. Save for Merge step.

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: OCLCnewcatY4T682107bib.mrk  → (OCLC batch-searched/edited records)

    2. Merge File: NewCatlogingY4T682107brief.mrk → (brief records with our data)

    3. Save file: OCLCnewcatY4T682107merged.mrk          → (future file of merged records)

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

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

    6. Select/type each field to be merged into the OCLC batch-searched file; click the Green arrow to select each:

      • 099 Our SuDoc number

      • 590 Project name

      • 949  auto item record creation

    7. Then click Next to do the merge.

  2. Click on MarcEditor; open new file and check the merged records and do minor edits:

    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. Tools; Add/Delete Fields:

      • Field: 856 Field Data: [leave blank]

      • Delete All

    4. Tools; Add/Delete Fields

      • Field: 909 Field Data: \\$aFirstname Lastname$bYYYY-MM-DD$eMODIFIED$dCMS STATS or GOV INFO STATS$zY4 Hearings Project

      • Add Field

    5. Compile cleaned up merged files into MARC format: OCLCnewcatY42T68107done.mrc

Load file into Sierra and track

Data Exchange

  • Get PC: Navigate to file

    • lfts

    • Prep - make sure number in Prep matches number of records

  • Use first Overlay load table: LOAD/Overlay bibs & create items (.briefbiboverlay)

    • Checkmark: Use Review Files

    • Test, then if all looks good, Load

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

        • Load: Overlaid records for OCLCnewbarY42T68107done.mrc (b) (10-12-2022)

      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.

  • At some point a Global edit should be done to change 090 item call number to 086

    • (Or go ahead and change item record call number fields from 090 to 086 manually).

Set Holdings in OCLC for the load

Track Progress in Airtable.

  • Return to the Sign up in Hearings Airtable and change to Finished/Imported in Sierra, OCLC update, etc.

Statistics:

Count number of records in batch under Batch Modified for statistics.