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
| Table of Contents |
|---|
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 (New Barcodes, Update Sierra, Set Holdings)
General Brief to OCLC Merge Workflow
...
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
Preprocess:
...
Jen’s crew has checked for physical items, added barcodes, checked SuDoc numbers, and so on, in the spreadsheet & uploaded to Airtable.
...
Cheryl will delete MQ barcodes from Sierra before we bring in our Merged Batchload records.
...
Liz has created 949 fields for each item record in Airtable.
...
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.
...
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.
Brief records will be created/edited in MarcEdit:
...
OCLC # (OCLC Masterpiece change to 035 - match point for OCLC merge)
...
Title – for error resolution, only - will not be merged into OCLC bibs.
...
590 field for the Project Name
...
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.
...
Batch Changes to OCLC record will be:
Change 049 from UUSA to UUSH.
General and very modest edits for the usual obvious errors.
...
Merge Brief bibs with our local data into OCLC bibs:
035 OCLC Masterpiece
099 SUDOC
590 field for the Project Name
949
907 (for New Barcodes, Update Sierra, Update Holdings).
Undergoing revision, please wait until finished
...
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:
Go to AirTable Hearings 9-10:
...
Sign up for a certain section of SuDoc Y4 numbers
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to view: New barcodes, Update Sierra, Set holdings:
...
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 SUDOC stem (easily done by adding a filter of Sign up Name is exactly: [your name]--then delete just that filter when done downloading)
...
Download entire set and delete other titles after opening file in Excel (probably too bulky to do).
...
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:
...
Open Excel app first
Open; from PC; Desktop; All Files;
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.
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.
...
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
Keep these specific columns and rename:
...
SuDoc Call Number change to 099$a
...
Title to 245$a - for error checking only
...
ADD a new Column:
Insert a column; in header name it: 590$a
In first column add the text: Y4 Retrocataloging Project 2022
Pull down to fill the rest of the column cells.
...
Record #(Biblio) change to 907$a
...
MARC 949 - $ Subfield to 949$a
OCLC Masterpiece - first do these edits to create two OCLC columns:
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:
...
click return 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 (OCoLC) prefix column and the old OCLC Masterpiece column.
...
Copy the column, then, Insert as new column at the beginning of the spreadsheet
...
Rename as: 035$a
...
Go back to the (OCoLC)OCLC Masterpiece column, then use Find & Select; Replace to edit to OCLC batch-search format:
Find: (OCoLC) ← no spaces
Replace: * ← asterisk
Replace All
Rename column to OCLC batchsearch
Now there are two OCLC columns at either end:
...
Make sure entire spreadsheet is formatted as Text:
Select entire spreadsheet; right-click; Format Cells; Number Category: Text
...
Save the Excel New Barcodes spreadsheet as Text (Tab Delimited)
I usually give it a new name, at this point (but that is optional)
NewBarcodes-Y4T68-2-107to49done
MarcEdit Conversion and Edits
Open MarcEdit; then Tools; Delimited Text Translator
...
Click Import File (formerly Next)
Manually enter in each column (fields) and map them to MARC fields
...
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
...
The field to MARC arguments:
Field 0 035a 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
...
The MARC fields will show up in the Arguments preview box
...
you can also select one argument (or more) and use the blue circled arrows to move it up or down
If all looks fine, click Finish.
...
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.
Go to Reports; Field Count to make sure the number of records is correct
...
(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)
...
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 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
...
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 with all hidden quotation marks…
=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…
fix any articles found using Edit; Find; Replace:
...
Replace: =245 14$aThe ← add a single [space]
...
Edited to here – Melanie
...
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 (choose All Files): Theses-OCLCbatchsearchOver-year
...
Save As: Theses-OCLCbatchsearchOVER-year
...
Leave Default Character Encoding as MARC8
...
Click Execute; then click on Edit Records:
...
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
...
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.
Jump to Record to fix incorrect text.
...
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
949 automatic item record creation
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.
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.
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:
Send an email with the subject line: Global Update for Thesis Batch Load.
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.
| Info |
|---|
Statistics for Merge Overlay ProceduresReport 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!
New Barcodes, Ready for New Cataloging
Not yet READY: do not use yet2-Detailed Procedures for New Barcoded, Ready for New Cataloging: (Merge, NO overlay) workflow
UNDERGOING REVISION: please wait before using
AirTable Extraction:
Go to AirTable Hearings 9-10:
...
Sign up for a certain section of SuDoc Y4 numbers
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to view: Barcodee, Ready for New Cataloging:
...
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 SUDOC stem (easily done by adding a filter of Sign up Name is exactly: [your name]--then delete just that filter when done downloading)
...
Download entire set and delete other titles after opening file in Excel (probably too bulky to do).
...
Distinguish the download by changing the CSV name of Imported table-Barcoded, Ready for New Cataloging after downloading, something like this: NewCatalogingY4V2HRG101-103.
Excel Column work:
...
Open Excel app first
Open; from PC; Desktop; All Files;
Then, navigate to the CSV file (such as NewcatalogingY4V2HRG101-103); then choose Delimited; Next; click in Comma box, Next; choose Text, and Finish.
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.
...
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
Record #(Biblio) - we’re not overlaying
86 (since we’ll be using the first SuDoc Call Number column)
OCLC Finally for Melanie will NOT be deleted: instead delete ANY other OCLC related numbers
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)
Count of items on Bib
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 - first item record
Keep these specific columns and rename:
...
SuDoc Call Number change to 099a
...
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: Y4 Retrocataloging Project 2022
Pull down to fill the rest of the column cells.
...
MARC 949 - $ Subfield to 949a
OCLC final for Melanie/etc.
Insert 2 new columns to the left.
...
In the second column add an asterisk * to the header cell, and then pull down to fill in the rest of the column.
In the first column add the function:
...
click return key:
...
First (header) cell should display *OCLC final for Melanie…
...
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 column to 035a
...
[image]
...
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
Open MarcEdit; then Tools; Delimited Text Translator
...
Click Import File (formerly Next)
Manually enter in each column (fields) and map them to MARC fields
...
Use the Data Snapshot as a guide:
...
[image]
...
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
| Table of Contents |
|---|
...
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 (New Barcodes, Update Sierra, Set Holdings)
General Brief to OCLC Merge Workflow
MarcEdit Brief record creation for merging: export fromAirTable → 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
Preprocess:
Jen’s crew has checked for physical items, added barcodes, checked SuDoc numbers, and so on, in the spreadsheet & uploaded to Airtable.
Cheryl will delete MQ barcodes from Sierra at some point before or after we bring in our Merged Batchload records.
Liz has created 949 fields for each item record in Airtable.
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.
949s for titles with multiple volumeswill to be grouped to be added to a separate workflow.
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.
Brief records will be created/edited in MarcEdit:
OCLC # (OCLC Masterpiece change to 035 - match point for OCLC merge)
Title – for error resolution, only - will not be merged into OCLC bibs.
086 - taken from the first (home) columnand change to 099 - our SUDOC number
590 field for the Project Name
Record #(Biblio) change to 907 - Sierra bib-number match point (needed only for merge & OVERLAY)- match point for Sierra import
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.
Batch Changes to OCLC record will be:
Change 049 from UUSA to UUSH.
General and very modest edits for the usual obvious errors.
Merge Brief bibs with our local data into OCLC bibs:
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
099 SUDOC
590 field for the Project Name
949
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:
Go to AirTable Hearings 9-10:
Sign up for a certain section of SuDoc Y4 numbers
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to view: New barcodes, Update Sierra, Set holdings:
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,
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:
OpenExcel first:
Open; from This PC; Desktop; All Files;
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.
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:
SuDoc Call Number change to 099$a
Title to 245$a - this will be for error tracing;/checking, if necessary, it will not be used in the record
Record #(Biblio) change to 907$a
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 copied the period at the end of the Collection title (if you didn’t, Excel adds up the years):
MARC 949 - $ Subfield rename to 949$a
For OCLC Masterpiece - Create 2 new OCLC columns:
The 035 column:
Use 2 columns to the left
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.
then in the first OCLC column add the function:
=CONCATENATE(G1,F1) ←G equals (OCoLC) and F = OCLC Masterpiece
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 this beginning column to: 035$a
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
Open MarcEdit; then Tools; Delimited Text Translator
Source File:[file name, such as: NewBarcodes-Y4Hearings-Y4-3]
Output File:[new name such as NewbarcodesY4HearingsOverBrief-Y4-3…brief]
Click Import File (formerly Next)
Manually enter in each column (fields) and map them to MARC fields
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
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
The MARC fields will show up in the Arguments preview box
you can right-click in the Add Argument preview boxto Delete or Edit if there are any problems
(Edit will bring up the data entered in Select, Map To, Indicator boxes to be changed as needed)
you can also select one argument (or more) and use the blue circled arrows to move it up or down
If all looks good, click Finish.
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.
Go to Reports; Field Count to make sure the number of records is correct
(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)
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).
[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.]
Do some quick Find’s to check for egregious errors in our local data.
Edit; Find; Find: =035; Find All:
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.
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
In Excel and Notepad, create a text file of OCLC numbers for the batch search in OCLC Connexion.
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.
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, like: OCLCnosNewBarY4T68107
Note the number of *OCLC titles (records).
In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).
Do a Local Save file search (F3)/enter key to make sure there are no records lingering in there;
If something is found, 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 (because then it has to be recreated, if there is a problem).
The text file should load into the Bibliographic Batch Search Window; click Save; but don’t close the window.
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).
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--make sure nothing else is selected.
Click OK;
The OCLC numbers should scroll down the search 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 titles in the Excel spreadsheet.
The Local Save File will have received all the records.
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:
Go to Batch; Process Batch; Click Export; then hit OK;
Name the export file, something like this: OCLCnewbarY4T682107to49
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
Open (choose All Files): OCLCnewbarY4T682107to49.dat
Save As: OCLCnewbarY4T682107to49 type:(.mrk)
Leave Default Character Encoding as MARC8
Click Execute; then click on Edit Records:
Edit; Replace
Find =049 \$aUUSA
Replace:=049 \$aUUSH
Replace All
should match number of titles
Save (.mrk) for the MarcEdit 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: OCLCnewbarY4T682107to49.mrk → (OCLC batch-searched/edited records)
Merge File: NewBarcodes-Y4T68-2-107to49brief2.mrk → (brief records with our data)
Save file: NewBarcodes-Y4T68-2-107to49merged.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:
099 Our SuDoc number
590 Project name
907 Sierra bib-number for overlay
949 auto item record creation
Then click Next to do the merge.
Check the merged records in MarcEdit and do minor edits:
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.
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
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
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
Go to Create Lists; Copy; find file, looking somewhat like this:
Load: Overlaid records for OCLCnewbarY42T68107to49.mrc (b) (10-12-2022)
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
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:
Batch; Enter Bibliographic Search Keys
click on Local File Manager
click on Authorization; then sign in with number and password
Close Local File Manager window; click Save, then Close.
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
Select Update Holdings only
Click OK
A Batch Holdings Report will open with a list of the records.
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.
| Info |
|---|
StatisticsReport 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:
Go to AirTable Hearings 9-10:
Sign up for a certain section of SuDoc Y4 numbers
Batch Loader: you
Batch Load Status: In Progress
Fill in Batch Load Start Date and (if needed during the process) Notes.
Go to view: Barcoded, Ready for New Cataloging:
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:
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).
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:
Open Excel app first
Open; from PC; Desktop; All Files;
Then, navigate to the CSV file (such as NewcatalogingY4V2HRG101-103); then choose Delimited; Next; click in Comma box, Next; choose Text, and Finish.
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.
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
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
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
Open MarcEdit; then Tools; Delimited Text Translator
Source File:[file name, such as: NewBarcodes-Y4Hearings-Y4-3]
Output File:[new name such as NewbarcodesY4HearingsNewBrief-Y4-3…brief]
Click Import File (formerly Next)
Manually enter in each column (fields) and map them to MARC fields
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
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
you can also select one argument (or more) and use the blue circled arrows to move it up or down
If all looks good, click Finish.
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.
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).
Do some quick Find’s to check for egregious errors in our local data and fix them if you find any:
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
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.
Then Save the file of edited 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.
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.
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, like: OCLCnosNewCatY4T68107
Note the number of *OCLC titles (records).
In Connexion, go to File; Local File Manager; choose an empty Local Save File path (or make a new one).
Do a Local Save file search (F3)/enter key to make sure there are no records lingering in there;
If something is found, 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 (because then it has to be recreated, if there is a problem).
The text file should load into the Bibliographic Batch Search Window; click Save; but don’t close the window.
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).
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--make sure nothing else is selected.
Click OK;
The OCLC numbers should scroll down the search 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 titles in the Excel spreadsheet.
The Local Save File will have received all the records.
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:
Go to Batch; Process Batch; Click Export; then hit OK;
Name the export file, something like this: OCLCnewcatY4T682107to49
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
Open (choose All Files): OCLCnewcatY4T682107to49.dat
Save As: OCLCnewcatY4T682107to49 type: (.mrk)
Leave Default Character Encoding as MARC8
Click Execute; then click on Edit Records:
Edit; Replace
Find =049 \$aUUSA
Replace: =049 \$aUUSH
Replace All
(results should match number of titles)
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: OCLCnewcatY4T682107bib.mrk → (OCLC batch-searched/edited records)
Merge File: NewCatlogingY4T682107brief.mrk → (brief records with our data)
Save file: OCLCnewcatY4T682107merged.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:
099 Our SuDoc number
590 Project name
949 auto item record creation
Then click Next to do the merge.
Click on MarcEditor; open new file and check the merged records and do minor edits:
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.
Tools; Add/Delete Fields:
Field: 856 Field Data: [leave blank]
Delete All
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
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
Go to Create Lists; Copy; find file, looking somewhat like this:
Load: Overlaid records for OCLCnewbarY42T68107done.mrc (b) (10-12-2022)
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.
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.